|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Deadlocks "again"
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 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 Show quoteHide quote "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 > > > 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 Show quoteHide quote "Charl" <Ch***@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. -- Show quoteHide quoteAndrew 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" <Ch***@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 >>> >>> >>> > > 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 -- Show quoteHide quoteAndrew 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 > >  
Other interesting topics
Deadlock problem with insert trigger
Move DB by backup/restore or SP_Detach? How to "Grant" a Trigger ??? Table comparison "Timeout Expired" on large table change Installing SQL Server 2005 MSDE 2000 problems with linked server Licensing by the month? Q: Diagram in SQL Server Distributed Partioned Views between different SS versions |
|||||||||||||||||||||||
