Deadlocks "again"

Hi
I am getting the same deadlock on our SQL 2K server but with different SPID
Ids throughout the day.
I know what tables and Index information from the KEY: information , and
that is it performing an exclusive lock. What i cannot figure out is what
to do to help resolve this.
Do anyone have any tips? Here is the deadlock results: -
Deadlock encountered ... Printing deadlock information
Wait-for graph
Node:1
KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
Grant List 1::
Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0 Ec:(0x77D6D550)
Value:0x46951760 Cost:(0/F08)
Node:2
KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
Grant List 2::
Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: RPC Event: sp_execute;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
Value:0x51983620 Cost:(0/714)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
Value:0x51983620 Cost:(0/714)
Many ThanksHi Hush
Have you identified the SQL statements that cause the deadlock and tried to
optimize the queries/indexes so that the locks resolve faster. The dump
specify the RPC event as sp_execute,1. Are you executing dynamic SQL ?
Regards
Charl
--
http://www.sqlserver.co.za
"Hush" wrote:
> Hi
> I am getting the same deadlock on our SQL 2K server but with different SPID
> Ids throughout the day.
> I know what tables and Index information from the KEY: information , and
> that is it performing an exclusive lock. What i cannot figure out is what
> to do to help resolve this.
> Do anyone have any tips? Here is the deadlock results: -
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0 Ec:(0x77D6D550)
> Value:0x46951760 Cost:(0/F08)
> Node:2
> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 2::
> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
> Value:0x51983620 Cost:(0/714)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
> Value:0x51983620 Cost:(0/714)
>
> Many Thanks
>
>|||See if these help:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp
Troubleshooting Deadlocks
http://www.support.microsoft.com/?id=224453 Blocking Problems
http://www.support.microsoft.com/?id=271509 How to monitor SQL 2000
Blocking
--
Andrew J. Kelly SQL MVP
"Hush" <hushdontspamme@.hotmail.com> wrote in message
news:uzWvhdOSGHA.5156@.TK2MSFTNGP10.phx.gbl...
> Hi
> I am getting the same deadlock on our SQL 2K server but with different
> SPID Ids throughout the day.
> I know what tables and Index information from the KEY: information , and
> that is it performing an exclusive lock. What i cannot figure out is what
> to do to help resolve this.
> Do anyone have any tips? Here is the deadlock results: -
> Deadlock encountered ... Printing deadlock information
> Wait-for graph
> Node:1
> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 1::
> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64 ECID:0
> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0
> Ec:(0x77D6D550) Value:0x46951760 Cost:(0/F08)
> Node:2
> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
> Grant List 2::
> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61 ECID:0
> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
> Input Buf: RPC Event: sp_execute;1
> Requested By:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
> Value:0x51983620 Cost:(0/714)
> Victim Resource Owner:
> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
> Value:0x51983620 Cost:(0/714)
>
> Many Thanks
>|||Hi Charl
Thanks for your reply:
I am currently running a SQL Profiler - How do i look for the RPC event? I
have included Excution Plan and the SQL locks events in the profiler.
What i am alittle concerned about is that the table in question has on index
(Clustered Index) and out of the 7 Columns in the table 6 of them is
assigned to clustered index. Is that good practice?
I am a non SQL Developer DBA....so i am a little stuck with this.
Many Thanks
"Charl" <Charl@.discussions.microsoft.com> wrote in message
news:F38AA9E6-F580-4F62-A3D6-93C70375D145@.microsoft.com...
> Hi Hush
> Have you identified the SQL statements that cause the deadlock and tried
> to
> optimize the queries/indexes so that the locks resolve faster. The dump
> specify the RPC event as sp_execute,1. Are you executing dynamic SQL ?
> Regards
> Charl
> --
> http://www.sqlserver.co.za
>
> "Hush" wrote:
>> Hi
>> I am getting the same deadlock on our SQL 2K server but with different
>> SPID
>> Ids throughout the day.
>> I know what tables and Index information from the KEY: information , and
>> that is it performing an exclusive lock. What i cannot figure out is
>> what
>> to do to help resolve this.
>> Do anyone have any tips? Here is the deadlock results: -
>> Deadlock encountered ... Printing deadlock information
>> Wait-for graph
>> Node:1
>> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
>> Grant List 1::
>> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64
>> ECID:0
>> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
>> Input Buf: RPC Event: sp_execute;1
>> Requested By:
>> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0
>> Ec:(0x77D6D550)
>> Value:0x46951760 Cost:(0/F08)
>> Node:2
>> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
>> Grant List 2::
>> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61
>> ECID:0
>> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
>> Input Buf: RPC Event: sp_execute;1
>> Requested By:
>> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
>> Value:0x51983620 Cost:(0/714)
>> Victim Resource Owner:
>> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
>> Value:0x51983620 Cost:(0/714)
>>
>> Many Thanks
>>|||While there are always exceptions having that many columns in a clustered
index is usually not a good idea. Since the leaf level of the clustered
index is the actual data there is little to gain by clustering on so many
columns. Usually you can make the index selective enough on just a few and
it is always a good idea to keep the clustered index narrow since the
clustered index key is appended to the end of any nonclustered indexes as
well. If you are updating any of the 6 columns in the clustered index there
is a prime candidate for deadlocks since it has to physically move the
entire row.
--
Andrew J. Kelly SQL MVP
"Hush" <hushdontspamme@.hotmail.com> wrote in message
news:ekTgocPSGHA.424@.TK2MSFTNGP12.phx.gbl...
> Hi Charl
> Thanks for your reply:
> I am currently running a SQL Profiler - How do i look for the RPC event?
> I have included Excution Plan and the SQL locks events in the profiler.
> What i am alittle concerned about is that the table in question has on
> index (Clustered Index) and out of the 7 Columns in the table 6 of them is
> assigned to clustered index. Is that good practice?
> I am a non SQL Developer DBA....so i am a little stuck with this.
> Many Thanks
>
> "Charl" <Charl@.discussions.microsoft.com> wrote in message
> news:F38AA9E6-F580-4F62-A3D6-93C70375D145@.microsoft.com...
>> Hi Hush
>> Have you identified the SQL statements that cause the deadlock and tried
>> to
>> optimize the queries/indexes so that the locks resolve faster. The dump
>> specify the RPC event as sp_execute,1. Are you executing dynamic SQL ?
>> Regards
>> Charl
>> --
>> http://www.sqlserver.co.za
>>
>> "Hush" wrote:
>> Hi
>> I am getting the same deadlock on our SQL 2K server but with different
>> SPID
>> Ids throughout the day.
>> I know what tables and Index information from the KEY: information , and
>> that is it performing an exclusive lock. What i cannot figure out is
>> what
>> to do to help resolve this.
>> Do anyone have any tips? Here is the deadlock results: -
>> Deadlock encountered ... Printing deadlock information
>> Wait-for graph
>> Node:1
>> KEY: 6:1618820829:1 (7d009bbca7e6) CleanCnt:2 Mode: X Flags: 0x0
>> Grant List 1::
>> Owner:0x51983760 Mode: X Flg:0x0 Ref:1 Life:02000000 SPID:64
>> ECID:0
>> SPID: 64 ECID: 0 Statement Type: UPDATE Line #: 136
>> Input Buf: RPC Event: sp_execute;1
>> Requested By:
>> ResType:LockOwner Stype:'OR' Mode: Range-S-U SPID:61 ECID:0
>> Ec:(0x77D6D550)
>> Value:0x46951760 Cost:(0/F08)
>> Node:2
>> KEY: 6:1831013604:1 (0c0021774e53) CleanCnt:2 Mode: X Flags: 0x0
>> Grant List 2::
>> Owner:0x2faa6340 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:61
>> ECID:0
>> SPID: 61 ECID: 0 Statement Type: SELECT Line #: 1
>> Input Buf: RPC Event: sp_execute;1
>> Requested By:
>> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
>> Value:0x51983620 Cost:(0/714)
>> Victim Resource Owner:
>> ResType:LockOwner Stype:'OR' Mode: X SPID:64 ECID:0 Ec:(0x7E9EF550)
>> Value:0x51983620 Cost:(0/714)
>>
>> Many Thanks
>>
>