Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hierarchical Tree View of blocking behaviour


Hierarchical Tree View of blocking behaviour

Author
Message
simon_s
simon_s
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 231
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

spid   waitresource   typ   ecid   blocked   lastwaittype   login_time   open_tran   cmd
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


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 !
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11008 Visits: 14858
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
simon_s
simon_s
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 231
thanks jack ...the only problem is it takes 1 hr 15 mins to run on just a 1000 records !!
Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11008 Visits: 14858
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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
simon_s
simon_s
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 231
sure here goes

also its still including duplicates :-(


spid   waitresource   typ   blocked   lastwaittype   login_time   open_tran   cmd   block_level
57   RID: 11:1:2937:8   Blocking   54   LCK_M_U   2014-03-07 11:27:34.197   2   UPDATE   2
57   RID: 11:1:2937:8   Blocked   54   LCK_M_U   2014-03-07 11:27:34.197   2   UPDATE   2



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>



Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11008 Visits: 14858
Okay. First I'd try an update stats on the base table to make sure all the estimates are right.

Those aren't really duplicates based on the logic in the query. Notice the data in the typ column, on the rows where there are duplicates there is a row with a value of "blocked" and row with a value of "blocking". So the query needs to be changed to either not include the typ column in the final output or only include "blocked" in the UNION ALL section of the CTE.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search