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.
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.
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.