In this post we’ll look at how to check the fragmentation level of the indexes on your SQL Server tables, and how to reorganize the ones that need it.
Indexes are one of the biggest factors in how fast your T-SQL queries run. Think of an index as the index at the back of a book, built on the columns you choose. If you’ve indexed column x in a table, searching on that column or joining on it becomes much faster. There are two basic kinds of index: clustered and nonclustered.
Because tables are constantly hit with INSERT, UPDATE and DELETE statements, index pages get fragmented over time. It’s a lot like files getting fragmented on an old hard drive and needing a defrag. Fragmented indexes gradually lose performance.
Checking index fragmentation
1. In SSMS: In the database you want to check, expand Tables, open the table and find the index under the Indexes folder.
Right-click the index and open Properties. The Fragmentation page shows how fragmented it is.
2. With T-SQL: To list every index in a database ordered by fragmentation, use the sys.dm_db_index_physical_stats function:
SELECT dbtables.[name] AS [Table],
dbindexes.[name] AS [Index],
ROUND(indexstats.avg_fragmentation_in_percent, 3) AS Fragmentation,
indexstats.record_count,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(N'YourDatabase'), NULL, NULL, NULL, 'SAMPLED') AS indexstats
INNER JOIN sys.tables AS dbtables
ON dbtables.[object_id] = indexstats.[object_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(N'YourDatabase')
AND indexstats.avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent DESC;The avg_fragmentation_in_percent > 30 condition returns indexes that are more than 30% fragmented. On very small tables (under a few hundred pages) a high percentage usually makes no real difference, so keep an eye on page_count too.
Reorganizing fragmented indexes
There are several ways to reorganize an index; I’ll cover two of them.
1. In SSMS: Right-click the index and choose Reorganize. In the Reorganize Indexes dialog, click OK to start.
2. With T-SQL: You can reorganize a single index or every index on a table.
-- Reorganize one index on a table
USE [YourDatabase];
GO
ALTER INDEX [IndexName] ON [dbo].[TableName] REORGANIZE;
GO
-- Reorganize all indexes on a table
ALTER INDEX ALL ON [dbo].[TableName] REORGANIZE;
GOReorganize or rebuild?
REORGANIZE is an online, lightweight operation. It tidies up the leaf level of the index in place, and you can stop it halfway if you need to. REBUILD recreates the index from scratch. It’s more thorough, but it uses more resources and, outside Enterprise edition, can lock the table while it runs. The usual rule of thumb is to reorganize at moderate fragmentation and rebuild when it’s high. That said, rather than applying thresholds blindly, it’s best to measure whether index maintenance actually improves your queries.
ALTER INDEX [IndexName] ON [dbo].[TableName] REBUILD;Common mistakes
- Rebuilding everything every night: Unneeded rebuilds bloat the log, slow down backups and replication, and stretch the maintenance window. Only touch indexes that are actually fragmented and large enough to matter.
- Forgetting statistics:
REORGANIZEdoes not update statistics;REBUILDupdates that index’s statistics with a full scan. RunUPDATE STATISTICSafter a reorganize if needed. - Looking only at the percentage: Page density (
avg_page_space_used_in_percent) matters too. Half-empty pages mean more reads and more memory. This column is returned inSAMPLEDorDETAILEDmode. - Setting fill factor too low: A very low fill factor leaves pages empty from the start and raises read costs. Lower it only for indexes that suffer frequent page splits.
Frequently asked questions
Does fragmentation still matter on SSDs?
The physical order of pages matters much less on SSDs. Page density still matters: low density means more pages and more memory to read the same data. Keep measuring, but maintain indexes based on need rather than on a fixed schedule.
What is a practical way to automate maintenance?
A common choice is the IndexOptimize procedure from Ola Hallengren’s free maintenance solution, scheduled with SQL Server Agent. You can pass thresholds and a minimum page count as parameters. On Enterprise edition, REBUILD WITH (ONLINE = ON) rebuilds without locking the table for the whole operation.
Related posts
- SQL Server Window Functions: Ranking, LAG and Running Totals
- RegEx Comes to T-SQL: The New REGEXP Functions in SQL Server 2025
STAY IN THE LOOP
New guides, sorted by topic in a single email.
Every Monday, the week's new posts arrive grouped by topic. No new posts, no email.