Disadvantages of reorganizing all tables nightly 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?
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:


Ola Hallengren: http://ola.hallengren.com/
Michelle Ufford (@sqlfool): http://sqlfool.com/2011/06/index-defrag-script-v4-1/


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.