Quiz on troubleshooting blocking retroactively

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
  •  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