Index defrag script causes blocking...

  • I wrote a script that will go through each index on a db and determine if it should be rebuilt, reorg, or skipped based on certain criteria. I believe I am running into issues where a clustered index and non-clustered index are trying to be defragmented at the same time ... causing blocking (according to blocked process report). Does anyone have suggestions on how to get around this?

    Should I break the step into two steps, the first one deals with only clustered indexes, and the second deals only with non-clustered?

  • So your script is not waiting for the previous process to complete?

    If you do go with 2 steps make sure you do the clustered index first as that key is included in the non-clustered indexes.

  • I believe the indexes are be defragmented in parallel.

    Has anyone else encountered this?

    Thanks for the tip, I will be sure to defrag the clustered indexes first if I do take this route.

  • schep021 (8/29/2008)


    I wrote a script that will go through each index on a db and determine if it should be rebuilt, reorg, or skipped based on certain criteria. I believe I am running into issues where a clustered index and non-clustered index are trying to be defragmented at the same time.

    I haven't seen this behaviour. I think that if you're running it in one script then it will be run one index at a time (unless your using the ALL option).

    I have a stored procedure that you're welcome to use.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Ola Hallengren

    http://ola.hallengren.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply