Introduction

The analysis of blocking in SQL Server can be hard if the SQL Server instance has a high number of active connections. This analysis becomes complicated because busy instances could have more than one blocking connection, meaning that we could have more than just one blocking chain. For every group of blocked connections, we could also have more than two connections being blocked.

This article will show you how the analysis of blocking connections could be simplified by using a proc, sp_who4, developed by the author of this article. The only purpose of the procedure is the the simplification of blocking analysis.

Proposed Solution

The proposed solution is using a proc with two goals:

1. Blocked connections should be displayed in a hierarchical format
2. The missing indexes from execution plans should be extracted

Displaying the information in a hierarchical fashion means the parent is shown first and children’s and children of children’s (and so on) after this. This is done to help identify the cause of blocking because the root connection will be the first connection displayed.

The extraction of missing indices from execution plans has the primary goal of helping to optimize the queries involved in blocking. If we know the missing index information provided, we could possibly add these indexes and help the Query Optimizer (QO) produce a more efficient plan. This procedure helps by extracting these missing indices quicker than you can using SSMS (SQL Server Management Studio).

This proc returns just 19 columns containing details about current blocked connections and various information that help the analysis. The results are:

• Group Num: This column will contain identifiers for groups of SQL Server blocked connections
• Connections Blocked: current spid or session_id plus parent spid for blocking scenarios
• Connection DB: database for current connection
• Object: Name of current procedure
• SQL Statement: Active sql statement
• Status: sys.sysprocesses.status
• Transaction count (for current connection)
• Wait type: Last wait type
• Wait object: Blocked objects (ex: indices)
• Wait duration: Difference between current date time
• CPU
• Reads
• Writes
• Indexes: Missing indexes
• Query plan: The execution plan of current object
• Program name
• Hst name: Name of the host currently connected to SQL Server
• Name of login
• Hid: Binary representation of Connections Blocked.

Hid is a hierachyid column used to store blocked spids in a hierarchical manner thus: root blocking session\level 1 blocked session\level 2 blocked session\etc. As an example, if this returns: 55\56\59\84, this means:

• • The root session for current blocking is 55
  • 56 is blocked by spid 55
  • 59 is blocked by spid 56
  • 84 is blocked by 59 but the root cause is 55.

If I run this proc on a system, I might get the results shown in the image below. This result contains 2 blockings connections:

Group 1 contains 3 connections: 62, 53, 58. The root cause of this blocking is connection 53, and the reason for this blocking is the missing index needed to execute the Insert_Tab1 procedure.

Group 2 also contains 3 connections: 60, 56, 64. The root cause of this blocking is connection 60 that has transaction_count = 1.

Here is the code that I used to create this situation. Note, my batch separator is "GOOGOOGOO".

CREATE TABLE Tab1(
Col1 VARCHAR(11) NOT NULL/*PRIMARY KEY*/ ,
Col2 INT NULL,
Col3 VARCHAR(20) NULL,
Col4 MONEY NOT NULL,
Col5 NVARCHAR(50)
)
GOOGOOGOO
CREATE TABLE Tab1His(
Col1 VARCHAR(11) NOT NULL,
Col2 INT NULL,
Col3 VARCHAR(20) NULL,
Col4 MONEY NOT NULL,
Col5 NVARCHAR(50),
ID INT IDENTITY PRIMARY KEY,
[Type] VARCHAR(1) NOT NULL,
CurrentDate DATETIME NOT NULL DEFAULT(GETDATE()),
)
GOOGOOGOO
CREATE TRIGGER trgTab1His
ON Tab1
AFTER UPDATE, DELETE
AS
BEGIN
DECLARE @Type VARCHAR(1)
SET @Type =
CASE
WHEN EXISTS(SELECT * FROM inserted)
THEN 'U' ELSE 'D'
END
INSERT Tab1His(Col1, Col2, Col3, Col4, Col5, [Type])
SELECT Col1, Col2, Col3, Col4, Col5, @Type
FROM deleted
END
GOOGOOGOO
CREATE OR ALTER PROC Insert_Tab1 (@Col1 VARCHAR(11), @Col2 INT, @Col3 VARCHAR(20), @Col4 MONEY, @Col5 NVARCHAR(50))
AS
BEGIN
IF EXISTS(SELECT * FROM Tab1 WHERE Col1 = @Col1)
BEGIN
UPDATE th
SET Col2 = @Col2, Col3 = @Col3, Col4 = @Col4, Col5 = @Col5
FROM Tab1 th
WHERE Col1 = @Col1
END
ELSE
BEGIN
INSERT Tab1(Col1, Col2, Col3, Col4, Col5)
SELECT @Col1, @Col2, @Col3, @Col4, @Col5
END
END
GOOGOOGOO
EXEC Insert_Tab1 1, 2000, 'A', 0.50, 'ABCDE'
EXEC Insert_Tab1 1, 2000, 'A', 5.38, 'ABCDE'
SELECT * FROM Tab1
SELECT * FROM Tab1His
DELETE Tab1
WHERE Col1 > 1
;WITH N10(Num)
AS (
SELECT *
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) N10(Num)
), N100(Num)
AS (
SELECT (a.Num-1)*10 + b.Num
FROM N10 a
CROSS JOIN N10 b
), N10000(Num)
AS (
SELECT (a.Num-1)*100 + b.Num
FROM N100 a
CROSS JOIN N100 b
)
INSERT Tab1(Col1, Col2, Col3, Col4, Col5)
SELECT num.Num + 1, t.Col2, t.Col3, t.Col4, t.Col5
FROM Tab1 t
CROSS JOIN N10000 num
WHERE t.Col1 = 1
DBCC FREEPROCCACHE

Run each section of code below in a separate window. The final line runs the procedure attached to this article.

--Blocking #1: Window 1
--Con #1
SET XACT_ABORT ON
BEGIN TRAN
EXEC Insert_Tab1 1, 1630, 'BBB', 1.68, 'Croco'
--ROLLBACK
--Con #2: Window 2
SET XACT_ABORT ON
BEGIN TRAN
EXEC Insert_Tab1 1, 2630, 'DDD', 1.56, 'Crocodilo'
ROLLBACK
--Con #3: Window 3
SELECT *
FROM Tab1
--Blocking #2: Window 4
--Con #4
BEGIN TRAN
CREATE TABLE Coco (Col1 INT, Col2 INT)
--ROLLBACK
--Con #5
SELECT * FROM sys.tables ta
--Now
EXEC sp_who4

Conclusions

This proc will simplify the analysis of blocked connections because will create groups of blocked connections and will extract the missing indices. This is main reason why this proc (sp_Who4) could be used to simplify the analysis of blocked processes and then take fast decision for QO (the missing indices).