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 ««123»»

Deadlock on update command Expand / Collapse
Author
Message
Posted Monday, March 25, 2013 3:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:15 AM
Points: 184, Visits: 654
After applying that index update. I am now receiving another deadlock on that same table:

Node:1 
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x0000000204E9FA50
SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update orderha set status=2 where sessionid='7591791' and orderha=1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000000800BCE90 Mode: S SPID:63 BatchID:0 ECID:11 TaskProxy0x00000002E58F1A60) Value:0xff19db40 Cost0/0)

Node:2
PAGE: 9:1:3481582 CleanCnt:2 Mode:S Flags: 0x3
Grant List 1:
Owner:0x00000003D5CD8F40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x0000000080025680
SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select isnull(count(rsn),0) as value from orderha where status < 3 and exists (select rsn from printbatches where shippingloc in ('INT') and batch=orderha.printref)
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000204E9FA10 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy0x00000001C09FA538) Value:0x94aaf3c0 Cost0/220)

Node:3
Port: 0x00000000DF17A480 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000000D9BDC8, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:63 BatchID:0 ECID:0 TaskProxy0x00000000B69AC510) Value:0xd9bdc8 Cost0/10000)

Post #1434781
Posted Monday, March 25, 2013 3:42 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
bugg (3/25/2013)
After applying that index update. I am now receiving another deadlock on that same table:

Node:1 
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x0000000204E9FA50
SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update orderha set status=2 where sessionid='7591791' and orderha=1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000000800BCE90 Mode: S SPID:63 BatchID:0 ECID:11 TaskProxy0x00000002E58F1A60) Value:0xff19db40 Cost0/0)

Node:2
PAGE: 9:1:3481582 CleanCnt:2 Mode:S Flags: 0x3
Grant List 1:
Owner:0x00000003D5CD8F40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x0000000080025680
SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select isnull(count(rsn),0) as value from orderha where status < 3 and exists (select rsn from printbatches where shippingloc in ('INT') and batch=orderha.printref)
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000204E9FA10 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy0x00000001C09FA538) Value:0x94aaf3c0 Cost0/220)

Node:3
Port: 0x00000000DF17A480 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000000D9BDC8, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:63 BatchID:0 ECID:0 TaskProxy0x00000000B69AC510) Value:0xd9bdc8 Cost0/10000)



Can you post the missing bits please?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1434794
Posted Monday, March 25, 2013 4:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:15 AM
Points: 184, Visits: 654
ChrisM@Work (3/25/2013)
bugg (3/25/2013)
After applying that index update. I am now receiving another deadlock on that same table:

Node:1 
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x0000000204E9FA50
SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update orderha set status=2 where sessionid='7591791' and orderha=1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000000800BCE90 Mode: S SPID:63 BatchID:0 ECID:11 TaskProxy0x00000002E58F1A60) Value:0xff19db40 Cost0/0)

Node:2
PAGE: 9:1:3481582 CleanCnt:2 Mode:S Flags: 0x3
Grant List 1:
Owner:0x00000003D5CD8F40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x0000000080025680
SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select isnull(count(rsn),0) as value from orderha where status < 3 and exists (select rsn from printbatches where shippingloc in ('INT') and batch=orderha.printref)
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000204E9FA10 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy0x00000001C09FA538) Value:0x94aaf3c0 Cost0/220)

Node:3
Port: 0x00000000DF17A480 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000000D9BDC8, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:63 BatchID:0 ECID:0 TaskProxy0x00000000B69AC510) Value:0xd9bdc8 Cost0/10000)



Can you post the missing bits please?


Hi ChrisM, do you need the table def with indexes?
Post #1434802
Posted Monday, March 25, 2013 4:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:15 AM
Points: 184, Visits: 654
bugg (3/25/2013)
ChrisM@Work (3/25/2013)
bugg (3/25/2013)
After applying that index update. I am now receiving another deadlock on that same table:

Node:1 
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x0000000204E9FA50
SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update orderha set status=2 where sessionid='7591791' and orderha=1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000000800BCE90 Mode: S SPID:63 BatchID:0 ECID:11 TaskProxy0x00000002E58F1A60) Value:0xff19db40 Cost0/0)

Node:2
PAGE: 9:1:3481582 CleanCnt:2 Mode:S Flags: 0x3
Grant List 1:
Owner:0x00000003D5CD8F40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x0000000080025680
SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select isnull(count(rsn),0) as value from orderha where status < 3 and exists (select rsn from printbatches where shippingloc in ('INT') and batch=orderha.printref)
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000204E9FA10 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy0x00000001C09FA538) Value:0x94aaf3c0 Cost0/220)

Node:3
Port: 0x00000000DF17A480 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000000D9BDC8, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:63 BatchID:0 ECID:0 TaskProxy0x00000000B69AC510) Value:0xd9bdc8 Cost0/10000)



Can you post the missing bits please?


Hi ChrisM, do you need the table def with indexes?


Please see execution plans:

Select - http://s8.postimg.org/owmsmdlcl/exeplan1.png
Update - http://s17.postimg.org/jl20ih17z/exeplan2.png
Post #1434809
Posted Monday, March 25, 2013 4:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
bugg (3/25/2013)
ChrisM@Work (3/25/2013)
bugg (3/25/2013)
After applying that index update. I am now receiving another deadlock on that same table:

Node:1 
KEY: 9:72057602492792832 (860067b2b9d1) CleanCnt:2 Mode:X Flags: 0x1
Grant List 2:
Owner:0x0000000198215A40 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:73 ECID:0 XactLockInfo: 0x0000000204E9FA50
SPID: 73 ECID: 0 Statement Type: UPDATE Line #: 1
Input Buf: Language Event: update orderha set status=2 where sessionid='7591791' and orderha=1
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x00000000800BCE90 Mode: S SPID:63 BatchID:0 ECID:11 TaskProxy0x00000002E58F1A60) Value:0xff19db40 Cost0/0)

Node:2
PAGE: 9:1:3481582 CleanCnt:2 Mode:S Flags: 0x3
Grant List 1:
Owner:0x00000003D5CD8F40 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:63 ECID:0 XactLockInfo: 0x0000000080025680
SPID: 63 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select isnull(count(rsn),0) as value from orderha where status < 3 and exists (select rsn from printbatches where shippingloc in ('INT') and batch=orderha.printref)
Requested by:
ResType:LockOwner Stype:'OR'Xdes:0x0000000204E9FA10 Mode: IX SPID:73 BatchID:0 ECID:0 TaskProxy0x00000001C09FA538) Value:0x94aaf3c0 Cost0/220)

Node:3
Port: 0x00000000DF17A480 Xid Slot: 0, Wait Slot: -1, Task: 0x0000000000D9BDC8, (Coordinator), Exchange Wait Type: e_waitPipeGetRow, Merging: 0
ResType:ExchangeId Stype:'AND' SPID:63 BatchID:0 ECID:0 TaskProxy0x00000000B69AC510) Value:0xd9bdc8 Cost0/10000)



Can you post the missing bits please?


Hi ChrisM, do you need the table def with indexes?


You've already posted them. No, it looks like a part of the deadlock info is missing. Can you post the actual plans for both of the queries please?
You may well get some mileage by changing the update query as follows:
UPDATE orderha 
SET [status] = 2
WHERE sessionid = '7560129'
AND orderha = 1
AND [status] <> 2

The actual plan for this query would be useful too.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1434810
Posted Monday, March 25, 2013 4:44 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:15 AM
Points: 184, Visits: 654


You've already posted them. No, it looks like a part of the deadlock info is missing. Can you post the actual plans for both of the queries please?
You may well get some mileage by changing the update query as follows:
UPDATE orderha 
SET [status] = 2
WHERE sessionid = '7560129'
AND orderha = 1
AND [status] <> 2

The actual plan for this query would be useful too.


Plans for old queries in previous post, plan for new code below:

http://s9.postimg.org/ilcy4oi8v/exeplan3.png

<?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.0.1600.22" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1.0509" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0369085" StatementText="UPDATE [orderha] set [status] = @1 WHERE [sessionid]=@2 AND [orderha]=@3 AND [status]<>@4" StatementType="UPDATE" QueryHash="0xD91B15D2CAD0727F" QueryPlanHash="0x341CA7AF0267EE91">
<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" CachedPlanSize="40" CompileTime="5" CompileCPU="5" CompileMemory="328">
<RelOp AvgRowSize="9" EstimateCPU="3.15269E-06" EstimateIO="0.03" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Update" NodeId="1" Parallel="false" PhysicalOp="Clustered Index Update" EstimatedTotalSubtreeCost="0.0369085">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Index="[PK_orderha]" IndexKind="Clustered" />
<Object Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Index="[IX_sessionid]" IndexKind="NonClustered" />
<Object Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Index="[IX_shipopt_suspect_status_rsn_dateadded_oos]" IndexKind="NonClustered" />
<SetPredicate>
<ScalarOperator ScalarString="[DBxxxx].[dbo].[orderha].[status] = [Expr1021]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1021" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="27" EstimateCPU="1.0509E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00690538">
<OutputList>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1024" />
<ColumnReference Column="Expr1025" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1025" />
<ScalarOperator ScalarString="[Expr1025]">
<Identifier>
<ColumnReference Column="Expr1025" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1024" />
<ScalarOperator ScalarString="[Expr1024]">
<Identifier>
<ColumnReference Column="Expr1024" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="27" EstimateCPU="1.0509E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00690538">
<OutputList>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />
<ColumnReference Column="Expr1021" />
<ColumnReference Column="Expr1024" />
<ColumnReference Column="Expr1025" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1024" />
<ScalarOperator ScalarString="CASE WHEN [Expr1006] THEN (0) ELSE (1) END">
<IF>
<Condition>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1006" />
</Identifier>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1025" />
<ScalarOperator ScalarString="CASE WHEN [Expr1006] THEN (0) ELSE (1) END">
<IF>
<Condition>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1006" />
</Identifier>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="20" EstimateCPU="1.0509E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00690527">
<OutputList>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />
<ColumnReference Column="Expr1006" />
<ColumnReference Column="Expr1021" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1021" />
<ScalarOperator ScalarString="(2)">
<Const ConstValue="(2)" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="16" EstimateCPU="1.0509E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Compute Scalar" NodeId="5" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.00690517">
<OutputList>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />
<ColumnReference Column="Expr1006" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator ScalarString="CASE WHEN [DBxxxx].[dbo].[orderha].[status] = (2) THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="BINARY IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(2)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="19" EstimateCPU="1.0509E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Top" NodeId="6" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.00690506">
<OutputList>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="23" EstimateCPU="5.30974E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.27027" LogicalOp="Inner Join" NodeId="7" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00690435">
<OutputList>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />
</OuterReferences>
<RelOp AvgRowSize="19" EstimateCPU="0.000158397" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.27027" LogicalOp="Index Seek" NodeId="8" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032834" TableCardinality="3555030">
<OutputList>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />
</DefinedValue>
</DefinedValues>
<Object Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Index="[IX_sessionid]" IndexKind="NonClustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="sessionid" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="N'7560129'">
<Const ConstValue="N'7560129'" />
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[DBxxxx].[dbo].[orderha].[status]<(2) OR [DBxxxx].[dbo].[orderha].[status]>(2)">
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(2)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="status" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(2)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
<RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.270273" EstimateRewinds="0" EstimateRows="1.0509" LogicalOp="Clustered Index Seek" NodeId="10" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00361452" TableCardinality="3555030">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="0" />
</RunTimeInformation>
<IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues />
<Object Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Index="[PK_orderha]" TableReferenceId="-1" IndexKind="Clustered" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[DBxxxx].[dbo].[orderha].[rsn]">
<Identifier>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="rsn" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
<Predicate>
<ScalarOperator ScalarString="[DBxxxx].[dbo].[orderha].[orderha]=(1)">
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DBxxxx]" Schema="[dbo]" Table="[orderha]" Column="orderha" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</Top>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
<ParameterList>
<ColumnReference Column="@1" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)" />
<ColumnReference Column="@4" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)" />
<ColumnReference Column="@3" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
<ColumnReference Column="@2" ParameterCompiledValue="'7560129'" ParameterRuntimeValue="'7560129'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>

Post #1434816
Posted Monday, March 25, 2013 4:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
This article linked in my sig explains in detail how to post execution plans. They're active - .png graphics are not.
Right-click on the execution plan tab, select "Save Execution Plan As" and post as an attachment.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1434819
Posted Monday, March 25, 2013 4:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:15 AM
Points: 184, Visits: 654
Here is the deadlock info from the SQL log, unfirttunaltey it includes both deadlock flags. Due to my monitoring software

Date,Source,Severity,Message
03/25/2013 10:35:35,spid34s,Unknown,waiter id=processd4d708
03/25/2013 10:35:35,spid34s,Unknown,waiter-list
03/25/2013 10:35:35,spid34s,Unknown,owner id=process4fa0508
03/25/2013 10:35:35,spid34s,Unknown,owner-list
03/25/2013 10:35:35,spid34s,Unknown,exchangeEvent id=Pipe155556480 WaitType=e_waitPipeGetRow nodeId=3
03/25/2013 10:35:35,spid34s,Unknown,waiter id=process689b88 mode=IX requestType=wait
03/25/2013 10:35:35,spid34s,Unknown,waiter-list
03/25/2013 10:35:35,spid34s,Unknown,owner id=processd4d708 mode=S
03/25/2013 10:35:35,spid34s,Unknown,owner-list
03/25/2013 10:35:35,spid34s,Unknown,pagelock fileid=1 pageid=4013239 dbid=9 objectname=DBXXXX.dbo.orderha id=lock22ca4f380 mode=S associatedObjectId=72057609113042944
03/25/2013 10:35:35,spid34s,Unknown,waiter id=process4fa0508 mode=S requestType=wait
03/25/2013 10:35:35,spid34s,Unknown,waiter-list
03/25/2013 10:35:35,spid34s,Unknown,owner id=process689b88 mode=X
03/25/2013 10:35:35,spid34s,Unknown,owner-list
03/25/2013 10:35:35,spid34s,Unknown,keylock hobtid=72057602492792832 dbid=9 objectname=DBXXXX.dbo.orderha indexname =PK_orderhaid=lock2e17cd700 mode=X associatedObjectId=72057602492792832
03/25/2013 10:35:35,spid34s,Unknown,resource-list
03/25/2013 10:35:35,spid34s,Unknown,Select isnull(count(rsn)<c/>0) as value from orderha where status < 3 and <nl/> exists (select rsn from printbatches where shippingloc in ('GWM'<c/>'GWHUBM') and batch=orderha.printref)
03/25/2013 10:35:35,spid34s,Unknown,inputbuf
03/25/2013 10:35:35,spid34s,Unknown,Select isnull(count(rsn)<c/>0) as value from orderha where status < 3 and <nl/> exists (select rsn from printbatches where shippingloc in ('GWM'<c/>'GWHUBM') and batch=orderha.printref)
03/25/2013 10:35:35,spid34s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000b124a93432a05dac02c98dd52c4bc4a8623e4c71
03/25/2013 10:35:35,spid34s,Unknown,executionStack
03/25/2013 10:35:35,spid34s,Unknown,process id=processd4d708 taskpriority=0 logused=10000 waittime=2656 schedulerid=3 kpid=2888 status=suspended spid=52 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2013-03-25T10:35:33.040 lastbatchcompleted=2013-03-25T10:35:33.040 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 loginname=fusqldbuser isolationlevel=read committed (2) xactid=37813851141 currentdb=9 lockTimeout=4294967295
03/25/2013 10:35:35,spid34s,Unknown,update orderha set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='FN7592810' and orderha=1
03/25/2013 10:35:35,spid34s,Unknown,inputbuf
03/25/2013 10:35:35,spid34s,Unknown,update orderha set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='FN7592810' and orderha=1
03/25/2013 10:35:35,spid34s,Unknown,frame procname=adhoc line=1 sqlhandle=0x0200000003349709cef9f8e441641eabcec39ed43894f7ae
03/25/2013 10:35:35,spid34s,Unknown,(@1 int<c/>@2 int<c/>@3 varchar(8000)<c/>@4 tinyint)UPDATE [orderha] set [shipped] = @1<c/>[status] = @2<c/>[shipdate] = getdate() WHERE [sessionid]%%=%%@3 AND [orderha]%%=%%@4
03/25/2013 10:35:35,spid34s,Unknown,frame procname=adhoc line=1 sqlhandle=0x020000006d90e92510cd4c7f9a9af4c3c7d68bb4cd95914a
03/25/2013 10:35:35,spid34s,Unknown,executionStack
03/25/2013 10:35:35,spid34s,Unknown,process id=process689b88 taskpriority=0 logused=292 waitresource=PAGE: 9:1:4013239 waittime=2809 ownerId=37813851204 transactionname=UPDATE lasttranstarted=2013-03-25T10:35:33.113 XDES=0x148857970 lockMode=IX schedulerid=1 kpid=6156 status=suspended spid=59 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-03-25T10:35:33.107 lastbatchcompleted=2013-03-25T10:35:33.107 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 loginname=fusqldbuser isolationlevel=read committed (2) xactid=37813851204 currentdb=9 lockTimeout=4294967295
03/25/2013 10:35:35,spid34s,Unknown,inputbuf
03/25/2013 10:35:35,spid34s,Unknown,Select isnull(count(rsn)<c/>0) as value from orderha where status < 3 and <nl/> exists (select rsn from printbatches where shippingloc in ('GWM'<c/>'GWHUBM') and batch=orderha.printref)
03/25/2013 10:35:35,spid34s,Unknown,frame procname=adhoc line=1 sqlhandle=0x02000000b124a93432a05dac02c98dd52c4bc4a8623e4c71
03/25/2013 10:35:35,spid34s,Unknown,executionStack
03/25/2013 10:35:35,spid34s,Unknown,process id=process4fa0508 taskpriority=0 logused=0 waitresource=KEY: 9:72057602492792832 (f9008448af4c) waittime=2709 ownerId=37813851141 transactionname=SELECT lasttranstarted=2013-03-25T10:35:33.080 XDES=0x1b7502b60 lockMode=S schedulerid=14 kpid=236 status=suspended spid=52 sbid=0 ecid=13 priority=0 trancount=0 lastbatchstarted=2013-03-25T10:35:33.040 lastbatchcompleted=2013-03-25T10:35:33.040 clientapp=PHP 5 hostname=fuweb03 hostpid=227446 isolationlevel=read committed (2) xactid=37813851141 currentdb=9 lockTimeout=4294967295
03/25/2013 10:35:35,spid34s,Unknown,process-list
03/25/2013 10:35:35,spid34s,Unknown,deadlock victim=process4fa0508
03/25/2013 10:35:35,spid34s,Unknown,deadlock-list
03/25/2013 10:35:35,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001B7502B60 Mode: S SPID:52 BatchID:0 ECID:13 TaskProxy0x00000001243D7BD0) Value:0xb6996cc0 Cost0/0)
03/25/2013 10:35:35,spid5s,Unknown,Victim Resource Owner:
03/25/2013 10:35:35,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
03/25/2013 10:35:35,spid5s,Unknown,ResType:ExchangeId Stype:'AND' SPID:52 BatchID:0 ECID:0 TaskProxy0x000000023373C510) Value:0xd4d708 Cost0/10000)
03/25/2013 10:35:35,spid5s,Unknown,Port: 0x0000000155556480 Xid Slot: 0<c/> Wait Slot: -1<c/> Task: 0x0000000000D4D708<c/> (Coordinator)<c/> Exchange Wait Type: e_waitPipeGetRow<c/> Merging: 0
03/25/2013 10:35:35,spid5s,Unknown,Node:3
03/25/2013 10:35:35,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
03/25/2013 10:35:35,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000148857970 Mode: IX SPID:59 BatchID:0 ECID:0 TaskProxy0x000000014A4F0538) Value:0xcf1395c0 Cost0/292)
03/25/2013 10:35:35,spid5s,Unknown,Requested by:
03/25/2013 10:35:35,spid5s,Unknown,Input Buf: Language Event: Select isnull(count(rsn)<c/>0) as value from orderha where status < 3 and <nl/> exists (select rsn from printbatches where shippingloc in ('GWM'<c/>'GWHUBM') and batch=orderha.printref)
03/25/2013 10:35:35,spid5s,Unknown,SPID: 52 ECID: 0 Statement Type: SELECT Line #: 1
03/25/2013 10:35:35,spid5s,Unknown,Owner:0x0000000224960B80 Mode: S Flg:0x40 Ref:0 Life:00000001 SPID:52 ECID:0 XactLockInfo: 0x00000000800A5940
03/25/2013 10:35:35,spid5s,Unknown,Grant List 0:
03/25/2013 10:35:35,spid5s,Unknown,PAGE: 9:1:4013239 CleanCnt:2 Mode:S Flags: 0x3
03/25/2013 10:35:35,spid5s,Unknown,Node:2
03/25/2013 10:35:35,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
03/25/2013 10:35:35,spid5s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000001B7502B60 Mode: S SPID:52 BatchID:0 ECID:13 TaskProxy0x00000001243D7BD0) Value:0xb6996cc0 Cost0/0)
03/25/2013 10:35:35,spid5s,Unknown,Requested by:
03/25/2013 10:35:35,spid5s,Unknown,Input Buf: Language Event: update orderha set shipped=1<c/>status=3<c/>shipdate=getdate() where sessionid='FN7592810' and orderha=1
03/25/2013 10:35:35,spid5s,Unknown,SPID: 59 ECID: 0 Statement Type: UPDATE Line #: 1
03/25/2013 10:35:35,spid5s,Unknown,Owner:0x00000001D6758340 Mode: X Flg:0x40 Ref:1 Life:02000000 SPID:59 ECID:0 XactLockInfo: 0x00000001488579B0
03/25/2013 10:35:35,spid5s,Unknown,Grant List 0:
03/25/2013 10:35:35,spid5s,Unknown,KEY: 9:72057602492792832 (f9008448af4c) CleanCnt:2 Mode:X Flags: 0x1
03/25/2013 10:35:35,spid5s,Unknown,Node:1
03/25/2013 10:35:35,spid5s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:
03/25/2013 10:35:35,spid5s,Unknown,Wait-for graph
03/25/2013 10:35:35,spid5s,Unknown,Deadlock encountered .... Printing deadlock information


Post #1434823
Posted Monday, March 25, 2013 5:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 15, 2014 6:15 AM
Points: 184, Visits: 654
ChrisM@Work (3/25/2013)
This article linked in my sig explains in detail how to post execution plans. They're active - .png graphics are not.
Right-click on the execution plan tab, select "Save Execution Plan As" and post as an attachment.


Now attached plan. let me know if you need anything else appreciate your help on this.

thanks


  Post Attachments 
orderha.sqlplan (3 views, 62.84 KB)
Post #1434830
Posted Monday, March 25, 2013 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:44 AM
Points: 6,719, Visits: 13,828
OK got it. You need to change two indexes.
1. Include orderhash and status in index IX_SessionID, like this:
CREATE NONCLUSTERED INDEX [IX_sessionid] ON [dbo].[orderha] 
([sessionid] ASC, OrderHash ASC) INCLUDE (Status)

2. Include printref in index IX_suspect_status_rsn_shipopt, like this:
CREATE NONCLUSTERED INDEX [IX_suspect_status_rsn_shipopt] ON [dbo].[orderha] 
([suspectorder] ASC,
[status] ASC,
[rsn] ASC,
[shipopt] ASC) INCLUDE (PrintRef)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1434843
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse