Browse FAQs

How to Check Index Fragmentation on Indexes in a Database

Published date: 29 June 2020

SQL Queries taking longer than normal to complete.

Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise

SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016

when a database is frequently updated via INSERT, UPDATE, or DELETE statements, over time these modifications can cause the information in the index to become scattered in the database (fragmented). Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file.
If database indexes are fragmented, the SQL Server query optimizer may choose a non-optimal execution plan when using an index to resolve a query. Heavily fragmented indexes can degrade query performance and cause your application to respond slowly.

*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.*

The following is a simple query that will list every index on every table in your database, ordered by the 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

This query can be modified to focus on specific tables by appending the table name to the 'WHERE' clause:

WHERE indexstats.database_id = DB_ID() and dbtables.[name] like '%%'

Depending on the index fragmentation percentage, fragmentation can be reduced by reorganizing or rebuilding. For heavily fragmented indexes (greater than 30%) a rebuilding process is needed, else index reorganizing is 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

Indexes can be rebuilt/reorganize in SQL Server Management Studio using the following steps;
1- In Object Explorer locate the table that holds the intended index and expand it.
2- Expand Indexes.
3- Right-click on the index and click on Rebuild or Reorganize.


Was this helpful?

What can we do to improve the information ?

Can't find what you are looking for?

Reach out to our customer care team to receive information on technical support, assistance for complaints and more.