SQL Server: Index Defrag

Today in Microsoft SQL Server, I discovered first-hand that defragmenting indexes, especially on large tables that experience a high volume of INSERT, UPDATE and DELETE transactions, can really improve query performance. In my case, it took an update transaction that was running more than 6 hours before it failed down to 24 minutes with successful completion.

First, I ran a DBCC SHOWCONTIG (table_name, index_name) statement to reveal that the index was 46% fragmented. To place that in perspective, industry experts say that 30% is too much. Next I defragmented the index using the DBCC INDEXDEFRAG (database_name, table_name, index_name) command. This only took 15 minutes, but it chewed up 8GB of transaction log space so I had to keep a close eye on it in the Taskpad view of Enterprise Manager and adjust the growth limit on-the-fly as the space was quickly diminishing.

After that was complete, I executed the update transaction and was blown away with the 97% increase in speed. I'll be checking the other indexes tomorrow and creating a few scheduled jobs to defragment on a monthly basis.