Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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:

We also have a tool (not free) called SQL Sentry Fragmentation Manager 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 - since it's part of Performance Advisor - the ability to assess exactly how the work you're doing affects performance.

Disclaimer (of course): I work for SQL Sentry.

share|improve this answer
    
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 '13 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? –  Aaron Bertrand Jan 29 '13 at 15:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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