Technical FAQs

Ask a Question

How to Check Index Fragmentation on Indexes in a Database

Issue
SQL Queries taking longer than normal to complete.

Product
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM

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

Cause
When a database is frequently updated via INSERT, UPDATE, or DELETE statements we can expect it to become fragmented over the time.
If database indexes are fragmented, the SQL Server query optimizer may chose a non-optimal execution plan when using an index to resolve a query.
This will affect the overall query performance and you may notice a query behaving slower than normal.

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

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

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

In order to reduce fragmentation we will 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
Was this helpful?
What can we do to improve the information ?