Hierarchical Tree View of blocking behaviour

Question

Post reply

Hierarchical Tree View of blocking behaviour

simon_s

Ten Centuries

Points: 1358
More actions
March 7, 2014 at 7:55 am

#396384

Help before I leap out the window !
I want to create a view of a table that captures blocking info into a hierahical tree that reflects blocking chain
SELECT distinct
[spid]
,[waitresource]
,[typ]
,[ecid]
,[blocked]
,[lastwaittype]
,[login_time]
,[open_tran]
,[cmd]
FROM [DBA_Monitor].[dbo].[Blocking_Detail] (NOLOCK)
order by login_time
go

Returns
spidwaitresourcetypecidblockedlastwaittypelogin_timeopen_trancmd
54Blocking 00MISCELLANEOUS 2014-03-07 11:27:08.7432AWAITING COMMAND
57RID: 11:1:2937:8Blocking054LCK_M_U 2014-03-07 11:27:34.1972UPDATE
57RID: 11:1:2937:8Blocked054LCK_M_U 2014-03-07 11:27:34.1972UPDATE
59RID: 11:1:2937:8Blocked057LCK_M_U 2014-03-07 11:27:38.4572UPDATE
56RID: 11:1:2937:8Blocking057LCK_M_U 2014-03-07 11:36:27.6602DELETE
56RID: 11:1:2937:8Blocked057LCK_M_U 2014-03-07 11:36:27.6602DELETE
55RID: 11:1:2937:0Blocked056LCK_M_S 2014-03-07 11:49:10.5200SELECT
In order to make it look like a thing of beauty that shows stuff in tree view etc I've tried using CTE as follows ... it fails to return anything but blocking rows
WITH BlockingBehaviour
AS (
SELECT -- distinct
blocking.[spid]
,blocking.[waitresource]
,blocking.[typ]
,blocking.[blocked]
,blocking.[lastwaittype]
,blocking.[login_time]
,blocking.[open_tran]
,blocking.[cmd]
,1 AS block_level
FROM [DBA_Monitor].[dbo].[Blocking_Detail] (NOLOCK) as blocking
where TYP = 'blocking'
union all
SELECT --distinct
blocked.[spid]
,blocked.[waitresource]
,blocked.[typ]
,blocked.[blocked]
,blocked.[lastwaittype]
,blocked.[login_time]
,blocked.[open_tran]
,blocked.[cmd]
,BB.block_level + 1
FROM [DBA_Monitor].[dbo].[Blocking_Detail] (NOLOCK) as blocked
inner join BlockingBehaviour as BB
on blocked.blocked = bb.spid
where bb.TYP = 'blocked'
)
SELECT distinct *
FROM BlockingBehaviour
order by 7 asc ,3 desc
So somehow Im getting the join to the CTE wrong I guess but I cant see where for the life of me
any wise words gratefully recieved !

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I think this what you want:

DECLARE @blockinfo TABLE

(

spid INT ,

waitresource VARCHAR(100) ,

typ VARCHAR(25) ,

ecid INT ,

blocked INT ,

lastwaittype VARCHAR(100) ,

login_time DATETIME ,

open_tran INT ,

cmd VARCHAR(100)

);

INSERT INTO @blockinfo

( spid, waitresource, typ, ecid, blocked, lastwaittype, login_time,

open_tran, cmd )

VALUES ( 54, '', 'Blocking', 0, 0, 'MISCELLANEOUS',

' 2014-03-07 11:27:08.743', 2, 'AWAITING COMMAND' ),

( 57, 'RID: 11:1:2937:8', 'Blocking', 0, 54, 'LCK_M_U',

' 2014-03-07 11:27:34.197', 2, 'UPDATE' ),

( 57, 'RID: 11:1:2937:8', 'Blocked', 0, 54, 'LCK_M_U',

'2014-03-07 11:27:34.197', 2, 'UPDATE' ),

( 59, 'RID: 11:1:2937:8', 'Blocked', 0, 57, 'LCK_M_U',

' 2014-03-07 11:27:38.457', 2, 'UPDATE' ),

( 56, 'RID: 11:1:2937:8', 'Blocking', 0, 57, 'LCK_M_U',

' 2014-03-07 11:36:27.660', 2, 'DELETE' ),

( 56, 'RID: 11:1:2937:8', 'Blocked', 0, 57, 'LCK_M_U',

' 2014-03-07 11:36:27.660', 2, 'DELETE' ),

( 55, 'RID: 11:1:2937:0', 'Blocked', 0, 56, 'LCK_M_S',

' 2014-03-07 11:49:10.520', 0, 'SELECT' );

WITH BlockingBehaviour

AS ( SELECT -- distinct

blocking.[spid] ,

blocking.[waitresource] ,

blocking.[typ] ,

blocking.[blocked] ,

blocking.[lastwaittype] ,

blocking.[login_time] ,

blocking.[open_tran] ,

blocking.[cmd] ,

1 AS block_level

FROM @blockinfo AS blocking

WHERE blocked = 0

UNION ALL

SELECT --distinct

blocked.[spid] ,

blocked.[waitresource] ,

blocked.[typ] ,

blocked.[blocked] ,

blocked.[lastwaittype] ,

blocked.[login_time] ,

blocked.[open_tran] ,

blocked.[cmd] ,

BB.block_level + 1

FROM @blockinfo AS blocked

INNER JOIN BlockingBehaviour AS BB ON blocked.blocked = bb.spid

)

SELECT DISTINCT

spid ,

waitresource ,

typ ,

blocked ,

lastwaittype ,

login_time ,

open_tran ,

cmd ,

block_level

FROM BlockingBehaviour

ORDER BY block_level ASC ,

typ DESC;

Notice I took the data you gave and put it in a table so I could provide a tested solution. I also think it would be more clear if the blocked column was named either blocked_by or blocker.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

simon_s Ten Centuries Points: 1358 More actions · Answer 2

thanks jack ...the only problem is it takes 1 hr 15 mins to run on just a 1000 records !!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

simon_s (3/10/2014)
thanks jack ...the only problem is it takes 1 hr 15 mins to run on just a 1000 records !!

Can you post the execution plan and the table schema with indexes?

A small help might be adding WHERE blocked.blocked > 0 in the UNION ALL of the query.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

simon_s Ten Centuries Points: 1358 More actions · Answer 4

sure here goes

also its still including duplicates 🙁

spidwaitresourcetypblockedlastwaittypelogin_timeopen_trancmdblock_level

57RID: 11:1:2937:8Blocking54LCK_M_U2014-03-07 11:27:34.1972UPDATE2

57RID: 11:1:2937:8Blocked54LCK_M_U2014-03-07 11:27:34.1972UPDATE2

CREATE TABLE [dbo].[Blocking_Detail2](

[spid] [int] NOT NULL,

[waitresource] [varchar](512) NULL,

[typ] [varchar](20) NULL,

[blocked] [int] NULL,

[lastwaittype] [nchar](32) NULL,

[login_time] [datetime] NULL,

[open_tran] [smallint] NULL,

[cmd] [nchar](16) NULL,

[block_level] [int] NULL

) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX ix_bd ON [Blocking_Detail2](spid)

CREATE nonCLUSTERED INDEX ix_bd2 ON [Blocking_Detail2](blocked)

CREATE NONCLUSTERED INDEX ix_bd3

ON [dbo].[Blocking_Detail2] ([blocked])

INCLUDE ([spid],[waitresource],[typ],[lastwaittype],[login_time],[open_tran],[cmd])

GO

WITH BlockingBehaviour

AS ( SELECT -- distinct

blocking.[spid] ,

blocking.[waitresource] ,

blocking.[typ] ,

blocking.[blocked] ,

blocking.[lastwaittype] ,

blocking.[login_time] ,

blocking.[open_tran] ,

blocking.[cmd] ,

1 AS block_level

FROM [DBA_Monitor].[dbo].[Blocking_Detail2] AS blocking

WHERE blocked = 0

UNION ALL

SELECT --distinct

blocked.[spid] ,

blocked.[waitresource] ,

blocked.[typ] ,

blocked.[blocked] ,

blocked.[lastwaittype] ,

blocked.[login_time] ,

blocked.[open_tran] ,

blocked.[cmd] ,

BB.block_level + 1

FROM [DBA_Monitor].[dbo].[Blocking_Detail2] AS blocked

INNER JOIN BlockingBehaviour AS BB ON blocked.blocked = bb.spid

WHERE blocked.blocked > 0

)

SELECT DISTINCT

spid ,

waitresource ,

typ ,

blocked ,

lastwaittype ,

login_time ,

open_tran ,

cmd ,

block_level

FROM BlockingBehaviour

ORDER BY block_level ASC ,

typ DESC;

<?xml version="1.0" encoding="utf-16"?>

<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.50.1617.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

<BatchSequence>

<Batch>

<Statements>

<StmtSimple StatementCompId="1" StatementEstRows="498" StatementId="1" StatementOptmLevel="FULL" StatementSubTreeCost="1.08186" StatementText="WITH BlockingBehaviour AS ( SELECT -- distinct blocking.[spid] , blocking.[waitresource] , blocking.[typ] , blocking.[blocked] , blocking.[lastwaittype] , blocking.[login_time] , blocking.[open_tran] , blocking.[cmd] , 1 AS block_level FROM [DBA_Monitor].[dbo].[Blocking_Detail2] AS blocking WHERE blocked = 0 UNION ALL SELECT --distinct blocked.[spid] , blocked.[waitresource] , blocked.[typ] , blocked.[blocked] , blocked.[lastwaittype] , blocked.[login_time] , blocked.[open_tran] , blocked.[cmd] , BB.block_level + 1 FROM [DBA_Monitor].[dbo].[Blocking_Detail2] AS blocked INNER JOIN BlockingBehaviour AS BB ON blocked.blocked = bb.spid WHERE blocked.blocked > 0 ) SELECT DISTINCT spid , waitresource , typ , blocked , lastwaittype , login_time , open_tran , cmd , block_level FROM BlockingBehaviour ORDER BY block_level ASC , typ DESC;" StatementType="SELECT" QueryHash="0x36E3389FB1EBE3DA" QueryPlanHash="0xA5B189FDDE239693">

<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

<QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="48" CompileTime="3" CompileCPU="3" CompileMemory="456">

<RelOp AvgRowSize="398" EstimateCPU="0.00706124" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="498" LogicalOp="Distinct Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1.08186">

<OutputList>

<ColumnReference Column="Recr1017" />

<ColumnReference Column="Recr1018" />

<ColumnReference Column="Recr1019" />

<ColumnReference Column="Recr1020" />

<ColumnReference Column="Recr1021" />

<ColumnReference Column="Recr1022" />

<ColumnReference Column="Recr1023" />

<ColumnReference Column="Recr1024" />

<ColumnReference Column="Recr1025" />

</OutputList>

<MemoryFractions Input="1" Output="1" />

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="5" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<Sort Distinct="true">

<OrderBy>

<OrderByColumn Ascending="true">

<ColumnReference Column="Recr1025" />

</OrderByColumn>

<OrderByColumn Ascending="false">

<ColumnReference Column="Recr1019" />

</OrderByColumn>

<OrderByColumn Ascending="true">

<ColumnReference Column="Recr1017" />

</OrderByColumn>

<OrderByColumn Ascending="true">

<ColumnReference Column="Recr1018" />

</OrderByColumn>

<OrderByColumn Ascending="true">

<ColumnReference Column="Recr1020" />

</OrderByColumn>

<OrderByColumn Ascending="true">

<ColumnReference Column="Recr1021" />

</OrderByColumn>

<OrderByColumn Ascending="true">

<ColumnReference Column="Recr1022" />

</OrderByColumn>

<OrderByColumn Ascending="true">

<ColumnReference Column="Recr1023" />

</OrderByColumn>

<OrderByColumn Ascending="true">

<ColumnReference Column="Recr1024" />

</OrderByColumn>

</OrderBy>

<RelOp AvgRowSize="398" EstimateCPU="2.49E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="498" LogicalOp="Lazy Spool" NodeId="1" Parallel="false" PhysicalOp="Index Spool" EstimatedTotalSubtreeCost="1.06354">

<OutputList>

<ColumnReference Column="Expr1029" />

<ColumnReference Column="Recr1017" />

<ColumnReference Column="Recr1018" />

<ColumnReference Column="Recr1019" />

<ColumnReference Column="Recr1020" />

<ColumnReference Column="Recr1021" />

<ColumnReference Column="Recr1022" />

<ColumnReference Column="Recr1023" />

<ColumnReference Column="Recr1024" />

<ColumnReference Column="Recr1025" />

</OutputList>

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="31369247" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<Spool Stack="true">

<RelOp AvgRowSize="398" EstimateCPU="4.98E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="498" LogicalOp="Concatenation" NodeId="2" Parallel="false" PhysicalOp="Concatenation" EstimatedTotalSubtreeCost="1.06145">

<OutputList>

<ColumnReference Column="Expr1029" />

<ColumnReference Column="Recr1017" />

<ColumnReference Column="Recr1018" />

<ColumnReference Column="Recr1019" />

<ColumnReference Column="Recr1020" />

<ColumnReference Column="Recr1021" />

<ColumnReference Column="Recr1022" />

<ColumnReference Column="Recr1023" />

<ColumnReference Column="Recr1024" />

<ColumnReference Column="Recr1025" />

</OutputList>

<RunTimeInformation>

<RunTimeCountersPerThread Thread="0" ActualRows="31369247" ActualEndOfScans="1" ActualExecutions="1" />

</RunTimeInformation>

<Concat>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1029" />

<ColumnReference Column="Expr1026" />

<ColumnReference Column="Expr1028" />

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Recr1017" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="spid" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="spid" />

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Recr1018" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="waitresource" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="waitresource" />

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Recr1019" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="typ" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="typ" />

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Recr1020" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="blocked" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="blocked" />

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Recr1021" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="lastwaittype" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="lastwaittype" />

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Recr1022" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="login_time" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="login_time" />

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Recr1023" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="open_tran" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="open_tran" />

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Recr1024" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="cmd" />

<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="cmd" />

</DefinedValue>

<DefinedValue>

<ColumnReference Column="Recr1025" />

<ColumnReference Column="Expr1003" />

<ColumnReference Column="Expr1016" />

</DefinedValue>

</DefinedValues>

<RelOp AvgRowSize="398" EstimateCPU="4.98E-06" EstimateIO="0" EstimateRebinds="498" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="4.98E-06">