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