Database Reference
In-Depth Information
allocation of pages is going to be much more efficient. If you have mixed extents, you have pages shared between
multiple objects, and you have fragmentation within those extents, accessing the information becomes even more
problematic. But there is no defragmenting done on mixed extents.
To analyze the fragmentation of an index, let's re-create the table with the fragmented data set used in the
“Fragmentation Overhead” section. You can obtain the fragmentation detail of the clustered index (Figure
13-11
) by
executing the query against the
sys.dm_db_index_physical_stats
dynamic view used earlier.
Figure 13-11.
Fragmented statistics
SELECT ddips.avg_fragmentation_in_percent,
ddips.fragment_count,
ddips.page_count,
ddips.avg_page_space_used_in_percent,
ddips.record_count,
ddips.avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks2012'),
OBJECT_ID(N'dbo.Test1'),NULL,
NULL,'Sampled') AS ddips;
The dynamic management function
sys.dm_db_index_physical_stats
scans the pages of an index to return
the data. You can control the level of the scan, which affects the speed and the accuracy of the scan. To quickly check
the fragmentation of an index, use the Limited option. You can obtain an increased accuracy with only a moderate
decrease in speed by using the Sample option, as in the previous example, which scans 1 percent of the pages. For the
most accuracy, use the Detailed scan, which hits all the pages in an index. Just understand that the Detailed scan can
have a major performance impact depending on the size of the table and index in question. If the index has fewer than
10,000 pages and you select the Sample mode, then the Detailed mode is used instead. This means that despite the
choice made in the earlier query, the Detailed scan mode was used. The default mode is Limited.
By defining the different parameters, you can get fragmentation information on different sets of data. By removing
the
OBJECTID
function in the earlier query and supplying a
NULL
value, the query would return information on all
indexes within the database. Don't get surprised by this and accidentally run a Detailed scan on all indexes. You can
also specify the index you want information on or even the partition with a partitioned index.
The output from
sys.dm_db_index_physical_stats
includes 21 different columns. I selected the basic set of
columns used to determine the fragmentation and size of an index. This output represents the following:
•
avg_fragmentation_in_percent
: This number represents the logical average fragmentation
for indexes and heaps as a percentage. If the table is a heap and the mode is Sampled, then
this value will be
NULL
. If average fragmentation is less than 10 to 20 percent and the table isn't
massive, fragmentation is unlikely to be an issue. If the index is between 20 and 40 percent,
fragmentation might be an issue, but it can generally be helped by defragmenting the index
through an index reorganization (more information on index reorganization and index
rebuild is available in the “Fragmentation Resolutions” section). Large-scale fragmentation,
usually greater than 40 percent, may require an index rebuild. Your system may have different
requirements than these general numbers.
•
fragment_count
: This number represents the number of fragments, or separated groups of
pages, that make up the index. It's a useful number to understand how the index is distributed,
especially when compared to the
pagecount
value.
fragmentcount
is
NULL
when the sampling
mode is Sampled. A large fragment count is an additional indication of storage fragmentation.
