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">
<OutputList>
<ColumnReference Column="Expr1026" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="spid" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="waitresource" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="typ" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="blocked" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="lastwaittype" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="login_time" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="open_tran" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="cmd" />
<ColumnReference Column="Expr1003" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1026" />
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="398" EstimateCPU="2.47E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="247" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00649692">
<OutputList>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="spid" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="waitresource" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="typ" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="blocked" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="lastwaittype" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="login_time" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="open_tran" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="cmd" />
<ColumnReference Column="Expr1003" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1003" />
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="393" EstimateCPU="0.0004287" EstimateIO="0.00604352" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="247" LogicalOp="Index Seek" NodeId="5" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00647222" TableCardinality="1000">
<OutputList>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="spid" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="waitresource" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="typ" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="blocked" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="lastwaittype" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="login_time" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="open_tran" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="cmd" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="247" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="spid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="waitresource" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="typ" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="blocked" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="lastwaittype" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="login_time" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="open_tran" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="cmd" />
</DefinedValue>
</DefinedValues>
<Object Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Index="[ix_bd3]" Alias="[blocking]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocking]" Column="blocked" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="398" EstimateCPU="4.1832E-05" EstimateIO="0" EstimateRebinds="498" EstimateRewinds="0" EstimateRows="2.01619" LogicalOp="Assert" NodeId="12" Parallel="false" PhysicalOp="Assert" EstimatedTotalSubtreeCost="1.05496">
<OutputList>
<ColumnReference Column="Expr1028" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="spid" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="waitresource" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="typ" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="blocked" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="lastwaittype" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="login_time" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="open_tran" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="cmd" />
<ColumnReference Column="Expr1016" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="31369000" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Assert StartupExpression="false">
<RelOp AvgRowSize="398" EstimateCPU="4.1832E-05" EstimateIO="0" EstimateRebinds="498" EstimateRewinds="0" EstimateRows="2.01619" LogicalOp="Inner Join" NodeId="13" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="1.05496">
<OutputList>
<ColumnReference Column="Expr1028" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="spid" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="waitresource" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="typ" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="blocked" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="lastwaittype" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="login_time" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="open_tran" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="cmd" />
<ColumnReference Column="Expr1016" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="31369000" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Column="Expr1028" />
<ColumnReference Column="Recr1007" />
<ColumnReference Column="Recr1008" />
<ColumnReference Column="Recr1009" />
<ColumnReference Column="Recr1010" />
<ColumnReference Column="Recr1011" />
<ColumnReference Column="Recr1012" />
<ColumnReference Column="Recr1013" />
<ColumnReference Column="Recr1014" />
<ColumnReference Column="Recr1015" />
</OuterReferences>
<RelOp AvgRowSize="398" EstimateCPU="4.98E-06" EstimateIO="0" EstimateRebinds="498" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="14" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="4.98E-06">
<OutputList>
<ColumnReference Column="Expr1028" />
<ColumnReference Column="Recr1007" />
<ColumnReference Column="Recr1008" />
<ColumnReference Column="Recr1009" />
<ColumnReference Column="Recr1010" />
<ColumnReference Column="Recr1011" />
<ColumnReference Column="Recr1012" />
<ColumnReference Column="Recr1013" />
<ColumnReference Column="Recr1014" />
<ColumnReference Column="Recr1015" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1028" />
<ScalarOperator ScalarString="[Expr1027]+(1)">
<Arithmetic Operation="ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1027" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="398" EstimateCPU="4.98E-06" EstimateIO="0" EstimateRebinds="498" EstimateRewinds="0" EstimateRows="1" LogicalOp="Lazy Spool" NodeId="15" Parallel="false" PhysicalOp="Table Spool" EstimatedTotalSubtreeCost="4.98E-06">
<OutputList>
<ColumnReference Column="Expr1027" />
<ColumnReference Column="Recr1007" />
<ColumnReference Column="Recr1008" />
<ColumnReference Column="Recr1009" />
<ColumnReference Column="Recr1010" />
<ColumnReference Column="Recr1011" />
<ColumnReference Column="Recr1012" />
<ColumnReference Column="Recr1013" />
<ColumnReference Column="Recr1014" />
<ColumnReference Column="Recr1015" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="31369247" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Spool Stack="true" PrimaryNodeId="1" />
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="398" EstimateCPU="2.51E-05" EstimateIO="0" EstimateRebinds="497" EstimateRewinds="0" EstimateRows="251" LogicalOp="Compute Scalar" NodeId="19" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="1.05491">
<OutputList>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="spid" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="waitresource" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="typ" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="blocked" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="lastwaittype" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="login_time" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="open_tran" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="cmd" />
<ColumnReference Column="Expr1016" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1016" />
<ScalarOperator ScalarString="[Recr1015]+(1)">
<Arithmetic Operation="ADD">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Recr1015" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="393" EstimateCPU="0.0011785" EstimateIO="0.0172776" EstimateRebinds="0" EstimateRewinds="497" EstimateRows="251" LogicalOp="Clustered Index Scan" NodeId="20" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.604171" TableCardinality="1000">
<OutputList>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="spid" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="waitresource" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="typ" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="blocked" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="lastwaittype" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="login_time" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="open_tran" />
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="cmd" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="31369000" ActualEndOfScans="31369247" ActualExecutions="31369247" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="spid" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="waitresource" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="typ" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="blocked" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="lastwaittype" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="login_time" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="open_tran" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="cmd" />
</DefinedValue>
</DefinedValues>
<Object Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Index="[ix_bd]" Alias="[blocked]" IndexKind="Clustered" />
<Predicate>
<ScalarOperator ScalarString="[DBA_Monitor].[dbo].[Blocking_Detail2].[blocked] as [blocked].[blocked]=[Recr1007] AND [DBA_Monitor].[dbo].[Blocking_Detail2].[blocked] as [blocked].[blocked]>(0)">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="blocked" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Recr1007" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DBA_Monitor]" Schema="[dbo]" Table="[Blocking_Detail2]" Alias="[blocked]" Column="blocked" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</NestedLoops>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="CASE WHEN [Expr1028]>(100) THEN (0) ELSE NULL END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1028" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(100)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Predicate>
</Assert>
</RelOp>
</Concat>
</RelOp>
</Spool>
</RelOp>
</Sort>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>