who3_synthetic.sql

who3_synthetic.sql

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- https://blog.sqlauthority.com/2020/04/20/sql-server-blocking-tree-identifying-blocking-chain-using-sql-scripts/

IF OBJECT_ID('tempdb..#Blocks') IS NOT NULL
    DROP TABLE #Blocks
SELECT   spid
        ,blocked
        ,REPLACE (REPLACE (st.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS batch
INTO     #Blocks
FROM     sys.sysprocesses spr
    CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
GO
  
WITH BlockingTree (spid, blocking_spid, [level], batch)
AS
(
    SELECT   blc.spid
            ,blc.blocked
            ,CAST (REPLICATE ('0', 4-LEN (CAST (blc.spid AS VARCHAR))) + CAST (blc.spid AS VARCHAR) AS VARCHAR (1000)) AS [level]
            ,blc.batch
    FROM    #Blocks blc
    WHERE   (blc.blocked = 0 OR blc.blocked = SPID) AND
            EXISTS (SELECT * FROM #Blocks blc2 WHERE blc2.BLOCKED = blc.SPID AND blc2.BLOCKED <> blc2.SPID)
    UNION ALL
    SELECT   blc.spid
            ,blc.blocked
            ,CAST(bt.[level] + RIGHT (CAST ((1000 + blc.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS [level]
            ,blc.batch
    FROM     #Blocks AS blc
        INNER JOIN BlockingTree bt 
            ON  blc.blocked = bt.SPID
    WHERE   blc.blocked > 0 AND
            blc.blocked <> blc.SPID
)



SELECT
    SPID = N'' + ISNULL(REPLICATE (N'|         ', LEN ([level])/4 - 2),'')
        + CASE WHEN (LEN([level])/4 - 1) = 0 THEN '' WHEN [level] IS NULL THEN '' ELSE '|------  ' END
        + CAST (er.session_id AS VARCHAR (10))
    ,killSPID          = 'kill ' + CONVERT(varchar(15), er.session_id)
    ,BlkBy              = er.blocking_session_id
    ,killBlkBy          = CASE WHEN er.blocking_session_id > 0 THEN 'kill ' + CONVERT(varchar(15), er.blocking_session_id) ELSE '' END
    ,ElapsedMS          = er.total_elapsed_time
    ,CPU                = er.cpu_time
    ,IOReads            = er.logical_reads + er.reads
    ,IOWrites           = er.writes
    ,Executions         = ec.execution_count
    ,CommandType        = er.command
    ,[WaitRes]          = COALESCE(spr.waitresource, '')
    ,[BlkObj]           = COALESCE(OBJECT_NAME(sli.rsc_objid, sli.rsc_dbid), '')
    ,ObjectName         = COALESCE(OBJECT_SCHEMA_NAME(qt.objectid,qt.dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid) ,'')
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2 + 1,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2 +1
        )        
    ,STATUS             = ses.STATUS
    ,[Login]            = ses.login_name
    ,Host               = ses.host_name
    ,DBName             = DB_Name(er.database_id)
    ,LastWaitType       = er.last_wait_type
    ,StartTime          = er.start_time
    ,Protocol           = con.net_transport
    ,transaction_isolation =
        CASE ses.transaction_isolation_level
            WHEN 0 THEN 'Unspecified'
            WHEN 1 THEN 'Read Uncommitted'
            WHEN 2 THEN 'Read Committed'
            WHEN 3 THEN 'Repeatable'
            WHEN 4 THEN 'Serializable'
            WHEN 5 THEN 'Snapshot'
        END
    ,Dead_prio = ses.deadlock_priority
    ,ConnectionWrites   = con.num_writes
    ,ConnectionReads    = con.num_reads
    ,ClientAddress      = con.client_net_address
    ,Authentication     = con.auth_scheme
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
LEFT JOIN BlockingTree bt
ON bt.spid = er.session_id
LEFT OUTER JOIN sys.sysprocesses spr 
ON  spr.spid = bt.spid
LEFT JOIN sys.syslockinfo sli
ON  sli.req_spid = spr.spid AND
    sli.rsc_type = 5 AND
    OBJECT_NAME(sli.rsc_objid, sli.rsc_dbid) IS NOT NULL
	AND er.blocking_session_id > 0
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY 
(
    SELECT execution_count = MAX(cp.usecounts)
    FROM sys.dm_exec_cached_plans cp
    WHERE cp.plan_handle = er.plan_handle
) ec
WHERE 1=1
	--er.session_id != @@SPID
ORDER BY
    COALESCE(bt.[level], 'z') ASC,
    er.blocking_session_id DESC,
    er.logical_reads + er.reads DESC,
    er.session_id