January 12, 2011 at 5:20 am
There are frequent deadlocks occurring on the server mainly in relation to SharePoint databases.
Stored procedures mainly involved in deadlocking :
· proc_MSS_ProcessCommitted
· proc_MSS_UpdateHostStats
· proc_MSS_ProcessDelete
· proc_MSS_GetNextCrawlBatch
any one can resolve this issue?
January 12, 2011 at 7:00 am
We need a lot more information to help you out here. Do you have traceflags enabled? Traceflag 1222 will output deadlock information into the error log. With that you can begin to understand the causes of the deadlock. You can also gather deadlock information using trace events, but I prefer the detailed data you get from the traceflags.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2011 at 8:00 am
This below is the Error log report
2010-12-06 16:44:12.18 spid15s deadlock-list
2010-12-06 16:44:12.18 spid15s deadlock victim=process32ab5b8
2010-12-06 16:44:12.18 spid15s process-list
2010-12-06 16:44:12.18 spid15s process id=process2f69d38 taskpriority=0 logused=4500 waitresource=KEY : 94:72057594042777600 (52004123c266) waittime=734 ownerId=2117346 transactionname=user_transaction lasttranstarted=2010-12-06T16:44:11.370 XDES=0x3de6a250 lockMode=U schedulerid=10 kpid=7124 status=suspended spid=500 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-12-06T16:44:11.370 lastbatchcompleted=2010-12-06T16:44:11.370 clientapp=Windows SharePoint Services
hostname=servername hostpid=3300 loginname=name isolationlevel=read committed (2) xactid=2117346 currentdb=WSS_Search_DB_21052010 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
2010-12-06 16:44:12.18 spid15s executionStack
2010-12-06 16:44:12.18 spid15s frame procname=WSS_Search_DB_21052010.dbo.proc_MSS_ProcessDelete line=162 stmtstart=13936 stmtend=14066 sqlhandle=0x03005e0084764f7ce7d43c017d9d00000100000000000000
2010-12-06 16:44:12.18 spid15s DELETE FROM MSSCrawlQueue WHERE DocID=@DocID AND BatchID = 0
2010-12-06 16:44:12.18 spid15s frame procname=WSS_Search_DB_21052010.dbo.proc_MSS_ProcessCommitted line=249 stmtstart=20918 stmtend=22572 sqlhandle=0x03005e00bd9a437d4fd53c017d9d00000100000000000000
2010-12-06 16:44:12.18 spid15s EXEC dbo.proc_MSS_ProcessDelete
2010-12-06 16:44:12.18 spid15s @ProjectID,
2010-12-06 16:44:12.18 spid15s @HisContentSourceID,
2010-12-06 16:44:12.18 spid15s @StartAddressID,
2010-12-06 16:44:12.18 spid15s @DocID,
2010-12-06 16:44:12.18 spid15s @CrawlID,
2010-12-06 16:44:12.18 spid15s @HisCrawlID,
2010-12-06 16:44:12.18 spid15s @HisCommitCrawlID,
2010-12-06 16:44:12.18 spid15s @Scope,
2010-12-06 16:44:12.18 spid15s @HisTransactionFlags,
2010-12-06 16:44:12.18 spid15s @UseChangeLog,
2010-12-06 16:44:12.18 spid15s @ChangeLogCookie,
2010-12-06 16:44:12.18 spid15s @ChangeLogBatchID,
2010-12-06 16:44:12.18 spid15s @DeleteReason,
2010-12-06 16:44:12.18 spid15s @TransactionType,
2010-12-06 16:44:12.18 spid15s @HostDepth,
2010-12-06 16:44:12.18 spid15s @EnumerationDepth,
2010-12-06 16:44:12.18 spid15s @HisParentDocID,
2010-12-06 16:44:12.18 spid15s @EndPathFlag,
2010-12-06 16:44:12.18 spid15s @HostID,
2010-12-06 16:44:12.18 spid15s @LCID,
2010-12-06 16:44:12.18 spid15s @ErrorID,
2010-12-06 16:44:12.18 spid15s @ErrorLevel,
2010-12-06 16:44:12.18 spid15s @AccessURL,
2010-12-06 16:44:12.18 spid15s @AccessHash,
2010-12-06 16:44:12.18 spid15s @CompactURL,
2010-12-06 16:44:12.18 spid15s @CompactHash,
2010-12-06 16:44:12.18 spid15s @DisplayURL,
2010-12-06 16:44:12.18 spid15s @DisplayHash,
2010-12-06 16:44:12.18 spid15s @MaxDocId
2010-12-06 16:44:12.18 spid15s inputbuf
2010-12-06 16:44:12.18 spid15s Proc [Database Id = 94 Object Id = 2101582525]
2010-12-06 16:44:12.18 spid15s process id=process32ab5b8 taskpriority=0 logused=820 waitresource=KEY : 94:72057594042580992 (020068e8b274) waittime=781 ownerId=2117356 transactionname=user_transaction lasttranstarted=2010-12-06T16:44:11.377 XDES=0x3d11c250 lockMode=X schedulerid=13 kpid=4852 status=suspended spid=498 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-12-06T16:44:11.377 lastbatchcompleted=2010-12-06T16:44:11.377 clientapp=Windows SharePoint Services
hostname=ServerNamehostpid=3300 loginname=name isolationlevel=read committed (2) xactid=2117356 currentdb=WSS_Search_DB_21052010 lockTimeout=4294967295 clientoption1=673316896 clientoption2=128056
2010-12-06 16:44:12.18 spid15s executionStack
2010-12-06 16:44:12.18 spid15s frame procname=WSS_Search_DB_21052010.dbo.proc_MSS_UpdateHostStats line=35 stmtstart=3098 sqlhandle=0x03005e004b525b7be1d43c017d9d00000100000000000000
2010-12-06 16:44:12.18 spid15s UPDATE MSSCrawlHostList SET
2010-12-06 16:44:12.18 spid15s SuccessCount = CASE WHEN SuccessCount + @SuccessCount > 0 THEN SuccessCount + @SuccessCount ELSE 0 END,
2010-12-06 16:44:12.18 spid15s ErrorCount = CASE WHEN ErrorCount + @ErrorCount > 0 THEN ErrorCount + @ErrorCount ELSE 0 END,
2010-12-06 16:44:12.18 spid15s WarningCount = CASE WHEN WarningCount + @WarningCount > 0 THEN WarningCount + @WarningCount ELSE 0 END
2010-12-06 16:44:12.18 spid15s WHERE HostID = @HostID
2010-12-06 16:44:12.18 spid15s frame procname=WSS_Search_DB_21052010.dbo.proc_MSS_ProcessCommitted line=132 stmtstart=12972 stmtend=13300 sqlhandle=0x03005e00bd9a437d4fd53c017d9d00000100000000000000
2010-12-06 16:44:12.18 spid15s EXEC dbo.proc_MSS_UpdateHostStats @HostID, @TransactionType, @MarkDelete, @ErrorID, @ErrorLevel, @HisCommitCrawlID, @HisErrorLevel, @TrackIDDel, @DelErrorLevel
2010-12-06 16:44:12.18 spid15s inputbuf
2010-12-06 16:44:12.18 spid15s Proc [Database Id = 94 Object Id = 2101582525]
2010-12-06 16:44:12.18 spid15s resource-list
2010-12-06 16:44:12.18 spid15s keylock hobtid=72057594042777600 dbid=94 objectname=WSS_Search_DB_21052010.dbo.MSSCrawlQueue indexname=IX_MSSCrawlQueue_Cluster id=lock1edb5640 mode=X associatedObjectId=72057594042777600
2010-12-06 16:44:12.18 spid15s owner-list
2010-12-06 16:44:12.18 spid15s owner id=process32ab5b8 mode=X
2010-12-06 16:44:12.18 spid15s waiter-list
2010-12-06 16:44:12.18 spid15s waiter id=process2f69d38 mode=U requestType=wait
2010-12-06 16:44:12.18 spid15s keylock hobtid=72057594042580992 dbid=94 objectname=WSS_Search_DB_21052010.dbo.MSSCrawlHostList indexname=PK_MSSCrawlHostList id=lock31b6f180 mode=X associatedObjectId=72057594042580992
2010-12-06 16:44:12.18 spid15s owner-list
2010-12-06 16:44:12.18 spid15s owner id=process2f69d38 mode=X
2010-12-06 16:44:12.18 spid15s waiter-list
2010-12-06 16:44:12.18 spid15s waiter id=process32ab5b8 mode=X requestType=wait
2010-12-06 16:44:17.23 spid14s deadlock-list
January 12, 2011 at 8:05 am
From what I've heard (from CSS), deadlocks on Sharepoint search crawl are expected and are not a problem, the crawl retries.
You cannot make any code, structure or index changes to the sharepoint databases without completely losing all support from MS.
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply