The sys.dm_os_waiting_tasks DMV is a very powerful DMV and one of my favorites. How many times have you been approached by a user saying “My query is taking too long to run, can you see what the problem is”? Or maybe you have heard the famous question “The server seems to be moving very slow today compared to normal, can you see what’s going on?” As a DBA, these are all very common questions and one of the first places I will generally check is the sys.dm_os_waiting_tasks DMV. This DMV provides information about tasks that are currently “waiting” on something within SQL and information about the type of resource that is being “waited” for.
Lets have a quick look at this DMV to help illustrate the type of information it returns and how it can be used.
SELECT * FROM sys.dm_os_waiting_tasks
The first column presented is the waiting_task_address which represents the internal memory address for the task that is waiting. Next in the result set is the session_id (SPID) and the exec_context_id. Assuming that no explanation is needed for the session_id, I will go directly to the exec_context_id which represents the ID of the thread from the session_id whenever you have parallelism taking place. If a particular session_id is not running in parallel then you will always see a 0 for the exec_context_id field. The next field is the wait_duration_ms which gives you the time in milliseconds that a particular SPID has been waiting.
The next field is the crown jewel of this DMV which is the actual wait type. Looking at the screenshot above we see various wait types. For example we see ‘LAZYWRITER_SLEEP’ which is a background process in SQL Server that occurs when the lazy writer is waiting for more work. We see the wait type ‘CXPACKET’ wait which simply means that this session is running in parallel and the current session_id/exec_context_id is waiting for some of the other threads to complete their work. One of the more common wait types, although not displayed in the screen shot above, is the ‘LCK_M_%’ wait type. This wait type indicates that the current session is currently waiting on a lock and based on the type and mode of the lock it is waiting for, the wait type will be changed accordingly. For example, the ‘LCK_M_IS’ wait type indicates an Intent Shared lock is being waiting on. The ‘LCK_M_X’ indicates that an Exclusive lock is being waited for.
I wont go into detail about what every single wait type is, but Tom Davidson created a great white paper on SQL Server 2005 Waits and Queues which I highly recommend. Although the white paper was written for SQL Server 2005, the information within it still applies for current version. You can find the white paper at https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0CDYQFjAA&url=http%3A%2F%2Fdownload.microsoft.com%2Fdownload%2F4%2F7%2Fa%2F47a548b9-249e-484c-abd7-29f31282b04d%2FPerformance_Tuning_Waits_Queues.doc&ei=SD3cUfv0KYvI9gS5ooHgDQ&usg=AFQjCNFRIebSlMLnry8gH99CQklhdmokJw&bvm=bv.48705608,d.eWU
This DMV also returns information about the session_id that may be causing blocking and its resource addresses for that particular blocking session.
All in all, this is a very powerful DMV, and as I said before, one of my favorites. I would highly recommend that you play around with this DMV, get familiar with some of the more common wait types, and add this tool to your troubleshooting toolkit.
For more information about this DMV, see the below Books Online link:
Follow me on Twitter @PrimeTimeDBA