Quiz on blocking diagnosis

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 does the sys.dm_os_wait_stats dynamic management view show?

1.  The time for waits that have completed
2.  The titles of Tom Waits songs
3.  The time for waits that are in progress
4.  The time for waits that have not yet begun

Q2. How do you recognize lock waits in sys.dm_os_wait_stats?

1.  They contain the letters ‘WAIT’
2.  They begin with the prefix ‘LCK’
3.  They show up as part of the latch waits
4.  Lock waits aren’t in sys.dm_os_wait_stats

Q3. What does it mean if wait_time_ms is 0 for a wait type?

1.  The wait type isn’t measurable by time
2.  The instance has never had this type of wait, at least since you installed it
3.  The wait time had an integer overflow
4.  This wait type is probably ignorable, at least for the time since the instance has started up

Q4. Which of the following do you need to set up for the SQL Server Agent to send an email for an alert? (check all that apply)

1.  Configure Database Mail successfully
2.  Configure the SQL Server Agent properties to use the Database Mail profile
3.  Set up an Extended Events trace
4.  Configure Operators in the SQL Server Agent

Q5. Which type of alert do you set up to tell SQL Server to notify you if the “Processes Blocked” counter rises above zero?

1.  Severity Error Alert
2.  Service Broker Alert
3.  Performance condition alert
4.  WMI Alert

Q6. What does the column that sp_WhoIsActive shows at the very *left* of the results pane (by default) tell you?

1.  The session id that a query is blocked by
2.  Whether the query is SQL Server or Oracle
3.  The session status (sleeping, runnable, suspended)
4.  How long that query has been running

Q7 How can you see the text of the query a session is running in sp_WhoIsActive?

1.  You can’t do this yet, it might be in the next version
2.  Click on the sql_text column to open the query in a new window
3.  Run sp_WhoIsActive with @get_sql=1
4.  Look at the session in Activity Monitor

Scroll down for the answer key :point_down:

Answer Key

• A1. 1. The time for waits that have completed
• A2. 2. They begin with the prefix ‘LCK’
• A3. 4. This wait type is probably ignorable, at least for the time since the instance has started up
• A4. All are correct, EXCEPT 3. ‘Set up an Extended Events trace’
• A5. 3. Performance condition alert
• A6. 4. How long that query has been running
• A7. 2. Click on the sql_text column to open the query in a new window