asked
How to make a weekly index rebuild quicker?
We are using SQL 2005, Windows server 2003 Server We have 24 DB in our Server. Weekly rebuild index job is running Every Sunday Night. My Question is Weekly rebuild index job normally consuming time 45 to 60 mints But some times Regularly per month it tooks more than 5 hours.Why? How to solve this issues? Which is causes this?
Without seeing the scripts and your processing I can't tell you specifically, but the most likely issue is resource contention. You're rebuilding indexes, but there may be other processes, data loads, normal user access, pretty much anything, running at the same time. This will cause blocking, which extends the amount of time the index rebuild runs, which causes other blocking, etc. The key would be to identify the sources of the blocking and see if anything can be done to move the processing to another time. Or, spread the times you do your rebuilds across a wider time in order to reduce the resource contention.
I would also use the ola hallengren scripts instead of the standard maintenance tasks as they will reorganise when the indexes don't actually need rebuilding.
http://ola.hallengren.com/
Grant's point about resource contention is great. I'm also wondering what else is done job-wise on the server at other times than this index rebuild job? There are more or less two possible reasons for an index rebuild job to take significantly longer time to execute: - There are other jobs/workloads running at the same time, causing locking, blocking etc. - The indexes are more fragmented than usual. The later could be caused by a number of things. You could have performed massive loads or updates, causing possible page splits to your indexes. In that case, you could eg reconsider your index design. Another reason for the indexes to be more fragmented than usual is datafile shrinks. There are in general no good reasons to schedule shrink-file-jobs. Yet there are thousands and thousands of instances running shrink-file-jobs nightly/weekly/monthly. I have no idea if that's the case in your server. Please provide some more information about what's going on in your server, more than your index rebuild jobs, both at the same time and at other times.
18 People are following this question.
