I was investigating an issue with am Azure SQL Server database that was much bigger than it ought to be. It was around 50 GB, but should really have been about 15 GB. I ran an old query I keep lying around that finds the size of tables in a SQL database and there was a lot of unused space. Usually shrinking the database solves this issue, but it turns out I was experiencing a slightly different problem: SQL server index fragmentation.

There are a couple of nasty side effects to index fragmentation. It makes your indexes less efficient, but it can also result in a great deal of storage being used up unnecessarily.

Finding Index Fragmentation

If you want to know if you have a problem with SQL Server index fragmentation, here is the script that tells you how much fragmentation you have as a percentage.

SELECT
    DB_NAME() AS DatabaseName,
    '[' + OBJECT_SCHEMA_NAME(STAT.object_id) + '].[' + OBJECT_NAME(STAT.object_id) + ']' AS TableName,
    IDX.name AS IndexName,
    IPS.index_type_desc AS IndexType,
    IPS.avg_fragmentation_in_percent AS AverageFragmentationPercent
 FROM
    sys.dm_db_partition_stats STAT
 INNER JOIN
    sys.indexes IDX ON STAT.object_id = IDX.object_id AND STAT.index_id = IDX.index_id
 CROSS APPLY
    sys.dm_db_index_physical_stats(DB_ID(), STAT.object_id, STAT.index_id, NULL, 'LIMITED') IPS
 ORDER BY
    IPS.avg_fragmentation_in_percent DESC

Fixing Fragmented Indexes

If you have too much fragmentation (more than 30%), you can fix it by running:

ALTER INDEX ALL ON dbo.MyTableName REBUILD WITH (ONLINE=ON)

Certain data types prevent you using ONLINE=ON (like big text fields). In these cases, you can’t do it with this option enabled, so you have to use:

ALTER INDEX ALL ON dbo.MyTableName REBUILD

If you have index with less than 30% fragmentation, you can reorganise them by running:

ALTER INDEX [PK_MyTableName] ON dbo.MyTableName REORGANIZE;

You can also fix them all by pulling the table names into a cursor and running it for each one, as shown below. Note that there is a MinPercentage parameter that you can use to set the level at which you want to rebuild and index. You can use this to reduce the number of indexes you rebuild. Use a higher number if you are running this for the first time, and lower it for subsequent runs. Ideally, you shouldn’t have more than 10% fragmentation on any index.

DECLARE @MinPercentage INT = 50
DECLARE @TableName VARCHAR(255)
DECLARE @Message VARCHAR(255)
DECLARE TableCursor CURSOR FOR
(
    SELECT
        '[' + TBLS.TABLE_SCHEMA + '].[' + TBLS.TABLE_NAME + ']' AS [TableName]
    FROM
        INFORMATION_SCHEMA.TABLES TBLS
    WHERE
        TBLS.TABLE_TYPE = 'BASE TABLE'
    AND
        '[' + TBLS.TABLE_SCHEMA + '].[' + TBLS.TABLE_NAME + ']' IN
        (
            SELECT
                '[' + OBJECT_SCHEMA_NAME(STAT.object_id) + '].[' + OBJECT_NAME(STAT.object_id) + ']' AS TableName
                FROM
                sys.dm_db_partition_stats STAT
                INNER JOIN
                    sys.indexes IDX ON STAT.object_id = IDX.object_id AND STAT.index_id = IDX.index_id
                CROSS APPLY
                    sys.dm_db_index_physical_stats(DB_ID(), STAT.object_id, STAT.index_id, NULL, 'LIMITED') IPS
            GROUP BY
                '[' + OBJECT_SCHEMA_NAME(STAT.object_id) + '].[' + OBJECT_NAME(STAT.object_id) + ']'
            HAVING
                MAX(IPS.avg_fragmentation_in_percent) > @MinPercentage
        )
)
 
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @Message = 'Rebuilding index for table: ' + @TableName
    RAISERROR (@Message, 10, 1) WITH NOWAIT
    BEGIN TRY
        EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (ONLINE=ON)')
    END TRY
    BEGIN CATCH
        SELECT @Message = 'Failed to rebuild index for table "' + @TableName + '" - trying again without (ONLINE=ON).'
        RAISERROR (@Message, 10, 1) WITH NOWAIT
        EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
    END CATCH

    FETCH NEXT FROM TableCursor INTO @TableName
END
 
CLOSE TableCursor
DEALLOCATE TableCursor

Results

Before running the script to fix fragmentation:

Index Fragmentation
Average 16.48567749
Max     99.99346917

Database Size
Total Size  45.73765625
Used Size   15.30108398
Free Size   30.43657227

After running the script to fix fragmentation:

Index Fragmentation
Average 4.67736307       (-11.80831442)
Max     91.32310642      (-8.67036275)

Database Size
Total Size  16.76264648  (-28.97500977)
Used Size   15.22998047  (-0.07110351)
Free Size   1.532666016  (-28.90390625)

The key here is that the database has dropped to 35% of its original size – just from defragging the indexes. There are still some badly fragmented indexes, but the average fragmentation is massively improved. For best results, rebuild your indexes as part of a healthy maintenance plan.

Maintenance Plan

If you are running a SQL instance with a SQL agent available, you can set up a traditional maintenance plan. If you are running on Azure, you may want to add the Azure SQL Maintenance Plan procedure by Yochanan Rachamim – and run it with some regularity. This is a pretty smart proc that determines the best actions to resolve issues such as fragmentation of indexes.

Fragments image from pxhere. CC0 Public Domain.