Detecting head blockers and blocking chains with details for blocking/blocked sessions, actual running queries and blocked objects

Blocking is a very common problem in database systems and occurs when there is a conflict in access to objects. An open transaction keeps its locks whether it is running or sleeping and any other process trying to access these locked objects will be blocked. To solve a blocking problem quickly and efficiently, you have to know which process blocks which processes, on which objects, and using which queries. Both the head blocker and the blocked queries are important because a blocking problem can be solved by fixing any or all of them.

Unfortunately, SQL Server does not have a complete way of providing these details around a blocking problem in real-time. So, I decided to write an SP to do just that! Using recursive common table expressions, the SP can output multiple blocking chains in order, starting with each head blocker and its sibling blocked processes including all blocked objects and queries causing the problem.
 

 USE [master]  
 GO  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 -- =============================================  
 -- Author      : Ersin Unal, (C)2016  
 -- Description : Retrieves head blockers and blocking chain structure for multiple blocking chains.  
 -- =============================================  
 CREATE PROCEDURE [dbo].[sp_GetBlockingChains]  
 AS  
 SET NOCOUNT ON  
 DECLARE @BlockingChains TABLE (spid int, Chain varchar(MAX), Level int, Root tinyint);  
 DECLARE @ProcessTable TABLE (  
     spid smallint,  
     kpid smallint,  
     blocked smallint,  
     waittype binary(2),  
     waittime bigint,  
     lastwaittype nchar(32),  
     waitresource nchar(256),  
     [dbid] smallint,  
     [uid] smallint,  
     cpu int,  
     physical_io bigint,  
     memusage int,  
     login_time datetime,  
     last_batch datetime,  
     ecid smallint,  
     open_tran smallint,  
     [status] nchar(30),  
     [sid] binary(86),  
     hostname nchar(128),  
     [program_name] nchar(128),  
     hostprocess nchar(10),  
     cmd nchar(16),  
     nt_domain nchar(128),  
     nt_username nchar(128),  
     net_address nchar(12),  
     net_library nchar(12),  
     loginame nchar(128),  
     [context_info] binary(128),  
     [sql_handle] binary(20),  
     stmt_start int,  
     stmt_end int,  
     request_id int,  
     DatabaseName sysname NULL,  
     SchemaName sysname NULL,  
     ObjectName sysname NULL,  
     sql_text nvarchar(max) NULL  
 )  
 INSERT INTO @ProcessTable  
 SELECT p.*  
     , DB_NAME(p.dbid)  
     , OBJECT_SCHEMA_NAME(objectid, p.dbid)  
     , OBJECT_NAME(objectid, p.dbid)  
     , SUBSTRING(CONVERT(nvarchar(MAX), t.text),  
             COALESCE(NULLIF((p.stmt_start/2), 0), 1),   
             CASE (p.stmt_end/2.0)  
                 WHEN -0.5 THEN DATALENGTH(t.text)   
                 ELSE ((p.stmt_end/2) - (p.stmt_start/2) + 2)  
             END  
         )  
 FROM sys.sysprocesses p  
 CROSS APPLY sys.dm_exec_sql_text (p.sql_handle) t  
 ;WITH BlockingChains AS  
 (  
     SELECT  
         spid,  
         CONVERT(VARCHAR(MAX), spid) AS Chain,  
         1 AS Level,  
         1 AS Root  
     FROM @ProcessTable  
     WHERE blocked = 0  
     UNION ALL  
     SELECT  
         s1.spid,  
         x.Chain + ',' + CONVERT(VARCHAR(MAX), s1.spid) AS Chain,  
         x.Level + 1 AS Level,  
         0 AS Root  
     FROM BlockingChains x  
     JOIN @ProcessTable s1 ON s1.blocked = x.spid  
 )  
 INSERT INTO @BlockingChains  
 SELECT * FROM BlockingChains  
 OPTION (MAXRECURSION 0);  
 SELECT 'Head Blocker' AS Comment  
     , PT.spid, PT.blocked  
     , CONVERT(varchar(max), PT.spid) AS BlockingChain  
     , 1 AS BlockingLevel  
     , PT.waittype, PT.waittime, PT.lastwaittype, PT.waitresource, PT.cpu, PT.physical_io, PT.memusage, PT.login_time, PT.last_batch, PT.open_tran, PT.status, PT.hostname, PT.program_name, PT.hostprocess, PT.cmd, PT.loginame  
     , PT.DatabaseName  
     , PT.SchemaName  
     , PT.ObjectName  
     , PT.sql_text AS LatestStatement  
 FROM @ProcessTable PT  
 WHERE spid IN (  
     SELECT CONVERT(INT, LEFT(Chain, CHARINDEX(',', Chain) - 1)) FROM @BlockingChains  
     WHERE Root = 0 AND Level > 1  
     GROUP BY CONVERT(INT, LEFT(Chain, CHARINDEX(',', Chain) - 1))  
     )  
 UNION ALL  
 SELECT 'Blocked Process Details' AS Comment, PT2.spid, PT2.blocked  
     , C.Chain AS BlockingChain  
     , C.Level AS BlockingLevel  
     , PT2.waittype, PT2.waittime, PT2.lastwaittype, PT2.waitresource, PT2.cpu, PT2.physical_io, PT2.memusage, PT2.login_time, PT2.last_batch, PT2.open_tran, PT2.status, PT2.hostname, PT2.program_name, PT2.hostprocess, PT2.cmd, PT2.loginame  
     , PT2.DatabaseName  
     , PT2.SchemaName  
     , PT2.ObjectName  
     , PT2.sql_text AS LatestStatement  
 FROM @ProcessTable PT2  
 JOIN @BlockingChains C ON C.spid = PT2.spid  
 WHERE C.Root = 0  
 ORDER BY 4 ASC, 5 DESC, 2 ASC  
 GO  

Detecting access to partitioned indexes without providing the partitioning key

Partitioned tables provide effective means of data management and are usually used for archiving data with sliding window management. A lesser known use-case is partitioning large tables for higher performance. Partitioning in this sense splits large tables into smaller chunks which help with index depth reductions.

Index depth is a key performance issue because it affects every data access. The greater the index depth, the more pages must be read to traverse the index to find the required row.

There is a very important caveat for making effective use of partitions. If the query optimizer knows the partition(s) it has to hit in order to access certain data, it can eliminate the other partitions and avoid accessing them altogether. That is, the query optimizer is intelligent enough to access only those partitions which may have the required rows. This is only possible if the partitioning key is provided in the query predicates (the where clause).

Initial designs often take this key point into account and the queries are designed to include the partitioning key. However, this may not hold true over time. If a query is added later without the partitioning key, the SQL engine will have to check every partition to look for the requested row. Even if there is a partitioned index perfectly fitting the other predicates, missing the partitioning key results in checking every partition, whether there is data in it or not!

This point is very important. The query optimizer does not check whether a partition is empty and access the header page nevertheless. This may not seem to be a problem if the number of empty partitions is small. However, in many sliding window scenarios, switched-out partitions will be left empty if a separate maintenance is not planned ahead to take case of the older partitions. In this case, even singleton index accesses will keep taking up more and more I/O as well as time as more empty partitions are left over.

Another issue comes up when a table is partitioned for better performance by splitting large indexes into smaller partitions. If the partitions are relatively full and the statistics are updated frequently, better performance is to be expected. With a few hundred partitions, even access without a partitioning column predicate can be acceptable. But if you decide to add a significant number of empty partitions to cater for future growth, it is very likely to get a sudden performance impact due to access of empty partitions.

When you are victim to empty partition scans, you will see high CPU, high I/O and possibly latch contention in tempdb due to managing the empty results of empty partition accesses. It may be quite difficult to recover from such a situation. Getting rid of the empty partitions is always effective but may be very difficult to perform because partition merging is an operation which requires a schema lock on the underlying table; effectively an offline operation!

It is definitely better to be safe than sorry, so here is a way to detect if the empty partitions of your partitioned tables are accessed without providing the partitioning key:

 -- =============================================  
 -- Author      : Ersin Unal, (C)2016  
 -- Description : Retrieves partitioned indexes which are accessed without a partitioning column predicate 
 -- =============================================  
SET NOCOUNT ON  
 if OBJECT_ID('tempdb.dbo.#partition_stats') IS NOT NULL  
     DROP TABLE #partition_stats  
 -- Create temp table to store the results for each index  
 SELECT TOP 0 DB_NAME() database_name, OBJECT_SCHEMA_NAME(1) schema_name, OBJECT_NAME(1) table_name, i.name index_name, os.*   
 INTO #partition_stats  
 FROM sys.partitions p  
 JOIN sys.dm_db_index_operational_stats(DB_ID(), 1, 1, 0) os  
     ON os.object_id = p.object_id   
         AND os.index_id = p.index_id   
         AND os.partition_number = p.partition_number  
 JOIN sys.indexes i  
     ON i.object_id = p.object_id  
         AND i.index_id = p.index_id  
 DECLARE @dbid int, @objid int, @indid int  
 -- The cursor to traverse all indexes defined on any partition scheme  
 DECLARE c CURSOR FAST_FORWARD FOR  
     SELECT DB_ID() db_id, i.object_id objectid, i.index_id  
     FROM sys.indexes i  
     JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id  
     ORDER BY 1,2,3  
 OPEN c  
 FETCH NEXT FROM c INTO @dbid, @objid, @indid  
 WHILE @@FETCH_STATUS = 0  
 BEGIN  
     INSERT INTO #partition_stats  
         SELECT DB_NAME(@dbid) database_name, OBJECT_SCHEMA_NAME(@objid) schema_name, OBJECT_NAME(@objid) table_name, i.name index_name, os.*   
         FROM sys.partitions p  
         JOIN sys.dm_db_index_operational_stats(@dbid, @objid, @indid, 0) os  
             ON os.object_id = p.object_id   
                 AND os.index_id = p.index_id   
                 AND os.partition_number = p.partition_number  
         JOIN sys.indexes i  
             ON i.object_id = p.object_id  
                 AND i.index_id = p.index_id  
         WHERE  
             p.rows = 0 -- The partition is empty  
             AND os.singleton_lookup_count > 0 -- Yet, there is a singleton lookup which means the query optimizer did not prune the partition because it did not have the partitioning column!  
     FETCH NEXT FROM c INTO @dbid, @objid, @indid  
 END  
 CLOSE c  
 DEALLOCATE c  
 SELECT DISTINCT database_name, schema_name, table_name, index_name, index_id FROM #partition_stats ORDER BY 1,2,3,5  

- I've heard you help users with SQL Server problems.
- Truth is, I help SQL Server with user problems.