Two indexes can hold the same rows while requiring different numbers of page reads. Page density explains how much useful data each leaf page carries during a scan.
Measure Page Density as Well as Order
Logical fragmentation describes how an index's logical page order differs from its physical ordering. Density describes how full the pages are. These measurements answer different questions and deserve separate attention.
I inspect density when a scan reads more pages than its data suggests. I also check the index's fill factor before recommending maintenance. Deliberately reserved free space can explain a low density without requiring an emergency.
Lower density means the same stored rows occupy more pages. Scans must process that larger leaf population, and the buffer pool must cache more pages. The impact exists even when the storage handles random reads efficiently.
A tidy page order does not fill an empty page. Sorting the seats leaves the empty chairs empty. Keep that distinction clear before rebuilding an index to improve a fragmentation percentage.
Use a disposable database for this experiment. It creates a random-key table, loads synthetic rows, and rebuilds its clustered index. Keep the measured results from your own instance rather than expecting a specific percentage or read count.
Build a Random-Key Fixture
NEWID assigns random identifiers that send inserts throughout the index's key range. Splits and available free space depend on the actual insertion pattern. The loop inserts 100 small batches, so new keys land between existing ones and split full pages. The payload provides enough row content to make leaf page occupancy meaningful.
CREATE TABLE dbo.PageDensityDemo
(
ItemID uniqueidentifier NOT NULL DEFAULT NEWID(),
Payload char(200) NOT NULL,
CONSTRAINT PK_PageDensityDemo PRIMARY KEY CLUSTERED(ItemID)
WITH (FILLFACTOR = 100)
);
SET NOCOUNT ON;
DECLARE @Batch int = 1;
WHILE @Batch <= 100
BEGIN
WITH Digits AS
(
SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS v(n)
)
INSERT dbo.PageDensityDemo(Payload)
SELECT REPLICATE('x', 200)
FROM Digits AS a CROSS JOIN Digits AS b;
SET @Batch += 1;
END;The generator's shape is an input to the test, not a reported production table size. Run the load, then inspect the index metrics and scan messages. One large INSERT would sort its rows first and fill pages cleanly, which hides the effect. The resulting density depends on the built index and inserted keys.
This is a clustered rowstore index rather than a heap or columnstore example. Those structures have different maintenance and interpretation rules. Keep the index type visible when expanding the investigation beyond this fixture.
Random identifiers also affect row width and nonclustered index keys when used as a clustered key. Those design costs are separate from density. Do not attribute every cost of this key choice to fragmentation alone.
Read Leaf Page Density in SAMPLED Mode
Limit the physical statistics call to this table and index. Guard the object identifier so a missing table cannot turn the query into a broader scan. Then inspect the leaf-level in-row allocation results.
DECLARE @ObjectID int = OBJECT_ID(N'dbo.PageDensityDemo');
IF @ObjectID IS NULL THROW 50001, 'The fixture table does not exist.', 1;
SELECT i.name AS IndexName, p.index_level, p.page_count,
p.avg_page_space_used_in_percent,
p.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), @ObjectID, 1, NULL, N'SAMPLED') AS p
JOIN sys.indexes AS i
ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE p.index_level = 0
AND p.alloc_unit_type_desc = N'IN_ROW_DATA';LIMITED mode does not report avg_page_space_used_in_percent. A null value from that mode is not evidence of a completely empty index. Choose a mode that supplies the measurement you intend to interpret.
SAMPLED mode estimates physical statistics for larger indexes. For sufficiently small indexes, SQL Server uses detailed processing instead. The function's mode is an inspection policy rather than a guarantee that every returned value was sampled.
The function itself consumes resources and acquires locks. Do not run a broad physical scan repeatedly during a busy incident. Start with the specific index whose scan behavior requires explanation.
For a partitioned index, retain partition identity during comparison. Different partitions can have different insertion histories and occupancy. An unqualified average hides which partition would benefit from maintenance.
Record the Baseline Scan
Enable STATISTICS IO and read the table's logical reads in the Messages output. The aggregate below requires the payload across the fixture. Keep the query unchanged for the after-rebuild comparison.
SET STATISTICS IO ON;
SELECT SUM(CONVERT(bigint, DATALENGTH(Payload))) AS PayloadBytes
FROM dbo.PageDensityDemo;
SET STATISTICS IO OFF;Record the actual plan along with the messages. Logical reads measure page access, while the aggregate result checks the data being summarized. Neither output should be replaced with an invented example measurement.
Cache warmth changes physical reads and elapsed time. Logical reads are useful for comparing the work this scan performs, but repeated or parallel access affects interpretation. Keep execution settings and the plan comparable.
Page density is a reason to inspect scan work rather than an isolated maintenance target. Connect the measured occupancy with leaf page count and the query's reads. A small rarely scanned index has a different priority from a frequent large scan.
Rebuild With an Explicit Choice
The test rebuild below uses fill factor one hundred to pack the leaf pages for comparison. This is a deliberate fixture setting. It is not a recommendation that every random-insert workload should use that setting.
ALTER INDEX PK_PageDensityDemo
ON dbo.PageDensityDemo
REBUILD WITH (FILLFACTOR = 100);
SET STATISTICS IO ON;
SELECT SUM(CONVERT(bigint, DATALENGTH(Payload))) AS PayloadBytes
FROM dbo.PageDensityDemo;
SET STATISTICS IO OFF;Run the physical statistics query again after the rebuild. Compare density, page count, and scan reads with the captured baseline. Verify the aggregate result still represents the same stored payload.
A rebuild also updates index statistics and can affect subsequent compilation. That is another reason to retain the actual plans. A changed plan is not evidence that density alone caused every performance difference.
Rebuilds consume log space, I/O, processor time, and working space. Offline operations also affect access, while supported online operations still have locking and resource costs. Plan production maintenance around the actual operation and workload.
Balance Reads Against Future Splits
A lower fill factor reserves space during index creation or rebuild. It reduces initial page occupancy in exchange for room for later changes. That free space is not continuously maintained as a permanent percentage.
For random inserts, tightly packed pages can split again as new rows arrive. Rebuilding every night without addressing the insertion pattern can repeat the same cycle. Compare split activity and scan costs before choosing a different fill factor.
Which workload pays more for this index, ongoing inserts or repeated scans? That question helps select a defensible trade-off. A fragmentation threshold alone cannot answer it.
Use Page Density to Set Priorities
Fast storage reduces some penalties associated with out-of-order physical reads. It does not eliminate the work of reading additional low-density pages. Examine both measurements and the workload instead of treating logical fragmentation as the complete story.
Page density becomes useful when it explains observed scan work and a maintenance choice. Retain before and after measurements, unchanged test SQL, and the chosen fill factor. Those details make the decision reviewable.
Finish by monitoring how quickly occupancy changes under normal writes. A rebuild's initial state is only one point in the index's lifecycle. Maintain the index for a demonstrated workload benefit rather than a pleasing percentage.
Related reading on this blog: Sample Script to Check Index Fragmentation with RowCount and Fill Factor: Instance Level or Index Level.
A fragmentation percentage is not a maintenance plan, it is one measurement beside density and workload.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
