Index Fragmentation - Pro SQL Server Internals - page 113

A small degree of internal fragmentation is not necessarily bad. It reduces page splits during insert and update
operations when data is inserted into or updated from different parts of the index. Nonetheless, a large degree of
internal fragmentation wastes index space and reduces the performance of the system. Moreover, for indexes with
ever-increasing keys, for example on identity columns, internal fragmentation is not desirable because the data is
always inserted at the end of the index.
There is a data management function, sys.dm_db_index_physical_stats , which you can use to analyze
fragmentation in the system. The three most important columns from the result set are:
avg_page_space_used_in_percent shows the average percentage of the data storage space
used on the page. This value shows you the internal index fragmentation.
avg_fragmentation_in_percent provides you with information about external index
fragmentation. For tables with clustered indexes, it indicates the percent of out-of-order
pages when the next physical page allocated in the index is different from the page
referenced by the next-page pointer of the current page. For heap tables, it indicates the
percent of out-of-order extents, when extents are not residing continuously in data files.
fragment_count indicates how many continuous data fragments the index has. Every
fragment constitutes the group of extents adjacent to each other. Adjacent data increases the
chances that SQL Server will use sequential I/O and Read-Ahead while accessing the data .
Sys.dm_db_index_physical_stats can analyze data in three different modes: LIMITED , SAMPLED , and DETAILED ,
which you need to specify as a parameter of the function. In LIMITED mode, SQL Server uses non-leaf index pages to
analyze the data. It is the fastest mode, although it does not provide information about internal fragmentation.
In DETAILED mode, SQL Server scans the entire index. As you can guess, that mode provides most accurate
results, although it is the most I/O intensive method.
In SAMPLED mode, SQL Server returns statistics based on a one percent data sample from the table when it has
10,000 or more data pages. It reads every 100 th page from leaf-level during execution. For tables with less than 10,000
data pages, SQL Server scans the entire index using DETAILED mode instead.

■ Check out the topics Online article at: http://technet.microsoft.com/en-us/library/ms188917.aspx for
more details about sys.dm_db_index_physical_stats .

Note

Page split is not limited to single page allocation and data movement. Let's look at an example, and create the
table and populate it with some data, as shown in Listing 5-1.

Listing 5-1. Multiple page splits: Table creation

create table dbo.PageSplitDemo
(
ID int not null,
Data varchar(8000) null
);

create unique clustered index IDX_PageSplitDemo_ID
on dbo.PageSplitDemo(ID);

;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows