RE: Hierarchical Tree View of blocking behaviour

simon_s

Ten Centuries

Points: 1354

March 10, 2014 at 11:38 am

#1695682

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]

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])

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">