Avoiding Index Maintenance Rebuilds

Abstract

How can that be if I'm checking the fragmentation using SQL Server's Dynamic Management Views (DMVs)? The problem can affect nonclusiered and clustered indexes, and unless you're actively keeping !rack of when the fragmentation threshold triggers he maintenance routine to rebuild an index, you'll never know that you have the problem.

I recently implemented an automated index maintenance system for our SQL Server machines, but I ran into some trouble. It seems that for some tables, the clustered indexes are being rebuilt no matter how much fragmentation they have. How can that be if I'm checking the fragmentation using SQL Server's Dynamic Management Views (DMVs)?

This is a very common problem that people have when maintaining their own indexes. To restate the issue: An index is rebuilt or reorganized, even though it has very low fragmentation. Why does this happen?

The problem can affect nonclusiered and clustered indexes, and unless you're actively keeping !rack of when the fragmentation threshold triggers [he maintenance routine to rebuild an index, you'll never know that you have the problem. It can be a big problem - especially ii" the maintenance routine is unnecessarily and repeatedly rebuilding large indexes. This wastes CPU and I/O resources and can generate a lot of transaction logging.

The problem arises when using the sys.dm_db_ index_physical_stats DM V on indexes (hat have offrow large object (LOB) data. This can happen with both clustered and nonclustered indexes because nonclustered indexes can explicitly INCLUDE non-key LOB columns, and both can have ROW_ OVERFLOW data- that is. SQLVARIANT or N/VARCHAR ( 1 -8000) data - -that has been pushed off-row when the record size exceeds 8060 bytes.

In these cases, the index will have multiple allocation units: one for the row data, one for off-row LOB columns, and one for ROW_ OVERFLOW. The sys.dm_db_index_physicaL stats DMV will return at least one row of output for each allocation unit in an index, including the fragmentation of the allocation unit. It's entirely possible that an index can have very low fragmentation in the index rows, but the LOB or ROW_OVERFLOW data is fragmented and erroneously triggers a rebuild operation during regular maintenance.

The fix for this problem is to change the code thai queries the sys.dm__db_index_physical_stats DMV to have a WHERE clause, like so:

This excludes the LOB and ROW_OVERFLOW allocation units from consideration and prevents unnecessary rebuilds from being triggered. You might want to get more sophisticated with your logic if you do want to look at the LOB data to decide when to use the LOB_COMPACTION option of ALTER INDEX ... REORGANIZE. However, you'll need to dump ihe output from querying the DMV into a temporary table to be able to do that, as well as triizner rebuilds.