Index Fragmentation - SQL Server Query Performance Tuning - page 247

DECLARE @n INT = 1;

WHILE @n <= 28
BEGIN
INSERT INTO dbo.Test1
VALUES (@n, @n, @n, 'a');
SET @n = @n + 1;
END
CREATE CLUSTERED INDEX FirstIndex ON dbo.Test1(C1);

In the preceding table, with each INT taking 4 bytes, the average row size is 2,012 (=4 + 4 + 4 + 2,000) bytes.
Therefore, a default 8KB page can contain up to four rows. After all 28 rows are added to the table, a clustered index
is created to physically arrange the rows and reduce fragmentation to a minimum. With the minimum internal
fragmentation, seven (=28 / 4) pages are required for the clustered index (or the base table). Since the number of pages
is not more than eight, SQL Server uses pages from mixed extents for the clustered index (or the base table). If the
mixed extents used for the clustered index are not side by side, then the output of sys.dm_db_index_physical_stats
may express a high amount of external fragmentation. But as a SQL user, you can't reduce the resultant external
fragmentation. Figure 13-14 shows the output of sys.dm_db_index_physical_stats .

Figure 13-14. Fragmentation of a small clustered index

From the output of sys.dm_db_index_physical_stats , you can analyze the fragmentation of the small clustered
index (or the table) as follows:

•

avg_fragmentation_in_percent : Although this index may cross to multiple extents, the
fragmentation shown here is not an indication of external fragmentation because this index is
being stored on mixed extents.

Avg_page_space_used_in_percent : This shows that all or most of the data is stored well
within the seven pages displayed in the pagecount field. This eliminates the possibility of
logical fragmentation.

•

Fragment_count : This shows that the data is fragmented and stored on more than one extent,
but since it's less than eight pages long, SQL Server doesn't have much choice about where it
stores the data.

•

In spite of the preceding misleading values, a small table (or index) with fewer than eight pages is simply unlikely
to benefit from efforts to remove the fragmentation because it will be stored on mixed extents.
Once you determine that fragmentation in an index (or a table) needs to be dealt with, you need to decide which
defragmentation technique to use. The factors affecting this decision, and the different techniques, are explained in
the following section.