Grab a pen and notepad, and jot down your answers as you go, then check your answers at the key at the bottom of the page.
Questions
Q1. What’s a big limitation of the alert based on the “Processes Blocked” perf counter?
- It only captures information caused by blockers who are sleeping
- It doesn’t capture any information about the queries involved
- It captures information in XML
- It only captures information about blocking at the Operating System process level
Q2. What does the command RECONFIGURE do?
- Applies only the most recent pending change made by sp_configure
- Shows if there are any pending changes made by sp_configure
- Restarts the SQL Server
- Applies all pending changes made using sp_configure
Q3. What value for ‘blocked process threshold (s)’ means that the blocked process report will NOT be issued?
- 0
- NULL
- -1
- 5 or higher
Q4. What wakes up periodically in SQL Server to look for deadlocks?
- Deadlock Monitor
- Dirty Buffer Manager
- SQL Server Agent
- The Lazywriter
Q5. What happens to blocked process information if you don’t set up a SQL trace or Extended Events session to capture it?
- It’s written to the SQL Server Error Log
- It’s written to a text file on the system drive
- It’s written to the Windows Event Log
- It is not recorded anywhere
Q6. Why might you allow for event loss in an Extended Events session?
- This is required to do causality tracking in the session
- To allocate twice as much memory for the session
- To allow data to be written to the target more quickly
- To reduce the performance impact of the session
Q7. Do you have to restart the SQL Server to configure the Blocked Process Report?
- Yes
- No
- Only on SQL Server 2005
- Only on Windows Server 2008R2
Q8. Which of these questions will the Blocked Process Report answer?
- What are the execution plans for the queries involved in blocking?
- Did the lead blocker have to read data from disk, or was the data already in memory?
- When I have blocking of five seconds or more, is the lead blocker usually the same query?
- How often do I have blocking that lasts five seconds or less?
Q9. Why does inputbuf not always contain the entire TSQL transaction run by a process?
- It only displays 4000 characters of the last statement sent from the client
- The buffer pool in the SQL Server might be full when this is generated
- It only displays information if the client is a .NET application
- It uses Service Broker
Q10. If you get a waitresource on a PAGE number, how can you tell the related table name?
- The Database Tuning Advisor
- The DBCC INPUTBUFFER command
- The undocumented DBCC PAGE command
- You can’t decode that
Scroll down for the answer key :point_down:
Answer Key
- A1. 2. It doesn’t capture any information about the queries involved
- A2. 4. Applies all pending changes made using sp_configure
- A3. 1. 0
- A4. 1. Deadlock Monitor
- A5. 4. It is not recorded anywhere
- A6. 4. To reduce the performance impact of the session
- A7. 2. No
- A8. 3. When I have blocking of five seconds or more, is the lead blocker usually the same query?
- A9. 1. It only displays 4000 characters of the last statement sent from the client
- A10. 3. The undocumented DBCC PAGE command
REGISTER