sp_whoisactive: Seeing All of the Waits

Home ... Documentation ... Downloads
Prior: Seeing the Wait That Matters Most .... Next: Is This Normal?

Who is Active’s default lightweight waits collection is designed to show you the most important wait that’s currently slowing down your request. But in doing so it hides a lot of information. Who is Active is capable of showing you information about all of the waits that are currently pending on behalf of your request. All you need to do is modify the @get_task_info parameter...

EXEC sp_WhoIsActive
    @get_task_info = 2

This causes the single wait described in the prior article (the blocking wait, LCK_M_S) to become a report about all nine waits (see the article on blocking for background information):


We now see the full picture, including both the lock wait and all of the CXPACKET waits. Each wait type is broken out and accompanied by information on how many waits there are, and how long the waits have been outstanding. In this case we have a single task that is waiting on LCK_M_S and eight tasks waiting on CXPACKET.

Depending on how many tasks are waiting for each wait type, Who is Active breaks out the times as follows:

In this case the LCK_M_S wait has been pending for just over 412 seconds. The exchange waits all started building up just after the block situation started, so the minimum and average wait times for those are within a few milliseconds of the wait time for the blocking wait.

In addition to the pending wait time, some additional information gets shipped along with three wait categories:

A bit more information is available when using @get_task_info = 2. Three additional columns are added to the Who is Active output, which apply to active requests (all three will be NULL for sleeping sessions).


The key term for all of these is “real time”—even for parallel plans. This is in stark contrast to the [CPU], [reads], [writes], and [physical_reads] columns that are in the default output, and which are not updated in real time once more than one thread is involved. Unfortunately, real time also has its drawbacks. As the various tasks that are working on behalf of the query begin finishing their work, the numbers will no longer be cumulative to the query, and will cease to make a whole lot of sense. That’s a small price to pay, in my opinion, for timely information when looking at active queries.

Prior: Seeing the Wait That Matters Most .... Next: Is This Normal?
(C) 2017 Adam Machanic