question

sqlqa avatar image
sqlqa 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?
indexrebuild
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Are you setting any criterion on which indexes need rebuilding?
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

goeddieuk avatar image
goeddieuk answered
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/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.