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.
4 thoughts on “SQL Server Diagnostic Information Queries Detailed, Day 15”
Dear Glenn,
thank you very much for this blog.
Could you please verify Query #36 – Detect Blocking?
I tried to run this T-SQL code in various of MS SQL server’s versions but still failed.
I tried to “fix” it but without success.
Thanks.
Regards,
matus knut
Hi Matus, I’m not sure what you actually mean. That query absolutely works. If there is no blocking, it won’t return any results, but it does not error out.
After removing the line # and: and running…
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ‘/text’.
Msg 319, Level 15, State 1, Line 11
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 156, Level 15, State 1, Line 17
Incorrect syntax near the keyword ‘AS’.
Msg 319, Level 15, State 1, Line 18
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Msg 319, Level 15, State 1, Line 19
Incorrect syntax near the keyword ‘with’. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Hi Brian, There are two queries in this blog post, but you don’t say which one you are having a problem with. You also don’t say what version of SQL Server you are trying to run them on. Both of these queries will work on any recent version of SQL Server. They are both part of my Diagnostic Information Queries, which are used by thousands of people around the world.
If you can be more specific, I might be able to help you.