Check index fragmentation MS SQL

Discussion created by Jeremy_Clere_7804 on Nov 16, 2016

In case of slowdown  accessing the database you can check the fragmentation of the index
The following is a simple query that will list every index on every table in your database, ordered by percentage of index fragmentation

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
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()
ORDER BY indexstats.avg_fragmentation_in_percent desc

tested on SQL 2012 and SQL 2014

In order to reduce fragmentation you can  have to reorganize or rebuild the indexes. Choosing between reorganizing or rebuilding depends on the query results.
For heavily fragmented indexes a rebuild process is needed, otherwise index reorganization should be sufficient.

The following table summarizes when to use each one:


Reference   Values (in %)




 SQL   statement


avg_fragmentation_in_percent > 5 AND < 30


 Reorganize   Index




avg_fragmentation_in_percent   > 30


 Rebuild   Index




Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.