locked
index rebuild in maintenance plan causes deadlock RRS feed

  • Question

  • Hello all,

    We got a sql server 2008 Enterprise. The index rebuild process I set up worked fine for 1 year until I enabled the parallelism last month. I configured as follow:

    MAXDoP : 6  (well, we got an old machine with only 12 cores)
    cost threshold for parallelism: 5

    the traffic during the index rebuilding is minimum, because I scheduled it at midnight whereas the index rebuild process seems deadlocked itself. strange! Please see the deadlock log as follow:

    =====================================

    - <EVENT_INSTANCE>
      <EventType>DEADLOCK_GRAPH</EventType>
      <PostTime>2012-05-12T23:01:17.987</PostTime>
      <SPID>24</SPID>
    - <TextData>
    - <deadlock-list>
    - <deadlock victim="process564ebc8">
    - <process-list>
    - <process id="process564ebc8" taskpriority="10" logused="140" waitresource="PAGE: 10:1:13391" waittime="127" ownerId="1220972478" transactionname="OnlineIndexInsertTxn" lasttranstarted="2012-05-12T23:01:17.803" XDES="0x8ca03970" lockMode="IX" schedulerid="8" kpid="10420" status="suspended" spid="156" sbid="0" ecid="3" priority="0" trancount="0" lastbatchstarted="2012-05-12T23:01:17.793" lastbatchcompleted="2012-05-12T23:01:17.793" clientapp="Microsoft SQL Server Management Studio" hostname="SQLLIVE" hostpid="11408" isolationlevel="serializable (4)" xactid="1220972472" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    - <executionStack>
      <frame procname="adhoc" line="1" sqlhandle="0x0200000027834303cb5b4e575068fcee6716b7438e383f84">insert [dbo].[tblPrice] select * from [dbo].[tblPrice]</frame>
      <frame procname="adhoc" line="1" sqlhandle="0x01000a00c925602d003b38d2020000000000000000000000">ALTER INDEX [PK_tblPrice] ON [dbo].[tblPrice] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = ON )</frame>
      </executionStack>
      <inputbuf />
      </process>
    - <process id="process5644e08" taskpriority="10" logused="94952" waitresource="PAGE: 2:1:125160" waittime="126" ownerId="1220972479" transactionname="OnlineIndexInsertTxn" lasttranstarted="2012-05-12T23:01:17.803" XDES="0x81758b60" lockMode="IX" schedulerid="7" kpid="9776" status="suspended" spid="156" sbid="0" ecid="1" priority="0" trancount="0" lastbatchstarted="2012-05-12T23:01:17.793" lastbatchcompleted="2012-05-12T23:01:17.793" clientapp="Microsoft SQL Server Management Studio" hostname="SQLLIVE" hostpid="11408" isolationlevel="serializable (4)" xactid="1220972472" currentdb="10" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">
    - <executionStack>
      <frame procname="adhoc" line="1" sqlhandle="0x0200000027834303cb5b4e575068fcee6716b7438e383f84">insert [dbo].[tblPrice] select * from [dbo].[tblPrice]</frame>
      <frame procname="adhoc" line="1" sqlhandle="0x01000a00c925602d003b38d2020000000000000000000000">ALTER INDEX [PK_tblPrice] ON [dbo].[tblPrice] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = ON )</frame>
      </executionStack>
      <inputbuf />
      </process>
      </process-list>
    - <resource-list>
    - <pagelock fileid="1" pageid="13391" dbid="10" objectname="DataSource.dbo.tblPrice" id="lock4ff7d6b80" mode="X" associatedObjectId="72057594416922624">
    - <owner-list>
      <owner id="process5644e08" mode="X" />
      </owner-list>
    - <waiter-list>
      <waiter id="process564ebc8" mode="IX" requestType="wait" />
      </waiter-list>
      </pagelock>
    - <pagelock fileid="1" pageid="125160" dbid="2" objectname="599673184" id="lock692908a80" mode="X" associatedObjectId="2738189759848513536">
    - <owner-list>
      <owner id="process564ebc8" mode="X" />
      </owner-list>
    - <waiter-list>
      <waiter id="process5644e08" mode="IX" requestType="wait" />
      </waiter-list>
      </pagelock>
      </resource-list>
      </deadlock>
      </deadlock-list>
      </TextData>
      <TransactionID />
      <LoginName>sa</LoginName>
      <StartTime>2012-05-12T23:01:17.983</StartTime>
      <ServerName>SQLLIVE</ServerName>
      <LoginSid>AQ==</LoginSid>
      <EventSequence>13745219</EventSequence>
      <IsSystem>1</IsSystem>
      <SessionLoginName />
      </EVENT_INSTANCE>

     ===========================================

    Another clue is that one of the processes which caused the deadlock was working on tempdb. I enabled 'ONLINE = ON, SORT_IN_TEMPDB = ON' in the script. I don't know why index rebuild can cause deadlock with parallelism. it never happened when I kept the MAXDOP as 1.

    Please share your thoughts. Thanks in advance for your assistance

    cn2500

    Monday, May 14, 2012 10:16 AM

Answers

  • Hello,
    I have seen this happening when rebuilding an index of a large table using “max degree of parallelism” greater than 1.

    I would say that probably MAXDOP=6 is not the recommended value for that instance, but if you want to continue using MAXDOP=6, change MAXDOP to 1 only during the maintenance plan job is running. You will see you won’t get that error anymore.

    There is a fix related to this issue, make sure you it has been applied to the instance.

    http://support.microsoft.com/kb/978250

    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Monday, May 14, 2012 11:14 AM

All replies

  • Hello,
    I have seen this happening when rebuilding an index of a large table using “max degree of parallelism” greater than 1.

    I would say that probably MAXDOP=6 is not the recommended value for that instance, but if you want to continue using MAXDOP=6, change MAXDOP to 1 only during the maintenance plan job is running. You will see you won’t get that error anymore.

    There is a fix related to this issue, make sure you it has been applied to the instance.

    http://support.microsoft.com/kb/978250

    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Monday, May 14, 2012 11:14 AM
  • Hi hope this link helps you for the better solution.

    http://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/

    Monday, May 14, 2012 1:01 PM
  • Thanks Alberto

    I have set the MAXDOP = 1 for a year. But recently our applications were developed with new features which cost a lot of CPU. I have to enable the Parallelism to minimize the execution time. And after 2 weeks observation it did its work well. The only side effect of parallism is this deadlock problem.

    Anyway, I adjusted the MP and disabled parallelism during the index rebuild process and resume after the MP is completed.

    Let's see what will happen this Sunday (next schedule of index rebuild)

    Much appreciated.

    cn2500

    Monday, May 14, 2012 1:01 PM
  • Hi,

    You don't have to change the whole servers parallelism setting for an index rebuild job.  You could put on a query hint, i.e.

    ALTER INDEX myindex ON mytable WITH(SORT_IN_TEMPDB=ON, MAXDOP = 1)



    Thanks,

    Andrew Bainbridge
    SQL Server DBA

    Please click "Propose As Answer" if a post solves your problem, or "Vote As Helpful" if a post has been useful to you

    Monday, May 14, 2012 1:15 PM
  • Hello,

    Let’s wait for the results on Sunday.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Monday, May 14, 2012 1:38 PM
  • check this link this may help you out

    http://www.sql-server-performance.com/forum/threads/deadlock-while-rebuilding-index-in-maintenance-plan.29186/

    Saturday, May 19, 2012 10:32 AM
  • It really worked fine on Sunday. Thanks a lot Alberto.

    Thanks all the guys who share your idea. Much appreciated.

    cn2500

    Monday, May 21, 2012 11:13 AM
  • Thanks Andrew,

    But we cannot modify the script generated by Maintenance Plan, can we?

    Monday, May 21, 2012 11:17 AM
  • Andrew,  I need to echo the question:  How do you modify a maintenance plan to set the maxdop parameter?
    Friday, June 15, 2012 4:42 PM
  • Hello David,

    Index Maintenance Task in maintainace plan cannot be modified to set the maxdop parameter. Could you please file a feature request at https://connect.microsoft.com/sqlServer/feedback ?

    As a workaround, you can modify your maintenance plan to use  "Execute T-SQL statement task " that can run any T-SQL statement that you would like to run as part of maintenance plan

    Thanks

    Sethu  Srinivasan [MSFT]

    SQL Server

    http://blogs.msdn.com/sqlagent

    Friday, June 15, 2012 6:34 PM
  • Would you think rebuilding indexes with ONLINE = ON could potentially help solving this deadlock without touching the MAXDOP setting? Since the online index building will not put the heavy locks on the underlying table, I thought it could be something to consider and give it a shot. BTW, I have enterprise edition of SQL Server. I think ONLINE REBUILDING of index is not available in other editions.


    • Edited by mbhandari Monday, June 18, 2012 5:51 PM
    Monday, June 18, 2012 4:04 PM
  • Hello,

    ONLINE = ON is indeed an Enterprise Edition feature. However, using ONLINE= ON may not prevent deadlocks from happen. In fact, the issue exposed by cn2500 on this thread occurred on a SQL Server 2008 Enterprise edition and was using ONLINE=ON to rebuild indexes.

    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Monday, June 18, 2012 5:51 PM
  • Thanks Alberto, that makes perfect sense. I saw that cn2500 had the issue even with online option set to ON. Well, looks like I have to get in path of setting MAXDOP=1. Any other options dealing with this?
    Monday, June 18, 2012 6:04 PM
  • Hello,

    Up to this moment, I don’t know of another workaround. MAXDOP=1 is the only option.

    Hope this helps.


    Regards,
    Alberto Morillo
    SQLCoffee.com

    Monday, June 18, 2012 6:10 PM
  • Hello Sethu,

    A Microsoft Connect item has been created as you suggested.

    https://connect.microsoft.com/SQLServer/feedback/details/755384/index-maintenance-task-in-maintainace-plan-cannot-be-modified-to-set-the-maxdop-parameter

    Thank you for your comments and suggestions.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, July 25, 2012 4:23 AM
  • Sadly, that link gets a page not found as of 2018-06-14.

    Pity, because the SPID handling this command DEADLOCKed itself!  Enterprise SQL 2016 SP2:

    ALTER INDEX [PK_Order_Line_Inventory] ON [OrderManager_C].[Order_Line_Inventory] REBUILD WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION= 5, ABORT_AFTER_WAIT=SELF) )

    Thursday, June 14, 2018 9:05 PM