Index Fragmentation - SQL Server Query Performance Tuning - page 246

As you can see, two rows were returned, representing the leaf level of the index ( index_ level = 0 ) and
representing the first level of the B-tree ( index_level = 1 ), which is the second row. You can see the additional
information offered by sys.dm_db_index_physical_stats that can provide more detailed analysis of your indexes.
For example, you can see the minimum and maximum record sizes, as well as the index depth (the number of
levels in the B-tree) and how many records are on each level. A lot of this information will be less useful for basic
fragmentation analysis, which is why I chose to limit the number of columns in the samples as well as use the
Sampled scan mode.
Analyzing the Fragmentation of a Small Table
Don't be overly concerned with the output of sys.dm_db_index_physical_stats for small tables. For a small table or
index with fewer than eight pages, SQL Server uses mixed extents for the pages. For example, if a table ( SmallTable1
or its clustered index) contains only two pages, then SQL Server allocates the two pages from a mixed extent instead of
dedicating an extent to the table. The mixed extent may contain pages of other small tables/indexes also, as shown in
Figure 13-13 .
Figure 13-13. Mixed extent
The distribution of pages across multiple mixed extents may lead you to believe that there is a high amount of
external fragmentation in the table or the index, when in fact this is by design in SQL Server and is therefore perfectly
acceptable.
To understand how the fragmentation information of a small table or index may look, create a small table with a
clustered index.
IF (SELECT OBJECT_ID('dbo.Test1')
) IS NOT NULL
DROP TABLE dbo.Test1;
GO
CREATE TABLE dbo.Test1
(C1 INT,
C2 INT,
C3 INT,
C4 CHAR(2000)
);