Index fragmentation is one of the most common causes of gradual SQL Server performance degradation. You can check the fragmentation level of a database using the built-in DMV sys.dm_db_index_physical_stats, which returns fragmentation percentage and page count for every index in the database. Run this against your target database and focus on indexes with fragmentation above 30% and a page count above 1000 - those are the ones actually worth acting on.

Why Index Fragmentation Matters

SQL Server stores data in 8KB pages. As rows are inserted, updated, and deleted over time, those pages become fragmented - meaning the logical order of the index no longer matches the physical order on disk. The result is more I/O operations to retrieve the same data, slower query execution, and increased pressure on the buffer pool.

The impact isn't always obvious. Fragmentation tends to build gradually, and performance degrades in a way that's easy to attribute to other causes - growing data volumes, increased user load, or network issues. Checking fragmentation level regularly is the only way to know for certain whether your indexes are contributing to the problem.

The Script: How to Check Fragmentation Level of a Database

The query below joins four system views to give you a practical picture of index fragmentation across the current database. It returns the table name, index name, fill factor, row count, average fragmentation percentage, and page count.

SELECT
    B.name AS TableName
    , C.name AS IndexName
    , C.fill_factor AS IndexFillFactor
    , D.rows AS RowsCount
    , A.avg_fragmentation_in_percent
    , A.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) A
INNER JOIN sys.objects B
    ON A.object_id = B.object_id
INNER JOIN sys.indexes C
    ON B.object_id = C.object_id
    AND A.index_id = C.index_id
INNER JOIN sys.partitions D
    ON B.object_id = D.object_id
    AND A.index_id = D.index_id
WHERE C.index_id > 0
AND A.avg_fragmentation_in_percent > 50
-- AND page_count > 1000

Run this in the context of the database you want to analyse. The DB_ID() function without arguments targets the current database. If you want to check a specific database without switching context, replace it with DB_ID('YourDatabaseName').

The WHERE C.index_id > 0 filter excludes heaps (tables with no clustered index). Heaps have their own fragmentation characteristics and are handled differently, so it makes sense to exclude them from this particular analysis.

Understanding the Results

The two most important columns are avg_fragmentation_in_percent and page_count. Used together, they tell you whether fragmentation is actually a problem worth fixing.

avg_fragmentation_in_percent is the logical fragmentation of the index. Microsoft's general guidance, which aligns with what most experienced DBAs use in practice, is:

  • Below 10%: no action needed
  • 10% to 30%: consider an index reorganise (ALTER INDEX ... REORGANISE)
  • Above 30%: consider an index rebuild (ALTER INDEX ... REBUILD)

These thresholds aren't absolute rules. A 35% fragmented index on a 200-page table is unlikely to cause measurable performance problems. The same fragmentation level on a 50,000-page index on a busy OLTP table is a different story entirely.

page_count is the key filter for separating noise from signal. Indexes with fewer than 1,000 pages are generally small enough that fragmentation has negligible impact on query performance. SQL Server can read the entire index in relatively few I/O operations regardless of fragmentation. To focus on indexes that are genuinely worth maintaining, uncomment the last line in the script:

AND page_count > 1000

This single change significantly reduces the list and points you toward the indexes where maintenance will actually make a difference.

What About Fill Factor?

The fill_factor column in the results is worth paying attention to. Fill factor controls how full SQL Server packs data into each index page when an index is created or rebuilt. A fill factor of 80 means SQL Server leaves 20% of each page free, which gives room for future inserts and updates without immediately causing page splits.

If you're seeing indexes that fragment rapidly - returning to high fragmentation within days of a rebuild - the fill factor is often the culprit. A fill factor that's too high (close to 100) on a heavily updated table will cause frequent page splits and accelerate fragmentation. Tuning fill factor is a separate exercise, but the data is right there in this query.

A Note on Ola Hallengren's Index Optimisation Script

Many SQL Server environments use Ola Hallengren's IndexOptimize solution for automated index maintenance. It's an excellent, well-maintained tool and the de facto standard for scheduled index maintenance in SQL Server shops. If you're using it, there are a couple of things worth knowing in the context of this fragmentation check.

By default, Ola's script skips index maintenance on tables with a low page count. This is intentional and sensible - it avoids wasting maintenance window time on indexes that don't need it. If you want to override this behaviour, you can set the @PageCountLevel parameter explicitly when calling the script. However, read through Ola's FAQ before changing this default. The reasoning behind it is sound, and overriding it without understanding the implications can lead to longer maintenance windows with minimal benefit.

The fragmentation query above complements Ola's tooling well. Use it to audit what's happening in your environment, understand which indexes are fragmenting fastest, and inform decisions about maintenance schedules and fill factor settings.

How Often Should You Check Fragmentation?

For most production environments, a weekly fragmentation check is a reasonable baseline. Environments with high transaction volumes - busy e-commerce platforms, ERP systems, or databases with frequent bulk loads - may warrant more frequent checks.

The practical approach is to run this query before and after scheduled maintenance windows. That tells you whether your maintenance jobs are actually keeping fragmentation under control, and whether your maintenance window is long enough to cover all the indexes that need attention.

Brent Ozar's writing on index fragmentation is also worth reading if you want to go deeper on the topic. His analysis of when fragmentation actually matters versus when it's a red herring is grounded in real-world testing.

Key Takeaways

  • Use sys.dm_db_index_physical_stats to check fragmentation level of a database. Focus on indexes with fragmentation above 30% and a page count above 1,000 for meaningful results.
  • Fragmentation below 10% needs no action. Between 10% and 30%, consider a reorganise. Above 30%, consider a rebuild - but always factor in page count before deciding.
  • Small indexes (under 1,000 pages) are rarely worth rebuilding regardless of fragmentation percentage. Uncomment the page_count > 1000 filter to cut through the noise.
  • Rapid re-fragmentation after a rebuild usually points to a fill factor problem, not just high write volume.
  • If you're using Ola Hallengren's IndexOptimize, understand the @PageCountLevel default before overriding it.

If you'd like a thorough assessment of index fragmentation and maintenance practices across your SQL Server environment, DBA Services offers SQL Server health checks that cover exactly this kind of issue. We work with organisations across Australia to identify performance risks and implement practical, sustainable maintenance strategies. Get in touch with the team to find out more.