Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Hierarchical Tree View of blocking behaviour Expand / Collapse
Author
Message
Posted Friday, March 7, 2014 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 16, 2014 5:10 AM
Points: 13, Visits: 77
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 !

Post #1548747
Posted Friday, March 7, 2014 11:43 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 11,219, Visits: 12,976
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

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
Post #1548840
Posted Monday, March 10, 2014 8:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 16, 2014 5:10 AM
Points: 13, Visits: 77
thanks jack ...the only problem is it takes 1 hr 15 mins to run on just a 1000 records !!
Post #1549252
Posted Monday, March 10, 2014 8:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 11,219, Visits: 12,976
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

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
Post #1549258
Posted Monday, March 10, 2014 11:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 16, 2014 5:10 AM
Points: 13, Visits: 77
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>


Post #1549407
Posted Monday, March 10, 2014 12:13 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:20 PM
Points: 11,219, Visits: 12,976
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

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
Post #1549425
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse