SQL Server – Help Reading Deadlock XML

deadlocksql serversql-server-2012

Even with reading the Bart Duncan blog about reading deadlocks I still struggle with this. I have included the XML from a particular deadlock.

  <?xml version="1.0" encoding="utf-8" ?> 
- <deadlock>
- <victim-list>
  <victimProcess id="process2fea928" /> 
  </victim-list>
- <process-list>
- <process XDES="0x64e18e3a8" clientapp=".Net SqlClient Data Provider+TransactionalSqlSession" clientoption1="673316896" clientoption2="128056" currentdb="12" ecid="0" hostname="APPSVM7" hostpid="3208" id="process2fea928" isolationlevel="read uncommitted (1)" kpid="11776" lastattention="1900-01-01T00:00:00.240" lastbatchcompleted="2014-07-25T00:00:41.240" lastbatchstarted="2014-07-25T00:00:41.240" lasttranstarted="2014-07-25T00:00:41.230" lockMode="X" lockTimeout="4294967295" loginname="NTSERVERS\AX2012WEBPROD" logused="0" ownerId="9934810052" priority="0" sbid="0" schedulerid="16" spid="148" status="suspended" taskpriority="0" trancount="2" transactionname="MgdSqlSessionTran" waitresource="OBJECT: 12:277576027:10" waittime="4440" xactid="9934810052">
- <executionStack>
  <frame line="25" procname="Search_Service_Application_CrawlStoreDB_3a7fefdbc44a48ef902a65ffb71c7d3a.dbo.proc_MSS_GetNextCrawlBatch" sqlhandle="0x03000c004f6ab01d5de1f8008ca2000001000000000000000000000000000000000000000000000000000000" stmtend="3504" stmtstart="3362">INSERT INTO MSSBatchHistory WITH(TABLOCKX)(CrawlID) VALUES (@CrawlID)</frame> 
  </executionStack>
  <inputbuf>Proc [Database Id = 12 Object Id = 498100815]</inputbuf> 
  </process>
- <process XDES="0x4212ea3a8" clientapp="SharePoint[OWSTIMER][1][Search_Service_Application_CrawlStoreDB_3a7fefdbc44a48ef902a65ffb71c7d3a]" clientoption1="673185824" clientoption2="128056" currentdb="12" ecid="0" hostname="APPSVM7" hostpid="9480" id="process403a188" isolationlevel="read committed (2)" kpid="14060" lastattention="1900-01-01T00:00:00.360" lastbatchcompleted="2014-07-25T00:00:40.360" lastbatchstarted="2014-07-25T00:00:40.360" lasttranstarted="2014-07-25T00:00:40.387" lockMode="Sch-M" lockTimeout="4294967295" loginname="NTSERVERS\AX2012WEBPROD" logused="168" ownerId="9934809243" priority="0" sbid="0" schedulerid="11" spid="170" status="suspended" taskpriority="-10" trancount="1" transactionname="ALTER INDEX" waitresource="OBJECT: 12:277576027:0" waittime="584" xactid="9934809243">
- <executionStack>
  <frame line="1" procname="adhoc" sqlhandle="0x01000c00d14d6c19c006e5650400000000000000000000000000000000000000000000000000000000000000" stmtend="294" stmtstart="24">ALTER INDEX IX_MSSBatchHistory ON dbo.MSSBatchHistory REBUILD WITH (MAXDOP = 0, FILLFACTOR = 95, DATA_COMPRESSION = PAGE, ONLINE = ON)</frame> 
  <frame line="133" procname="Search_Service_Application_CrawlStoreDB_3a7fefdbc44a48ef902a65ffb71c7d3a.dbo.proc_MSS_DefragGathererIndexes" sqlhandle="0x03000c0018d2e62d8e07d6008ca2000001000000000000000000000000000000000000000000000000000000" stmtend="8816" stmtstart="8768">EXEC (@command)</frame> 
  </executionStack>
  <inputbuf>Proc [Database Id = 12 Object Id = 770101784]</inputbuf> 
  </process>
  </process-list>
- <resource-list>
- <objectlock associatedObjectId="277576027" dbid="12" id="lock4f20fdc00" lockPartition="10" mode="IS" objectname="Search_Service_Application_CrawlStoreDB_3a7fefdbc44a48ef902a65ffb71c7d3a.dbo.MSSBatchHistory" objid="277576027" subresource="FULL">
- <owner-list>
  <owner id="process403a188" mode="IS" /> 
  </owner-list>
- <waiter-list>
  <waiter id="process2fea928" mode="X" requestType="wait" /> 
  </waiter-list>
  </objectlock>
- <objectlock associatedObjectId="277576027" dbid="12" id="lock4d7444c00" lockPartition="0" mode="X" objectname="Search_Service_Application_CrawlStoreDB_3a7fefdbc44a48ef902a65ffb71c7d3a.dbo.MSSBatchHistory" objid="277576027" subresource="FULL">
- <owner-list>
  <owner id="process2fea928" mode="X" /> 
  </owner-list>
- <waiter-list>
  <waiter id="process403a188" mode="Sch-M" requestType="wait" /> 
  </waiter-list>
  </objectlock>
  </resource-list>
  </deadlock>

It seems like the following is occurring:

SPID 148 running the query (line 25 of proc Search_Service_Application_CrawlStoreDB_3a7fefdbc44a48ef902a65ffb71c7d3a.dbo.proc_MSS_GetNextCrawlBatch)
INSERT INTO MSSBatchHistory WITH(TABLOCKX)(CrawlID) VALUES (@CrawlID)

SPID170 running the query (line 1 of adhoc query)
INDEX IX_MSSBatchHistory ON dbo.MSSBatchHistory REBUILD WITH (MAXDOP = 0, FILLFACTOR = 95, DATA_COMPRESSION = PAGE, ONLINE = ON)

However on the second executionStack there is a second frame, namely this:

<frame line="133" procname="Search_Service_Application_CrawlStoreDB_3a7fefdbc44a48ef902a65ffb71c7d3a.dbo.proc_MSS_DefragGathererIndexes" sqlhandle="0x03000c0018d2e62d8e07d6008ca2000001000000000000000000000000000000000000000000000000000000" stmtend="8816" stmtstart="8768">EXEC (@command)</frame>

How does this affect the deadlocking? Is this another process that was running at the time?

Thanks

Best Answer

as this is standard SharePoint maintenance the OP is unlikely to be responsible for the code or be able to interfere in it. What they could influence, is the time it is run at.

dbo.proc_MSS_DefragGathererIndexes should only really be run manually and after your first crawl but not generally otherwise. Can you confirm this proc has not been automated as part of a maintenance task?

Recommendations:

  • double-check with your DBA dbo.proc_MSS_DefragGathererIndexes has not been scheduled as part of a regular maintenance job in your Crawl database

  • if you can influence the SQL being run, consider lowering the deadlock priority on the index rebuild thread if possible, eg

    SET DEADLOCK_PRIORITY LOW

  • if this proc does need to be run, consider disabling the crawl first as part of a small maintenance window.

SQL 2014 has a really nice feature called Managed Lock Priority (MLP), which sadly won't be of any use to you, but may be to future users.

Related Question