Well, actually defrag, as in "should I defragment my indexes?" But, what I am really talking about is the method you use to defragment.
There are several schools of thought on this, and numerous scripts that you can snag from various places on the web that will help you do it, but ultimately it comes down to what works for you. I am talking about DBCC INDEXDEFRAG vs. DBCC DBREINDEX (SQL2005) or ALTER INDEX REBUILD vs. REORGANIZE (SQL2008+). Basically, a REORG vs. a REINDEX. Everyone will cite "Rules of Thumb", but a rule of thumb is what works for most cases. Most cases do not necessarily include my case, so I immediatly start to suspect these baseline rules - until proven. In my view, a "Rule of Thumb" is considered a Myth until proven true. Some of my fellow SQL bloggers have based entire blog series' on busting SQL Myths. For me, I am learning to consider "Rules of Thumb" as potential myths, begging to be debunked. And this topic is no different, so on to the task at hand.
Whether your version of SQL requires the DBCC or ALTER syntax, the decision is the same: Do I rip all my indexes off and rebuild them, or do they just need tidying up? Both of these operations are expensive, but the requirements are different. A REINDEX requires that your database be out of service, as in no one using it. It is an OFFLINE operation. If you have a large database with many tables, or one with very large tables, or both, you may find yourself in a position that requires extended downtime. What if you don't have that time? Wouldn't it be nice if you didn't have to interrupt production at all? Well, you don't have to, just keep reading.
A REORGANIZE, according to Microsoft, is an online operation and can be done while the database is accepting transactions.
REORGANIZE Specifies the index leaf level will be reorganized. This clause is equivalent to DBCC INDEXDEFRAG. ALTER INDEX REORGANIZE statement is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction. REORGANIZE cannot be specified for a disabled index or an index with ALLOW_PAGE_LOCKS set to OFF.
Of course there will be a performance hit while this operation is running, but some temporary system slowness is better than hours of maintenance downtime. Time is money. If the db ain't runnin', you ain't makin' money. (Sorry for that, it's how we say it in North Carolina)
Now comes the part where all the senior DBAs and MVPs will probably chime in and say I am wrong. I say Bring it. Numbers don't lie, and unless someone can prove to me I am doing damage, all I can say is that these numbers look good to me. The "Rule of Thumb" here is fragmentation threshold, and to a lesser extent, number of index pages. For example, the "Rule" will say, an index with < 30% fragmentation is a good candidate for a REORG: anything greater and its a REINDEX. I say do a REORG anyway. I have seen indexes with 99% fragmentation respond well to a REORG, sometimes falling back into single digit percentages. This is where number of pages becomes important. An index with a low number of data pages, say, below 100, will always contain some level of fragmentation after very little use. So, in this regard, it does not make sense to worry about indexes with very little data in them.
I have a script here that does all tables in a single database. This script is a compilation of 2 or three scripts, and some of my own handy work, but mainly from the one provided by Microsoft on index defragmentation. This script runs DBCC SHOWCONTIG and places the results in a temp table, runs the REORG using frag and page threshold parameters, then runs DBCC SHOWCONTIG again and compares the before and after fragmentation percentages so you can see how well it performed.
--DISCLAIMER - as with all code gleaned from the internets, test it thoroughly on non-production systems first. I am not responsible for any damage caused by use of this script.
Again, if there are any that disagree with my approach, feel free to speak up - I will listen to any and all responses. Until I see a reason not to use this method, I see no reason to cause a system outage over fragmented indexes.