3. Identifying index fragmentation
Indexes, like any other storage object, become
fragmented over time through the course of normal insert, delete, and
update activity. Identifying the level of fragmentation is a crucial
component of a targeted maintenance plan.In this section, our focus is on identifying
fragmentation levels, achieved using the sys.dm_db_index_physical_stats dynamic management function.
One of the columns returned from this DMF is
avg_fragmentation_in_percent. This indicates the percentage of pages
that are out of order, meaning the logical and physical ordering of
index pages no longer match, incurring additional disk activity when
scanning/seeking.
The code displayed in listing 3 uses the sys.dm_db_index_physical_stats function to return index fragmentation levels for all tables and indexes in the AdventureWorks database.
Example 3. Displaying index fragmentation
-- View index fragmentation ordered by fragmentation level
SELECT stats.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(
DB_ID(N'AdventureWorks'), NULL, NULL, NULL, NULL
) as stats
INNER JOIN sys.indexes AS b
ON stats.object_id = b.object_id AND stats.index_id = b.index_id
ORDER BY avg_fragmentation_in_percent DESC
|
The output of this command, as shown in figure 8,
is one row for each index in the database, with the
avg_fragmentation_in_percent column ordered descending to list the most
fragmented indexes first. This list will include clustered indexes,
which indicates the level of fragmentation of the table itself.
In previous versions of SQL Server, we used the DBCC SHOWCONTIG
command to retrieve, among other things, the logical scan fragmentation
value for a given index. While this command is still available, the sys.dm_db_index_physical_stats function is the preferred method. One of the limitations with DBCC SHOWCONTIG is its lack of accuracy when analyzing storage spanning multiple files. In contrast, the sys.dm_db_index_physical_stats function has no such limitations and is therefore the recommended method for index fragmentation analysis.
|
A common database maintenance technique involves an automated script that uses the sys.dm_db_index_physical_stats function to analyze index fragmentation levels and perform the appropriate action, for example, REORGANIZE if fragmentation between 5 percent and 30 percent and REBUILD
if greater than 30 percent.
|
Having covered a number
of important index-analysis techniques, let's move on now to the actual
index-maintenance tasks required to keep our databases performing well.
