Jeremy_Clere_7804

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',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
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 %)

 
 

Action

 
 

 SQL   statement

 
 

avg_fragmentation_in_percent > 5 AND < 30

 
 

 Reorganize   Index

 
 

 ALTER   INDEX REORGANIZE

 
 

avg_fragmentation_in_percent   > 30

 
 

 Rebuild   Index

 
 

 ALTER   INDEX REBUILD

 



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.







Outcomes