For Day 15 of this series, we start out with Query #35, which is Avg Task Counts. This query retrieves information from the sys.dm_os_schedulers dynamic management view about the current average load across your SQL OS schedulers. Query #35 is shown in Figure 1.

   1: -- Get Average Task Counts (run multiple times)  (Query 35) (Avg Task Counts)

   2: SELECT AVG(current_tasks_count) AS [Avg Task Count], 

   3: AVG(work_queue_count) AS [Avg Work Queue Count],

   4: AVG(runnable_tasks_count) AS [Avg Runnable Task Count],

   5: AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count]

   6: FROM sys.dm_os_schedulers WITH (NOLOCK)

   7: WHERE scheduler_id < 255 OPTION (RECOMPILE);

   8:  

   9: -- Sustained values above 10 suggest further investigation in that area

  10: -- High Avg Task Counts are often caused by blocking/deadlocking or other resource contention

  11:  

  12: -- Sustained values above 1 suggest further investigation in that area

  13: -- High Avg Runnable Task Counts are a good sign of CPU pressure

  14: -- High Avg Pending DiskIO Counts are a sign of disk pressure

Figure 1: Query #35 Avg Task Counts

If you see high average task counts (above 10), that is usually a pretty good indicator of blocking/deadlocking. In some cases, it just means that your instance is very busy, with a high sustained level of activity. If you see average runnable task counts above 0, that is a a good indicator of CPU pressure. If you see average pending IO counts above 0, that is a good indicator of I/O pressure or bottlenecks. You need to run this query multiple times, since the results will change from second to second.

Looking at the results of this query (after I have run it a few times over the course of a few minutes) gives me a good high-level sense of the workload and health of my SQL Server instance.

 

Query #36 is Detect Blocking. This query retrieves information from the sys.dm_exec_requests dynamic management view and the sys.dm_exec_sql_text dynamic management function about any blocking activity that is occurring when you run the query. Query #36 is shown in Figure 2.

   1: -- Detect blocking (run multiple times)  (Query 36) (Detect Blocking)

   2: SELECT t1.resource_type AS [lock type], DB_NAME(resource_database_id) AS [database],

   3: t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req],  --- lock requested

   4: t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time],       -- spid of waiter  

   5: (SELECT [text][/text] FROM sys.dm_exec_requests AS r WITH (NOLOCK)                      -- get sql for waiter

   6: CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) 

   7: WHERE r.session_id = t1.request_session_id) AS [waiter_batch],

   8: (SELECT SUBSTRING(qt.[text][/text],r.statement_start_offset/2, 

   9:     (CASE WHEN r.statement_end_offset = -1 

  10:     THEN LEN(CONVERT(nvarchar(max), qt.[text][/text])) * 2 

  11:     ELSE r.statement_end_offset END - r.statement_start_offset)/2) 

  12: FROM sys.dm_exec_requests AS r WITH (NOLOCK)

  13: CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt

  14: WHERE r.session_id = t1.request_session_id) AS [waiter_stmt],                    -- statement blocked

  15: t2.blocking_session_id AS [blocker sid],                                        -- spid of blocker

  16: (SELECT [text][/text] FROM sys.sysprocesses AS p                                        -- get sql for blocker

  17: CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) 

  18: WHERE p.spid = t2.blocking_session_id) AS [blocker_batch]

  19: FROM sys.dm_tran_locks AS t1 WITH (NOLOCK)

  20: INNER JOIN sys.dm_os_waiting_tasks AS t2 WITH (NOLOCK)

  21: ON t1.lock_owner_address = t2.resource_address OPTION (RECOMPILE);

  22:  

  23: -- Helps troubleshoot blocking and deadlocking issues

  24: -- The results will change from second to second on a busy system

  25: -- You should run this query multiple times when you see signs of blocking

Figure 2: Query #36 Detect Blocking

If no blocking is happening when you run this query, it will not return any results. This is what you want to see! You need to run this query multiple times, since the results will often change from second to second. Don’t just run it once, and then conclude that there is no blocking happening at any time.

If any blocking is occurring, then this query will show you the blocked query text and the query text of the blocker. This information can be very useful when it comes to understanding what is going on when blocking or deadlocking is happening. Many times, excessive blocking and deadlocking is caused by missing indexes on a table, so proper index tuning can be a very effective solution.