5

I consider scheduling a SQL Server job that reorganizes all tables nightly. We don't have enterprise, so we cannot rebuild online (this means we need to reorganize). We also don't want to invest too much time in fragmentation and space maintenance so we are seeking a solution that can be implemented quickly and does not require further time investments in the future.

Testing on a restored production backup showed that reorganize is almost as good as rebuild for reducing fragmentation and regaining space.

Is this a reasonable idea? Does this scheme create problems for availability or maintainability? Is this a reasonable long-term solution?

1 Answer 1

8

There are better ways - just reorganizing all indexes nightly can be quite wasteful. Why even bother reorganizing an index that is 12% fragmented? Why reorganize a 10GB index every night if it takes 30 minutes and you only reduce fragmentation by 2% or 3%? How much effort should you spend reorganizing an index that is largely or completely in memory anyway - sure you save some disk space, but you have to pull the index out of buffer to do so - is the space savings worth the performance hit that will cause?

There are free scripts out there that help make some of these decisions for you, and it's easy to override the defaults:

SentryOne also has a tool (not free) called SQL Sentry which goes quite a bit further than these by offering full historical reporting, broad to granular rules and schedules on which indexes to reorganize/rebuild and when (from server-wide to individual index and even partition), support for concurrent operations (should your maintenance window be tight), drag-and-drop calendar for scheduling, and the ability to assess exactly how the work you're doing affects performance.

2
  • I did consider using ready-made scripts. Thanks! The theory would be that reorganizing a low-fragmented index is very fast so it almost doesn't hurt. And we'd really like to defrag even unused or in-memory indexes to save on disk space (we are tight). What's your opinion on that?
    – usr
    Jan 29, 2013 at 15:51
  • 1
    Well, for your first question, it depends on how big the index is. If it's 10GB and you spend half an hour reorganizing it from 5% fragmented to 3% fragmented, was that an hour well spent? Just because some % is low does not mean it is cheap or fast. As for the in-memory index, sure you can save on disk space if you defrag, but you have to pull the index out of buffer to do that. Are the space savings worth the performance hit you're going to cause to the next round of queries that use that index and have to push it back into buffer? Jan 29, 2013 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.