Rebuilding fragmented indexes and tables

Over time indexes and tables will get fragmented. What that means is that the real data pages containing the data for the table or the index are more and more scattered around our data files and not neatly side by side, pretty much the same as disk fragmentation.

The effect this has is a loss in performance since data has to be picked up in different places, indexes must be read from different places, writes go crisscross all over the file(s). A lot of additional I/O operations.

To prevent that from happening we should regularly check our tables and indexes for fragmentation. You can use either DBCC SHOWCONTIG or the DMV sys.dm_db_index_physical_stats to query the fragmentation level of objects.

-- set minimum page count the index should have
-- to be considered for a rebuild.
-- indexes smaller than 100, 120 pages usually don't gain anything from rebuilding.
declare @IPageCnt int = 100;
-- set the minimum fragmentation level of an index
-- at which threshold it should be rebuild. (value in % of fragmentation)
declare @IMinFragmentation int = 30;
SELECT
     OBJECT_SCHEMA_NAME(FRAG.[object_id]) + '.' + OBJECT_NAME(FRAG.[object_id])  as [table]
    ,SIX.[name] as [index]
    ,FRAG.avg_fragmentation_in_percent as [fragmentation]
    ,FRAG.page_count as [pages]
    ,'alter index ' + SIX.[name]
        + ' on '+OBJECT_SCHEMA_NAME(FRAG.[object_id])
        + '.' + OBJECT_NAME(FRAG.[object_id])
        + ' rebuild;' as [rebuild_sql]
FROM
    sys.dm_db_index_physical_stats
    (
        db_id(),    --use the currently connected database
        0,          --Parameter for object_id.
        DEFAULT,    --Parameter for index_id.
        0,          --Parameter for partition_number.
        DEFAULT     --Scanning mode. Default to "LIMITED", which is good enough
    ) FRAG
    INNER JOIN sys.indexes SIX ON FRAG.[object_id] = SIX.[object_id]
        AND FRAG.index_id = SIX.index_id
WHERE
    -- consider only those indexes that need treatment
    FRAG.page_count > @IPageCnt
    AND FRAG.avg_fragmentation_in_percent > @IMinFragmentation
ORDER BY
    FRAG.avg_fragmentation_in_percent DESC

 The level of fragmentation you are willing to allow is up to you. You should consider the time the size of the table, the available space, the time it takes to rebuild tables and indexes etc. As a rule of thumb, the bigger the table the lower the threshold. On a huge table with several billion rows 10% mean several hundred million rows scattered around the data file, on a small table with some ten thousand rows even 40% or 50% fragmentation might be tolerable.

In this example I am interested in all tables that have a fragmentation level of 30% or more.

NOTE: You should always know resp. try to find out why fragmentation is happening, because in many cases it can hint to an underlying problem with the database setup and design. Fragmentation could hint to disk problems, memory problems or poor clustered key design.

Defragment Indexes

Once you identified the indexes and tables there are a lot of things to take into account before defragmenting. There are several ways to defragment an index.

alter index IX_myIndex on dbo.myTable reorganize

This will reorganize the leaf level data of an index, clustered or nonclustered. This operation is always done online. It reorders the index pages and also tries to compact them. The index and the table remain online.  It’s better than nothing but then again not much more than that. With a low level of fragmentation you might want to try a reorganization.

alter index IX_myIndex on dbo.myTable rebuild

This does what it says on the label: it will rebuild the whole index from scratch. New index pages are created in order and compacted together. This operation can be done online or offline.

Depending on the index size, rebuilding will take up a lot of temporary space:

– Size of index in the data file
– Enough space in tempdb for sorting
– Or space for sorting in the data file with sort_in_tempdb = off 

When rebuilding a clustered index be aware that the clustered index is the table, so you will need free space to move the whole table around! 

To make room for this operation you can disable other indexes on the table. This will remove all the leaf data from the index, but not the meta data and the index definition. It will also prevent the index from being constantly updated while the rebuild is moving data pages around. But beware that you have to rebuild the indexes afterwards to enable them again.

Also, if you disable a clustered, unique or primary key index, all associated non-clustered, non-unique indexes AND all foreign key constraints associated with the table will also be disabled and will have to be rebuilt afterwards. The table data will be completely inaccessible until the index is rebuilt. So, careful with disabling indexes!

Defragment Tables

If the table has a clustered index (which every table should have!) then rebuilding the clustered index IS rebuilding the table, and that’s exactly what you should do.

If there is no clustered index you can use

alter table dbo.myTable rebuild

Note that this will also rebuild ALL indexes on that table.

Important Note: After you rebuilt your indexes/tables, recompile your stored procedures!

After rebuilding indexes and/or tables you should recompile your stored procedures which access this table. The statistics will be up to date and the efficiency of the indexes might have change.

exec sp_recompile N'dbo.myTable'

 This command marks the table myTable for recompilation. This means, that every stored procedure accessing the table for the first time after the command will be recompiled and receive a new execution plan.

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: