August 29, 2008 at 3:17 pm
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?
August 29, 2008 at 3:21 pm
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 29, 2008 at 3:26 pm
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.
August 29, 2008 at 6:20 pm
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply