In one of my previous notes, I talked about Wait Types. Summary: When your query is waiting on resources, a specific wait type is assigned to it by SQL Server which you can track & capture. There are two DMVs (Dynamic Management Views) that show you real-time information about waits currently happening in the engine while the workload is running. sys.dm_os_waiting_tasks and sys.dm_exec_requests. While the former (os_waiting_tasks) is quite self-explanatory and shows a lot of details about the current waits happening, the latter (exec_requests) is also informative and has a column wait_type that shows the current wait encountered by the session. But there is a catch. Observe the nomenclature of the DMVs. “Task” vs “Session”. Got it? No? sys.dm_os_waiting_tasks shows you task-level wait information and sys.dm_exec_requests shows you session-level wait information. What is the difference? Well, a request can be broken down into multiple tasks and thereby multiple threads, and each thread could be waiting on a different wait type (possible scenario). So sys.dm_os_waiting_tasks will show you one record for each task/thread and the specific wait type. But sys.dm_exec_requests will show you a record for the overall request and may mislead you by showing a generic wait type. Underlying the session/request, a lot is going on at the task/thread level. So when you are troubleshooting query slowness, focus on task-level waits, not session/request level. Sounds interesting? Watch the video below.
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Get new content directly in your inbox – Click here
Hope the video was worth your time.
Happy Learning.
Leave A Comment