How can I quickly detect and resolve SQL Server Index fragmentation for a database?

Question

I've come across a situation where my database operations on many of my SQL Server database's tables have become very slow as more records have been added (5s for single insert to a table with 1 million records).

I've estimated that this may be due to fragmented indexes on tables in my database, because I have many tables that use (and need to use) a uniqueidentifier type for Primary Key clustered indexes.

How can I evaluate whether this is the case or not, and how can I resolve the fragmentation issues (perhaps once per deployment) if there are any fragmentation issues?

I would like a solution that works in SQL Server 2005 and higher (I am specifically working with SQL Server in an Azure database (12.0.2000.8)).

@TheGameiswar In my situation, there is a trigger on each table that updates the `CreatedAt` column of the record after insert (put in place by the Azure Mobile Services server-side library, used for offline syncing capabilities). That trigger makes each insert into an insert followed by an update. — Tommy Elliott, Apr 28 '17 at 19:58

score 9 · Answer 1 · answered Aug 25 '17 at 12:02

To check the fragmentation percentage on a table

SELECT a.index_id, 
       NAME, 
       avg_fragmentation_in_percent, 
       fragment_count, 
       avg_fragment_size_in_pages 
FROM   sys.Dm_db_index_physical_stats(Db_id('dbName'), Object_id('tableName'), 
       NULL, 
              NULL, NULL) AS a 
       INNER JOIN sys.indexes b 
               ON a.object_id = b.object_id 
                  AND a.index_id = b.index_id

To fix the fragmentation either rebuild or reorganize the index on the table

ALTER INDEX ALL ON table_name REBUILD

OR

ALTER INDEX index_name ON table_name REBUILD

-- REORGANIZE

ALTER INDEX ALL ON table_name REORGANIZE

OR

ALTER INDEX index_name ON table_name REORGANIZE

OR

DBCC DBREINDEX ('table_Name')

https://learn.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes

score 4 · Answer 2 · answered Apr 28 '17 at 20:40

Since you already know the table in which fragmentation is suspected, you can use the below T-SQL statements to identify Fragmentation.

To get the Database ID of a DB:

select name , database_id 
from sys.databases
where name = 'Database_Name'

Run these queries under the database in which the table belongs to.

To get the object ID of a table:

select * from sys.objects where name = 'Table_name'

To find the fragmentation percentage in a table:

select TableName=object_name(dm.object_id)
       ,IndexName=i.name
       ,IndexType=dm.index_type_desc
       ,[%Fragmented]=avg_fragmentation_in_percent   ,dm.fragment_count      ,dm.page_count      ,dm.avg_fragment_size_in_pages     
,dm.record_count     ,dm.avg_page_space_used_in_percent  from 
sys.dm_db_index_physical_stats(14,420770742,null,null,'SAMPLED') dm 
--Here 14 is the Database ID 
--And 420770742 is the Object ID of the table
join sys.indexes i on dm.object_id=i.object_id and
dm.index_id=i.index_id   order by avg_fragmentation_in_percent desc

If the fragmentation of an index is more than 20% then we can try rebuilding that index:

ALTER INDEX Index_Name 
ON [Database_name].[Table_Name] REBUILD

OR - to rebuild all the indexes in the table

ALTER INDEX ALL ON [Database_name].[Table_Name]
REBUILD WITH (FILLFACTOR = 80)

OR - by using DBCC DBREINDEX

DBCC DBREINDEX ('[Database_name].[ Table_Name]')

DBCC DBREINDEX ('[Database_name].[ Table _Name]', 
'Index_Name, 85)

If Fragmentation count is below 20%, you could do away with an Index rebuild or ReOrg.. instead just update statistics for that Index/Table.

To run update statistics on a table with FULLSCAN:

UPDATE STATISTICS [Database_Name].[Table_Name] 
with FULLSCAN

To Update Stats of an Index

UPDATE STATISTICS [Database_Name].[Table_Name] Index_Name
with FULLSCAN

I have given each of these as separate queries for you to get a better understanding of what is being done. Hope this helps

Tommy Elliott · Accepted Answer · 2017-04-28T21:30:43.130

Here is a SQL query solution that works in SQL Server 2005 and up, that will let you

1) first find all the indexes that need to be rebuilt or reorganized to reduce fragmentation, and then

2) with a single copy-paste of the first five columns of the results to a new query window (removing the column header line), execute all the statements (rebuild/reorganize of indexes) that will resolve the majority of the current fragmentation issues in all tables in your database.

Note: If you run into permission errors, you may need to make sure you are positioned in the master schema and that your user has appropriate permissions for the database.

I named this query: GetFragmentationOfIndexesAndFirst5ColumnsExecutedResolveFragmentation.sql

SELECT  
'alter index' as 'reindex_part1',
'[' + dbindexes.[name] + ']' as 'Index',
'on' as 'reindex_part2',
'[' + dbtables.[name] + ']' as 'Table',
CASE WHEN indexstats.avg_fragmentation_in_percent > 30
 THEN 'rebuild with (FILLFACTOR = 80)' ELSE 'reorganize' END as 'reindex_part3',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count,
indexstats.alloc_unit_type_desc,
dbschemas.[name] as 'Schema'
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
AND indexstats.avg_fragmentation_in_percent > 5
ORDER BY indexstats.avg_fragmentation_in_percent desc

I have to credit two places I used in understanding and ultimately coming to this solution:

Initial way to find fragmentation within a database: https://myadventuresincoding.wordpress.com/2013/05/27/sql-server-check-index-fragmentation-on-all-indexes-in-a-database/

How to resolve fragmentation within a database (and guideline of 5%-30% fragmentation should be resolved with reorganize of index, and 30%+ fragmentation should be resolved with rebuild of index): http://www.passionforsql.com/how-to-check-index-fragmentation-in-sql-server/

EDIT: I've included the with (FILLFACTOR = 80) part in the query above because in my case, the majority of the fragmented indexes were on uniqueidentifier columns, which should not be indexed with the default FILLFACTOR of 0 (100%) because having them that way will inevitably cause fragmentation quickly again because inserts will always need to be put between other rows due to the non-ordered creation of uniqueidentifiers. You can certainly change your pasted values to remove or change the parameters as is appropriate for your tables/indexes.

I've also found that you'll want to execute EXEC sp_updatestats after rebuilding and reorganizing indexes so that the statistics can catch up with the index changes rather than having to do so incrementally during future queries.

score 0 · Answer 4 · answered Mar 10 '23 at 22:40

Fully agree with the accepted answer. Just thought I would add this for posterity; it uses that same guidance and handles everything for you. Just run the script and defragment all indexes, or drop into a stored procedure and run nightly. You can restrict to a schema and skip sparse tables that will always appear fragmented. Enjoy!

Note that it does not alter the default FILLFACTOR (which is a great idea on uniqueidentifier columns) or rebuild the statistics, as that is often automated anyway, and adds a fair bit of time to this script.

/*
 * Smart Index Defrag
 * by Neil Laslett
 *
 * Version 1: Initial release
 *
 * Version 2: Schema-aware
 * Specify a single schema when calling to restrict to that schema (e.g. 'dbo').
 * Execute without specifying a schema to check user tables in all schemas.
 *
 * Version 3: SQL Server version aware
 * Heap tables cannot be rebuilt before SQL Server 2008.
 *
 * Version 4: Added ability to skip sparse tables which will never adequately defragment.
 *
 * See: http://msdn.microsoft.com/en-us/library/ms189858.aspx
 */
 BEGIN
    DECLARE @schema     sysname = '',
            @skipSparse bit = 0

    SET NOCOUNT ON

    DECLARE @table      sysname,
            @index_id   int,
            @object_id  int,
            @name       sysname,
            @avg        decimal(5,2),
            @msg        varchar(max),
            @ver_str    sysname,
            @version    tinyint,
            @dot        int

    SELECT  @ver_str = CONVERT(varchar(50), SERVERPROPERTY('ProductVersion')),
            @dot = CHARINDEX('.', @ver_str),
            @version = LEFT(@ver_str, @dot - 1)

    DECLARE @IndexTable TABLE
    (
        [Schema]    sysname NOT NULL,
        [Table]     sysname NOT NULL,
        index_id    int     NOT NULL,
        name        sysname NULL,
        avg_fragmentation_in_percent decimal(5,2) NOT NULL,
        result      varchar(50) NULL
    )

    DECLARE cTables CURSOR FOR
        SELECT  s.name AS s_name, o.name AS t_name, o.object_id
        FROM    sys.objects o INNER JOIN
                sys.schemas s ON o.schema_id = s.schema_id
        WHERE   o.[type] = 'U' AND
                (s.name = @schema OR @schema = '')

    OPEN cTables
    FETCH NEXT FROM cTables INTO @schema, @table, @object_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @IndexTable
        SELECT  @schema, @table, a.index_id, b.name, a.avg_fragmentation_in_percent, NULL
        FROM    sys.dm_db_index_physical_stats (DB_ID(), @object_id, NULL, NULL, NULL) AS a
        INNER JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
        WHERE   avg_fragmentation_in_percent > 5
                AND (a.index_id > 0 OR @version > 9)
                AND (a.avg_fragment_size_in_pages > 8 OR @skipSparse = 0)
        
        FETCH NEXT FROM cTables INTO @schema, @table, @object_id
    END
    CLOSE cTables
    DEALLOCATE cTables

    DECLARE cIndexes CURSOR FOR
        SELECT [Schema], [Table], index_id, name, avg_fragmentation_in_percent FROM @IndexTable

    OPEN cIndexes
    FETCH NEXT FROM cIndexes INTO @schema, @table, @index_id, @name, @avg
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @index_id = 0
        BEGIN
            SET @msg = 'Rebuilding heap table ' + @schema + '.' + @table + '...'
            RAISERROR(@msg, 0, 1) WITH NOWAIT
            EXEC('ALTER TABLE [' + @schema + '].[' + @table + '] REBUILD')
            UPDATE @IndexTable
            SET result = 'REBUILD HEAP'
            WHERE [Schema] = @schema AND [Table] = @table AND index_id = @index_id
        END
        ELSE IF @avg > 30
        BEGIN
            SET @msg = 'Rebuilding index ' + @schema + '.' + @table + '.' + @name + '...'
            RAISERROR(@msg, 0, 1) WITH NOWAIT
            EXEC('ALTER INDEX [' + @name + '] ON [' + @schema + '].[' + @table + '] REBUILD')
            UPDATE @IndexTable
            SET result = 'REBUILD INDEX'
            WHERE [Schema] = @schema AND [Table] = @table AND index_id = @index_id
        END
        ELSE
        BEGIN
            SET @msg = 'Reorganizing index ' + @schema + '.' + @table + '.' + @name + '...'
            RAISERROR(@msg, 0, 1) WITH NOWAIT
            EXEC('ALTER INDEX [' + @name + '] ON [' + @schema + '].[' + @table + '] REORGANIZE')
            UPDATE @IndexTable
            SET result = 'REORGANIZE INDEX'
            WHERE [Schema] = @schema AND [Table] = @table AND index_id = @index_id
        END
        
        FETCH NEXT FROM cIndexes INTO @schema, @table, @index_id, @name, @avg
    END
    CLOSE cIndexes
    DEALLOCATE cIndexes

    PRINT 'Done!'
    SELECT * FROM @IndexTable
END

How can I quickly detect and resolve SQL Server Index fragmentation for a database?

4 Answers4