Microsoft SQL Server: Detecting Fragmented Indexes – Improving Performance

Just like a disk can become physically fragmented, so can any logical structure that uses this medium — tablespaces, tables, and indexes. All of them fragment. It’s part of the game — if you want to play, accept the rules. Fragmentation (regardless of the database object) happens more or less depending on the type of operations the database performs.

Transactional databases (OLTP) tend to fragment much more than analytical databases (OLAP/DW), at least if managed correctly.

The more writes, the greater the probability of fragmentation. Modeling can also impact fragmentation.

Of course, anything fragmented is messy. And anything messy doesn’t perform well. In databases, this means performance loss.

The expected side effect of defragmentation is performance improvement — or, if you prefer: recovery of lost performance. In this article, we’re focusing purely on index fragmentation.

But for those eager for code, let’s get to the point: Index Fragmentation in Microsoft SQL Server. Handling fragmentation involves two basic steps: identification and action.

How to identify fragmented indexes in SQL Server?

Simple — here’s the “cake recipe”:

sqlCopyEditUSE <your_database>;

SELECT 
    dbschemas.[name] AS 'Schema',
    dbtables.[name] AS 'Table',
    dbindexes.[name] AS 'Index',
    ROUND(indexstats.avg_fragmentation_in_percent, 1) AS 'Fragmentation'
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
JOIN sys.tables dbtables ON dbtables.[object_id] = indexstats.[object_id]
JOIN sys.schemas dbschemas ON dbtables.[schema_id] = dbschemas.[schema_id]
JOIN sys.indexes dbindexes 
    ON dbindexes.[object_id] = indexstats.[object_id] 
    AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() 
  AND dbindexes.name IS NOT NULL
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

SQL Server will return a list of all indexes on all tables, ordered by fragmentation percentage — from most fragmented to least. The more fragmented, the worse.

My rule of thumb:

• ≥ 35% fragmentation → Rebuild the index
• 10% to 34% → Reorganize the index

Commands:

Reorganize a single index:

sqlCopyEditALTER INDEX <index_name> ON <schema>.<table> REORGANIZE;

Rebuild a single index:

sqlCopyEditALTER INDEX <index_name> ON <schema>.<table> REBUILD;

Do it all at once (carefully!):

sqlCopyEditALTER INDEX ALL ON <schema>.<table> REORGANIZE;  -- or REBUILD

Note: Only use ALL if you’re very familiar with your database workload and maintenance windows (or if you’re trying to annoy your boss).

Frequency of Defragmentation?

Start with identification. Fragmentation starts becoming an issue around 20–25%. If your indexes are below 10–15%, leave them alone. Schedule a weekly check — the more fragmentation you find, the shorter your maintenance window needs to be.

Schedule a meeting here

Visit our Blog

Learn more about databases

Learn about monitoring with advanced tools

Have questions about our services? Visit our FAQ

Want to see how we’ve helped other companies? Check out what our clients say in these testimonials!

Discover the History of HTI Tecnologia