In two previous articles—Getting to Know Wait Stats and Dissecting SQL Server’s Top Waits—I discussed SQL Server 2005 waits and showed you some of the more common ones. Those articles provided a mostly high-level view, aggregating waits up to the instance level by individual wait type. The articles provided good advice for determining what SQL Server is waiting on most, but sometimes you need a deeper view of what's going on. For example, from a high-level peek at the wait stats, you might see that you're waiting on lots of disk-to-memory transfers—based on large amounts of PAGEIOLATCH_SH waits shown—but you wouldn't know who or what is mainly responsible for these transfers.
Wouldn’t it be nice to see what each connection or even thread in the SQL Server instance is waiting on? Actually, this ability has always been available, starting with SQL Server 7.0 and the sysprocesses system pseudo table. I say "pseudo table" because there were a number of system tables that weren't even real tables. They were simply derived from memory and presented to the user as a table when queried. Over the years, with each new release, more and more of these "tables" have been added, especially with all the DMVs and DMFs we have in the SQL Server 2008 era.
Querying the sysprocesses table was the method of choice if you were looking for performance-related problems before SQL Server 2005. But even though sysprocesses is still around in the form of a compatibility view (sys.sysprocesses), the better choice now is a set of DMVs or DMFs that together give even more information—and are guaranteed to be updated with each new SQL Server version. In contrast, the compatibility views might be depreciated or at least not kept current with all the new functionality in future versions. I'll touch on only some of these here, so I recommend that you spend some time getting familiar with them if you aren't already.
Strategic DMVs
So, what are these objects, anyway? The main object of interest for my purpose is the sys.dm_os_waiting_tasks DMV, which tells me which connections (or threads, if there are multiple threads per connection) are waiting and what they're waiting on. However, although that DMV tells me things such as the wait type and how long it has been waiting, I need to join it with another DMV called sys.dm_exec_connections to get the handle that points to the actual code that's being executed. And from there, I need to invoke or technically CROSS APPLY the DMF called sys.dm_exec_sql_text to get the readable version of the code or stored procedure that's being executed at that time.
That sounds complicated, so perhaps the code snippet in Listing 1 (below) will help. Note that I've added a WHERE clause to limit the sessions shown to only non-system sessions and also to ensure that there's no actual wait time greater than 0. You can adapt this WHERE clause as necessary, but most of the time I would mainly be concerned with direct user-related activities and only those activities that are accumulating waits.
In Figure 1, you can see the waits that resulted when I ran a simple query that selected all the rows in a large table called OrderHeader, using my sample query. I ran this code on my laptop, which had lots of other things going on, so I happened to spot this query waiting on one of the new Preemptive wait types in SQL Server 2008 (which I'll discuss in a future article). Remember that the focus of this article is to show how you can drill down into the waits and see which statements were causing the waits. In this case, it's clear not only which connection (SPID 54) caused the waits but which statement was used.
This sample scenario was based on waiting on an external resource, but you can also wait on locks taken by other users and is simply known as blocking. Both will show up in the high-level wait stats, but only at this level can you begin to troubleshoot why or who is doing the blocking. Figure 2 shows another example of a select that is waiting, but this time it's being blocked by an update that hasn't yet been committed.
You can see that the blocking_session_id column is populated, and you can use that information along with yet another DMV—sys.dm_exec_requests—to get a snapshot view into both the blocked session and the blocker, as you see in Figure 3, using the query in Listing 2 (below).
Streamlined
Keep in mind that there are many good code examples that can provide much more detail for all aspects of this topic, if you need them. But, at the core, they almost universally use these DMVs similarly to what I've shown you here. These DMVs will satisfy most casual probes, while resulting in very little overhead on your system. Knowing how the DMVs work and what they can provide will get you moving in the right direction when you need to drill down a little further.
Listing 1
SELECT tx.\[text\] AS \[Executing SQL\], wt.session_id, wt.wait_duration_ms, wt.wait_type,
wt.resource_address, wt.blocking_session_id, wt.resource_description
FROM sys.dm_os_waiting_tasks AS wt INNER JOIN sys.dm_exec_connections AS ec
ON wt.session_id = ec.session_id
CROSS APPLY
(SELECT * FROM sys.dm_exec_sql_text(ec.most_recent_sql_handle)) AS tx
WHERE wt.session_id > 50 AND wt.wait_duration_ms > 0
Web Listing 2
SELECT
Blocked.Session_ID AS Blocked_Session_ID
, Blocked_SQL.text AS Blocked_SQL
, waits.wait_type AS Blocked_Resource
, Blocking.Session_ID AS Blocking_Session_ID
, Blocking_SQL.text AS Blocking_SQL
, GETDATE()
FROM sys.dm_exec_connections AS Blocking INNER JOIN sys.dm_exec_requests AS Blocked
ON Blocked.Blocking_Session_ID = Blocking.Session_ID
CROSS APPLY
(
SELECT * FROM sys.dm_exec_sql_text(Blocking.most_recent_sql_handle)
) AS Blocking_SQL
CROSS APPLY
(
SELECT * FROM sys.dm_exec_sql_text(Blocked.sql_handle)
) AS Blocked_SQL
INNER JOIN sys.dm_os_waiting_tasks AS waits
ON waits.Session_ID = Blocked.Session_ID
