A Deep Insight on SQL Server Blocking

This article will give complete insight on blocking, how you can manually create a blocking, how to find the root cause, how to set alert and how to fix it.

Blocking in SQL:

Blocking in SQL Server is very dangerous as it can heavily impact the performance. Not only that; it will create angry Clients along with numerous escalation emails.

To fix SQL SERVER blocking from growing bigger, we have to immediately identify the root blocker and take the needed steps.

Script to send an Alert email:

The below TSQL script will help you to quickly jump on blocking and fix it rather than someone informing to you through escalation emails. It sends an Alert Email with following info

1. Key Blocking Session ID
2. Count of other transactions in the queue
3. Total blocking time

;

WITH T1 (
    csid
    ,cstime
    ,cstats
    ,cbid
    ,cdb
    ,ccmd
    ,ctxt
    )
AS (
    SELECT session_id
        ,start_time
        ,STATUS
        ,blocking_session_id
        ,Db_name(database_id)
        ,command
        ,sql_text = Cast(TEXT AS VARCHAR(max))
    FROM sys.dm_exec_requests WITH (NOLOCK)
    CROSS APPLY sys.Dm_exec_sql_text(sql_handle)
    WHERE STATUS <> 'Background'
    )
    ,T2 (
    csid
    ,cstime
    ,cstats
    ,cbid
    ,ccmd
    ,ctxt
    ,cnum
    ,clvl
    )
AS (
    SELECT r1.csid
        ,r1.cstime
        ,r1.cstats
        ,r1.cbid
        ,r1.ccmd
        ,r1.ctxt
        ,Row_number() OVER (
            ORDER BY r1.csid
            )
        ,0 AS LevelRow
    FROM T1 r1
    INNER JOIN T1 r2 ON r1.csid = r2.cbid
    WHERE r1.cbid = 0
    
    UNION ALL
    
    SELECT r3.csid
        ,r3.cstime
        ,r3.cstats
        ,r3.cbid
        ,r3.ccmd
        ,r3.ctxt
        ,b.cnum
        ,b.clvl + 1
    FROM T1 r3
    INNER JOIN T2 b ON r3.cbid = b.csid
    WHERE r3.cbid > 0
    )
SELECT *
INTO ###table
FROM T2
ORDER BY cnum
    ,clvl

-- select * from ###table
DECLARE @id INTEGER
    ,@cmd VARCHAR(max)
    ,@ftime DATETIME
    ,@tcount INTEGER
    ,@mins INTEGER

SET @id = (
        SELECT TOP 1 csid
        FROM ###table
        )
SET @ftime = (
        SELECT TOP 1 cstime
        FROM ###table
        )
SET @cmd = (
        SELECT TOP 1 ccmd
        FROM ###table
        )
SET @tcount = (
        SELECT count(*)
        FROM ###table
        )
SET @mins = datediff(mi, @ftime, getdate())

-- select @id, @ftime, @cmd, @tcount - 1, @mins
DECLARE @message VARCHAR(1000)

SET @message = 'Session ID : ' + cast(@id AS VARCHAR(10)) + ' is blocking ' + cast(@tcount AS VARCHAR(10)) + ' other transactions for ' + cast(@mins AS VARCHAR(10)) + ' minutes. Please check ASAP !'

SELECT @message

DROP TABLE ###table

EXEC msdb.dbo.sp_send_dbmail @profile_name = '<SQL SERVER DBMAIL PROFILE NAME>'
    ,@recipients = '<RECIPIENT EMAIL ADDRESS. SEPARATE MULTIPLE EMAIL ADDRESS BY ;'
    ,@subject = 'Blocking'
    ,@body = @message

Steps involved while using this script:

Below is the sample Email that will be sent out by the script.

Soon as you see this email, the first step that you have to do is to connect to the SQL Server instance. Next you must analyze the sysprocesses table to quickly identify the login that is running this spid. After that you can contact the User and check if the spid can be killed. If yes, kill the spid using KILL Command and resolve the blocking.

Proactive testing of this script by creating a block on SQL Server:

Of course, you cannot wait for the blocking to happen on the production server to test this script. Creating blocking is easy. Try this on your Test server. Select some records from your test table by setting some lock on the table. Add a delay for the transaction to complete. While this is running from one query window, open another query window and try to select records from the same table. This time it is not necessary to set a lock.

BEGIN TRANSACTION 
SELECT * FROM <YOUR TEST DATABASE NAME>.<SCHEMA NAME>.<TABLE NAME> WITH (TABLOCKX, HOLDLOCK); 
WAITFOR DELAY '00:11:00' -- DURATION IN HH:MM:SS. 
ROLLBACK TRANSACTION

SELECT * FROM <YOUR TEST DATABASE NAME>.<SCHEMA NAME>.<TABLE NAME>

Points to remember if blocking happens often:

If blocking occurs often, check if index, statistics are proper on the target database. Share the problematic scripts/statements to the development team so that they can fine tune the query.

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook data problem and excel recovery software products. For more information visit www.datanumen.com