|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Statistics DeadlockingI have a statistic that has been created by SQL Server automatically that is
deadlocking. I understand how it deadlocks but would like some advice on the best fix. My thoughts are dropping the statistic or creating an index on the column. Has anyone else had experience with stats deadlocking? If so, what was your fix? Thanks in advance. JI (jida***@gmail.com) writes:
> I have a statistic that has been created by SQL Server automatically No, I've heard of that, and I don't understand how it happens. I would> that is deadlocking. I understand how it deadlocks but would like some > advice on the best fix. My thoughts are dropping the statistic or > creating an index on the column. > > Has anyone else had experience with stats deadlocking? If so, what was > your fix? be curious to know more about it. What does the deadlock trace say? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx If you take index id 5 for object id 242972092, you will see it is named
_WA_Sys_ShipmentStatusId_0E7B75BC and it has been automatically created by sql server. I do know there is a fix in SP4 for explicit update_statistics proc (http://support.microsoft.com/kb/826754). Here is the deadlock trace info that I am seeing: 01/12/2006 13:36:30,spid4,Unknown,Node:1 01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:1 (de001a40a963) CleanCnt:1 Mode: X Flags: 0x0 01/12/2006 13:36:30,spid4,Unknown,Grant List 3:: 01/12/2006 13:36:30,spid4,Unknown,Owner:0x2e84d360 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:61 ECID:0 01/12/2006 13:36:30,spid4,Unknown,SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 82 01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event: up_updateShipmentRequestLine;1 01/12/2006 13:36:30,spid4,Unknown,Requested By: 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: S SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0) 01/12/2006 13:36:30,spid4,Unknown, 01/12/2006 13:36:30,spid4,Unknown,Node:2 01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:25 (be027cd3b404) CleanCnt:1 Mode: S Flags: 0x0 01/12/2006 13:36:30,spid4,Unknown,Grant List 3:: 01/12/2006 13:36:30,spid4,Unknown,Owner:0x4e241860 Mode: S Flg:0x0 Ref:0 Life:02000000 SPID:56 ECID:0 01/12/2006 13:36:30,spid4,Unknown,SPID: 56 ECID: 0 Statement Type: SELECT Line #: 9 01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event: up_findShipmentRequestLineByShipmentRequestNumberAndLineNumber;1 01/12/2006 13:36:30,spid4,Unknown,Requested By: 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: X SPID:61 ECID:0 Ec:(0x235D5528) Value:0x5bc13d40 Cost:(0/C4) 01/12/2006 13:36:30,spid4,Unknown,Victim Resource Owner: 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: S SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0) Show quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns974AC0F94F5E4Yazorman@127.0.0.1... > JI (jida***@gmail.com) writes: >> I have a statistic that has been created by SQL Server automatically >> that is deadlocking. I understand how it deadlocks but would like some >> advice on the best fix. My thoughts are dropping the statistic or >> creating an index on the column. >> >> Has anyone else had experience with stats deadlocking? If so, what was >> your fix? > > No, I've heard of that, and I don't understand how it happens. I would > be curious to know more about it. What does the deadlock trace say? > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx JI (jida***@gmail.com) writes:
> If you take index id 5 for object id 242972092, you will see it is named The only thing I can think of is that one of the processes triggers an> _WA_Sys_ShipmentStatusId_0E7B75BC and it has been automatically created by > sql server. I do know there is a fix in SP4 for explicit update_statistics > proc (http://support.microsoft.com/kb/826754). Here is the deadlock trace > info that I am seeing: auto-update of the statistics, and the other process gets in the way. One thing you could try is to turn of auto-statistics for the table with sp_autostats. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi JI
I don't see any reference to index id 5. Keys are referenced by <dbid>:object-Id>:<index_id>, so there are references for index 1 (the clustered index) and index 25, on object with id 242972092 in database with id 5. Index 25 may be statistics, but you should verify with the correct numbers and make sure you are in the correct database.. The other interesting feature here is that one of the processes involved is spid4, which is a system process. So it may very well the auto update stats process. Did you try turning that off as suggested by Erland? How often is this happening? Show quote "JI" <jida***@gmail.com> wrote in message news:eL5aPaHGGHA.2704@TK2MSFTNGP15.phx.gbl... > > If you take index id 5 for object id 242972092, you will see it is named > _WA_Sys_ShipmentStatusId_0E7B75BC and it has been automatically created by > sql server. I do know there is a fix in SP4 for explicit update_statistics > proc (http://support.microsoft.com/kb/826754). Here is the deadlock trace > info that I am seeing: > > 01/12/2006 13:36:30,spid4,Unknown,Node:1 > 01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:1 (de001a40a963) > CleanCnt:1 Mode: X Flags: 0x0 > 01/12/2006 13:36:30,spid4,Unknown,Grant List 3:: > 01/12/2006 13:36:30,spid4,Unknown,Owner:0x2e84d360 Mode: X Flg:0x0 > Ref:0 Life:02000000 SPID:61 ECID:0 > 01/12/2006 13:36:30,spid4,Unknown,SPID: 61 ECID: 0 Statement Type: UPDATE > Line #: 82 > 01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event: > up_updateShipmentRequestLine;1 > 01/12/2006 13:36:30,spid4,Unknown,Requested By: > 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: S > SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0) > 01/12/2006 13:36:30,spid4,Unknown, > 01/12/2006 13:36:30,spid4,Unknown,Node:2 > 01/12/2006 13:36:30,spid4,Unknown,KEY: 5:242972092:25 (be027cd3b404) > CleanCnt:1 Mode: S Flags: 0x0 > 01/12/2006 13:36:30,spid4,Unknown,Grant List 3:: > 01/12/2006 13:36:30,spid4,Unknown,Owner:0x4e241860 Mode: S Flg:0x0 > Ref:0 Life:02000000 SPID:56 ECID:0 > 01/12/2006 13:36:30,spid4,Unknown,SPID: 56 ECID: 0 Statement Type: SELECT > Line #: 9 > 01/12/2006 13:36:30,spid4,Unknown,Input Buf: RPC Event: > up_findShipmentRequestLineByShipmentRequestNumberAndLineNumber;1 > 01/12/2006 13:36:30,spid4,Unknown,Requested By: > 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: X > SPID:61 ECID:0 Ec:(0x235D5528) Value:0x5bc13d40 Cost:(0/C4) > 01/12/2006 13:36:30,spid4,Unknown,Victim Resource Owner: > 01/12/2006 13:36:30,spid4,Unknown,ResType:LockOwner Stype:'OR' Mode: S > SPID:56 ECID:0 Ec:(0x52255528) Value:0x71aa7cc0 Cost:(0/0) > > > > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns974AC0F94F5E4Yazorman@127.0.0.1... >> JI (jida***@gmail.com) writes: >>> I have a statistic that has been created by SQL Server automatically >>> that is deadlocking. I understand how it deadlocks but would like some >>> advice on the best fix. My thoughts are dropping the statistic or >>> creating an index on the column. >>> >>> Has anyone else had experience with stats deadlocking? If so, what was >>> your fix? >> >> No, I've heard of that, and I don't understand how it happens. I would >> be curious to know more about it. What does the deadlock trace say? >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > >  |
|||||||||||||||||||||||
Other interesting topics