Why defragment tables & indexes at the same time?
Virtually every DBA I talk to about index maintenance seems to defrag tables & indexes together. Here are a few thoughts about why this isn't a good idea..
(a) If overall database performance improves radically after rebuilding your tables, you've probably got queries that are scanning those tables - which is usually not a good thing. Rather than simply rebuilding tables all the time, you'll usually get far better efficiency from designing indexes which service those range scans better than simply allowing table scans
(b) Index scan efficiency is (or should be) far more important to overall database performance than table scan efficiency, yet indexes always fragment much faster than tables (as you have no control over insertion order). Therefore, they really should be rebuild far more regularly than tables..
(c) Rebuilding indexs (non-clustered) is molto faster than rebuilding table structures. Obviously, there's a lot less data to scan, sort & write during NCIX rebuilds, so they tend to take seconds where table structures often take minutes or even hours to rebuild.
Given that it's more important to rebuild NCIXs regularly, they fragment faster, yet are far les of a burden to rebuild, why schedule their maintenance on the same time-scale as table rebuilds?
If you have a substantially large database (relative to your environment) and it takes too long to rebuild tables regularly, why let this hold back your more important NCIX maintenance?
It makes perfect sense to rebuild your NCIXs on a more regular schedule than your tables. Only problem is that there's no *easy* way to seperate these tasks - maintenance plan wizards don't have options to seperate the rebuild of NCIXs from table structures (CIXs & heaps). I haven't seen any articles demonstrating how to do this either (although they might be out there). Most articles I've seen tend to encourage rebuilding ALL indexes (CIXs & NCIXs) based on either global or index specific fragmentation thresholds (eg, rebuild them if they're > 15% fragmented).
I'll try to get such a script together for a future post. We do have such beasts at work but they'll need to be simplified for a blog entry - hopefully I'll get the time for this soon.