August 6, 2011 at 11:20 am
Hi,
We have SharePoint databases on SQL Server 2005 and we are using Quest spotlight for monitoring sql server. We got a below dead lock and which terminated our important production task.
What causing the dead lock here? what exact sql statement caused the deadlock?and how to identify that in the dead lock graph?
Is this due to Spotlight? Please advice
spid17s deadlock-list
spid15s deadlock-list
spid17s deadlock victim=process3c26da8
spid15s deadlock victim=process3c1cda8
spid17s process-list
spid15s process-list
spid15s process id=process3c09978 taskpriority=0 logused=493815816 waitresource=KEY: 11:281474978938880 (7f00619c34fb) waittime=3781 ownerId=2710782549 transactionname=ALTER INDEX lasttranstarted=2011-08-06T00:31:35.843 XDES=0x80055470 lockMode=X schedulerid=1 kpid=3556 status=suspended spid=182 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2011-08-06T00:30:55.920 lastbatchcompleted=2011-08-06T00:30:55.920 clientapp=.Net SqlClient Data Provider hostname=SP2 hostpid=6024 loginname=abc\mossadmin isolationlevel=read committed (2) xactid=2710782549 currentdb=11 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
spid17s process id=process3c09978 taskpriority=0 logused=493815816 waitresource=KEY: 11:281474978938880 (7f00619c34fb) waittime=3781 ownerId=2710782549 transactionname=ALTER INDEX lasttranstarted=2011-08-06T00:31:35.843 XDES=0x80055470 lockMode=X schedulerid=1 kpid=3556 status=suspended spid=182 sbid=0 ecid=0 priority=0 transcount=1 lastbatchstarted=2011-08-06T00:30:55.920 lastbatchcompleted=2011-08-06T00:30:55.920 clientapp=.Net SqlClient Data Provider hostname=SP2 hostpid=6024 loginname=abc\mossadmin isolationlevel=read committed (2) xactid=2710782549 currentdb=11 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
spid15s executionStack
spid17s executionStack
spid15s frame procname=adhoc line=1 stmtstart=372 stmtend=556 sqlhandle=0x01000b002a16372280bc1994010000000000000000000000
spid17s frame procname=adhoc line=1 stmtstart=372 stmtend=556 sqlhandle=0x01000b002a16372280bc1994010000000000000000000000
spid15s ALTER INDEX AllUserData_Url ON dbo.AllUserData REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF)
spid17s ALTER INDEX AllUserData_Url ON dbo.AllUserData REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF)
spid15s frame procname=WSS_Content_DB.dbo.proc_DefragmentIndices line=75 stmtstart=4926 stmtend=4974 sqlhandle=0x03000b0020586a2b48ebd500c89e00000100000000000000
spid17s frame procname=WSS_Content_DB.dbo.proc_DefragmentIndices line=75 stmtstart=4926 stmtend=4974 sqlhandle=0x03000b0020586a2b48ebd500c89e00000100000000000000
spid15s EXEC (@command)
spid17s EXEC (@command)
spid15s inputbuf
spid17s inputbuf
spid15s Proc [Database Id = 11 Object Id = 728389664]
spid17s Proc [Database Id = 11 Object Id = 728389664]
spid17s process id=process3c26da8 taskpriority=0 logused=0 waitresource=OBJECT: 11:1365579903:0 waittime=17500 ownerId=2710785336 transactionname=WstrObjnameI4I4 lasttranstarted=2011-08-06T00:31:50.557 XDES=0xb935ca38 lockMode=Sch-S schedulerid=4 kpid=6724 status=suspended spid=62 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-08-06T00:31:50.313 lastbatchcompleted=2011-08-06T00:31:50.307 lastattention=2011-08-05T03:08:40.700 clientapp=Quest Diagnostic Server (Monitoring) hostname=SQL2 hostpid=2464 loginname=abc\mssqladmin isolationlevel=read committed (2) xactid=2710785335 currentdb=11 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056
spid15s process id=process3c1cda8 taskpriority=0 logused=0 waitresource=OBJECT: 11:1365579903:0 waittime=31250 ownerId=2710782968 transactionname=SELECT lasttranstarted=2011-08-06T00:31:36.793 XDES=0x1f73321c8 lockMode=IS schedulerid=3 kpid=2360 status=suspended spid=181 sbid=0 ecid=0 priority=0 transcount=0 lastbatchstarted=2011-08-06T00:31:36.793 lastbatchcompleted=2011-08-06T00:31:36.793 clientapp=.Net SqlClient Data Provider hostname=SP1 hostpid=2408 loginname=abc\mossadmin isolationlevel=read committed (2) xactid=2710782968 currentdb=11 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
spid17s executionStack
spid15s executionStack
spid17s frame procname=adhoc line=1 stmtstart=60 sqlhandle=0x0200000041717535bbafe2be77751b6c893515d633dc4a24
spid15s frame procname=adhoc line=1 stmtstart=252 sqlhandle=0x020000009c16f000e4257c2e71f62dbf36068d742444eb81
spid17s insert into #qs_indexopstats2 select
spid17s db_name([dbid]) + '|' + isnull(object_name(tableid),'')+ '|' + isnull(si.name,'<Heap>') + '|' + convert(NVARCHAR,ios.partition_number),
spid17s [dbid],
spid17s db_name([dbid]) as dbname,
spid17s tableid,
spid17s object_name(tableid) as tablename,
spid17s indexid,
spid17s si.name as indexname,
spid17s waitcount,
spid17s waitms
spid17s from
spid17s #qs_indexopstats ios
spid17s inner join sys.objects so with (readpast) on ios.tableid = so.object_id
spid17s inner join dbo.sysindexes si with (readpast) on ios.indexid = si.indid and ios.tableid = si.id and ios.[dbid] = 11 where
spid17s (so.type = 'U')
spid17s frame procname=adhoc line=91 stmtstart=5630 stmtend=5652 sqlhandle=0x02000000888490374247611c08d5a19a81372d0fe8db7720
spid17s exec(@sql)
spid15s SELECT TOP 2147483648 t1.[Type] AS c0,t3.[tp_Created] AS c27c8,t1.[Id] AS c15,UserData.[tp_ItemOrder],UserData.[tp_Created],UserData.[tp_HasCopyDestinations],UserData.[tp_ModerationStatus],UserData.[tp_Level],UserData.[nvarchar1],t2.[tp_ID] AS c26c5,t3.[nvarchar5] AS c27c7,UserData.[tp_WorkflowInstanceID],t1.[MetaInfo] AS c14,UserData.[ntext1],UserData.[tp_HasAttachment],UserData.[nvarchar3],UserData.[tp_Modified],t2.[nvarchar1] AS c26c4,t3.[nvarchar4] AS c27c6,UserData.[tp_UIVersion],t1.[LeafName] AS c2,t1.[DirName] AS c18,UserData.[tp_ID],UserData.[tp_CopySource],UserData.[nvarchar5],t2.[tp_Created] AS c26c8,t3.[tp_ID] AS c27c5,UserData.[tp_InstanceID],UserData.[tp_GUID],t1.[ProgId] AS c13,UserData.[tp_Author],UserData.[tp_Editor],UserData.[tp_UIVersionString],t1.[TimeCreated] AS c1,UserData.[nvarchar2],UserData.[tp_ContentType],t2.[nvarchar5] AS c26c7,t3.[nvarchar1] AS c27c4,CASE WHEN DATALENGTH(t1.DirName) = 0 THEN t1.LeafName WHEN DATALENGTH(t1.LeafName) = 0 THEN t1.DirName ELSE t1.DirName + N'/' + t1.Le
spid17s inputbuf
spid17s -- SoSSE: 7.0.1.4515
spid17s -- File: QS_IndexLatchWaitTimes.sql
spid15s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
spid17s -- *
spid15s unknown
spid15s inputbuf
spid17s -- * Copyright 2010 Quest Software, Inc.
spid17s -- * ALL RIGHTS RESERVED.
spid17s -- *
spid17s -- * This software is the confidential and proprietary information of
spid17s -- * Quest Software Inc. ("Confidential Information"). You shall not
spid17s -- * disclose such Confidential Information and shall use it only in
spid17s -- * accordance with the terms of the license agreement you entered
spid17s -- * into with Quest Software Inc.
spid17s -- *
spid17s -- * QUEST SOFTWARE INC. MAKES NO REPRESENTATIONS OR
spid17s -- * WARRANTIES ABOUT THE SUITABILITY OF THE SOFTWARE,
spid17s -- * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
spid17s -- * TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS
spid17s -- * FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. QUEST
spid17s -- * SOFTWARE SHALL NOT BE LIABLE FOR ANY DAMAGES
spid17s -- * SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING
spid15s (@L0 uniqueidentifier,@L2 uniqueidentifier,@L3 uniqueidentifier,@L4 uniqueidentifier,@L5TXP nvarchar(255),@DN nvarchar(260)) SELECT TOP 2147483648 t1.[Type] AS c0,t3.[tp_Created] AS c27c8,t1.[Id] AS c15,UserData.[tp_ItemOrder],UserData.[tp_Created],UserData.[tp_HasCopyDestinations],UserData.[tp_ModerationStatus],UserData.[tp_Level],UserData.[nvarchar1],t2.[tp_ID] AS c26c5,t3.[nvarchar5] AS c27c7,UserData.[tp_WorkflowInstanceID],t1.[MetaInfo] AS c14,UserData.[ntext1],UserData.[tp_HasAttachment],UserData.[nvarchar3],UserData.[tp_Modified],t2.[nvarchar1] AS c26c4,t3.[nvarchar4] AS c27c6,UserData.[tp_UIVersion],t1.[LeafName] AS c2,t1.[DirName] AS c18,UserData.[tp_ID],UserData.[tp_CopySource],UserData.[nvarchar5],t2.[tp_Created] AS c26c8,t3.[tp_ID] AS c27c5,UserData.[tp_InstanceID],UserData.[tp_GUID],t1.[ProgId] AS c13,UserData.[tp_Author],UserData.[tp_Editor],UserData.[tp_UIVersionString],t1.[TimeCreated] AS c1,UserData.[nvarchar2],UserData.[tp_ContentType],t2.[nvarchar5] AS c26c7,t3.[nvarchar1] AS c27c4,CASE W
spid17s -- * OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES.
spid17s -- *
spid17s if isnull(object_id('tempdb..#qs_indexopstats'),0)<> 0
spid17s begin
spid17s drop table #qs_indexopstats
spid17s e
spid17s resource-list
spid17s keylock hobtid=281474978938880 dbid=11 objectname=WSS_Content_DB.sys.sysschobjs indexname=clst id=lock51d5500 mode=U associatedObjectId=281474978938880
spid17s owner-list
spid17s owner id=process3c26da8 mode=S
spid17s waiter-list
spid17s waiter id=process3c09978 mode=X requestType=convert
spid15s process id=process3c26da8 taskpriority=0 logused=0 waitresource=OBJECT: 11:1365579903:0 waittime=17500 ownerId=2710785336 transactionname=WstrObjnameI4I4 lasttranstarted=2011-08-06T00:31:50.557 XDES=0xb935ca38 lockMode=Sch-S schedulerid=4 kpid=6724 status=suspended spid=62 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-08-06T00:31:50.313 lastbatchcompleted=2011-08-06T00:31:50.307 lastattention=2011-08-05T03:08:40.700 clientapp=Quest Diagnostic Server (Monitoring) hostname=SQL2 hostpid=2464 loginname=abc\mssqladmin isolationlevel=read committed (2) xactid=2710785335 currentdb=11 lockTimeout=4294967295 clientoption1=671090784 clientoption2=128056
spid15s executionStack
spid17s objectlock lockPartition=0 objid=1365579903 subresource=FULL dbid=11 objectname=WSS_Content_DB.dbo.AllUserData id=lock5331900 mode=Sch-M associatedObjectId=1365579903
spid17s owner-list
spid15s frame procname=adhoc line=1 stmtstart=60 sqlhandle=0x0200000041717535bbafe2be77751b6c893515d633dc4a24
spid17s owner id=process3c09978 mode=Sch-M
spid15s insert into #qs_indexopstats2 select
spid17s waiter-list
spid17s waiter id=process3c26da8 mode=Sch-S requestType=wait
spid15s db_name([dbid]) + '|' + isnull(object_name(tableid),'')+ '|' + isnull(si.name,'<Heap>') + '|' + convert(NVARCHAR,ios.partition_number),
spid15s [dbid],
spid15s db_name([dbid]) as dbname,
spid15s tableid,
spid15s object_name(tableid) as tablename,
spid15s indexid,
spid15s si.name as indexname,
spid15s waitcount,
spid15s waitms
spid15s from
spid15s #qs_indexopstats ios
spid15s inner join sys.objects so with (readpast) on ios.tableid = so.object_id
spid15s inner join dbo.sysindexes si with (readpast) on ios.indexid = si.indid and ios.tableid = si.id and ios.[dbid] = 11 where
spid15s (so.type = 'U')
spid15s frame procname=adhoc line=91 stmtstart=5630 stmtend=5652 sqlhandle=0x02000000888490374247611c08d5a19a81372d0fe8db7720
spid15s exec(@sql)
spid15s inputbuf
spid15s -- SoSSE: 7.0.1.4515
spid15s -- File: QS_IndexLatchWaitTimes.sql
spid15s -- *
spid15s -- * Copyright 2010 Quest Software, Inc.
spid15s -- * ALL RIGHTS RESERVED.
spid15s -- *
spid15s -- * This software is the confidential and proprietary information of
spid15s -- * Quest Software Inc. ("Confidential Information"). You shall not
spid15s -- * disclose such Confidential Information and shall use it only in
spid15s -- * accordance with the terms of the license agreement you entered
spid15s -- * into with Quest Software Inc.
spid15s -- *
spid15s -- * QUEST SOFTWARE INC. MAKES NO REPRESENTATIONS OR
spid15s -- * WARRANTIES ABOUT THE SUITABILITY OF THE SOFTWARE,
spid15s -- * EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
spid15s -- * TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS
spid15s -- * FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. QUEST
spid15s -- * SOFTWARE SHALL NOT BE LIABLE FOR ANY DAMAGES
spid15s -- * SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING
spid15s -- * OR DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES.
spid15s -- *
spid15s if isnull(object_id('tempdb..#qs_indexopstats'),0)<> 0
spid15s begin
spid15s drop table #qs_indexopstats
spid15s e
spid15s resource-list
spid15s keylock hobtid=281474978938880 dbid=11 objectname=WSS_Content_DB.sys.sysschobjs indexname=clst id=lock51d5500 mode=U associatedObjectId=281474978938880
spid15s owner-list
spid15s owner id=process3c26da8 mode=S
spid15s waiter-list
spid15s waiter id=process3c09978 mode=X requestType=convert
spid15s objectlock lockPartition=0 objid=1365579903 subresource=FULL dbid=11 objectname=WSS_Content_DB.dbo.AllUserData id=lock5331900 mode=Sch-M associatedObjectId=1365579903
spid15s owner-list
spid15s owner id=process3c09978 mode=Sch-M
spid15s waiter-list
spid15s waiter id=process3c1cda8 mode=IS requestType=wait
spid15s waiter id=process3c26da8 mode=Sch-S requestType=wait
August 6, 2011 at 3:26 pm
Multiple processes involved here
The first one is doing an index rebuild. No idea why an index rebuild would be in the middle of an important process, but it's running. It probably shouldn't be running during critical times.
The other processes are from some Quest product, and they appear to be checking index stats of some form.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2011 at 3:54 pm
Multiple processes involved here
The first one is doing an index rebuild. No idea why an index rebuild would be in the middle of an important process, but it's running. It probably shouldn't be running during critical times.
The other processes are from some Quest product, and they appear to be checking index stats of some form
Thanks Gail,
Here, we got 2 deadlocks.
From SQL Server side, I'm not performing any index rebuilds and it looks like some process in SharePoint is doing the index rebuild.
I want to know that Quest spot light is the culprit for the dead lock chain to start? or the index rebuild?
How to determine what caused the 1st dead lock to occur? (spotlight or index rebuild)
please advice?
August 6, 2011 at 4:28 pm
You can't say that one process or the other is at fault. It takes at least 2 processes to cause the deadlock and they are both equally responsible.
Sharepoint does not automatically rebuild indexes, look at your jobs, look at automated processes, look at the scheduled sharepoint processes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 6, 2011 at 4:39 pm
Thanks for your immediate response
In SQLAgent, all jobs were disabled
Could you please tell me what is the exact sql statements that are involved in dead lock (in both dead locks).
August 6, 2011 at 5:09 pm
Something is rebuilding indexes. You need to find what is running that and decide whether it should be stopped or not.
As for the statements, they're in the deadlock graph you posted, right after the headings 'execution stack'. Just match the SPIDs as there are two deadlock graphs mixed up together.
Oh, there's one process I didn't notice earlier, a sharepoint workflow (at least that's what it looks like)
So the 3 processes involved are
1) an index rebuild (which is being run from a server SP2)
2) Some index-monitoring from a Quest product
3) a sharepoint workflow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 7, 2011 at 1:16 am
I may be wrong but it looks to me like to processes are attempting to rebuild the same index:
spid15s ALTER INDEX AllUserData_Url ON dbo.AllUserData REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF)
spid17s ALTER INDEX AllUserData_Url ON dbo.AllUserData REBUILD WITH (FILLFACTOR = 80, ONLINE = OFF)
December 9, 2011 at 2:37 am
Hi!
There is actually a built in SharePoint timer job that rebuilds indexes...if SP2 it rebuilds all indexes once every day.
Take a look at:
http://mysharepointofview.com/2010/02/database-statistics-timer-job-for-good-and-for-bad/
Regards,
Andreas
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply