Index Fragmentation - SQL Server Query Performance Tuning - page 245

page_count : This number is a literal count of the number of index or data pages that make
up the statistic. This number is a measure of size but can also help indicate fragmentation.
If you know the size of the data or index, you can calculate how many rows can fit on a page.
If you then correlate this to the number of rows in the table, you should get a number close to
the pagecount value. If the pagecount value is considerably higher, you may be looking at a
fragmentation issue. Refer to the avg_fragmentation_in_percent value for a precise measure.

•

•

avg_page_space_used_in_percent : To get an idea of the amount of space allocated within the
pages of the index, use this number. This value is NULL when the sampling mode is Limited.

•

recordcount : Simply put, this is the number of records represented by the statistics. For
indexes, this is the number of records within the current level of the B-tree as represented
from the scanning mode. (Detailed scans will show all levels of the B-tree, not simply the
leaf level.) For heaps, this number represents the records present, but this number may not
correlate precisely to the number of rows in the table since a heap may have two records after
an update and a page split.

•

avg_record_size_in_bytes : This number simply represents a useful measure for the amount
of data stored within the index or heap record.

Running sys.dm_db_index_physical_stats with a Detailed scan will return multiple rows for a given index.
That is, multiple rows are displayed if that index spans more than one level. Multiple levels exist in an index when
that index spans more than a single page. To see what this looks like and to observe some of the other columns of data
present in the dynamic management function, run the query this way:

SELECT ddips.*
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2012'),
OBJECT_ID(N'dbo.Test1'),NULL,
NULL,'Detailed') AS ddips;

To make the data readable, I've broken down the resulting data table into three pieces in a single graphic;
see Figure 13-12 .

Figure 13-12. Detailed scan of fragmented index