In SQL Server 2000, the DBCC SHOWCONTIG command was used to display index fragmentation. Fragmentation is the natural byproduct of data modifications to a table. When data is updated in the database, the logical order of indexes (based on the index key) gets out of sync with the actual physical order of the data pages. As data pages become further and further out of order, more I/O operations are required in order to return results requested by a query. Rebuilding or reorganizing an index allows you to defragment the index by synchronizing the logical index order, re-ordering the physical data pages to match the logical index order.
■Note
See Chapter 5 for a review of index management and Chapter 23 for a review of index defragmentation and reorganization.Now in SQL Server 2005, DBCC SHOWCONTIG has been deprecated in place of the new dynamic management function, sys.dm_db_index_physical_stats. The sys.dm_db_index_physical_stats dynamic management function returns information that allows you to determine an index’s level of fragmentation.
The syntax for sys.dm_db_index_physical_stats is as follows: sys.dm_db_index_physical_stats (
{ database_id | NULL } , { object_id | NULL } , { index_id | NULL | 0 } , { partition_number | NULL }
, { mode | NULL | DEFAULT } )
The arguments of this command are described in Table 28-5. Table 28-5. sys.dm_db_index_physical_stats Arguments
Argument Description
database_id | NULL The database ID of the indexes to evaluate. If NULL, all databases for the SQL Server instance are returned.
object_id | NULL The object ID of the table and views (indexed views) to evaluate. If NULL, all tables are returned.
index_id | NULL | 0 The specific index ID of the index to evaluate. If NULL, all indexes are returned for the table(s).
partition_number | NULL The specific partition number of the partition to evaluate. If NULL, all partitions are returned based on the defined
database/table/indexes selected.
LIMITED | SAMPLED | These modes impact how the fragmentation data is collected. The DETAILED | NULL | DEFAULT LIMITEDmode scans all pages for a heap and the pages above the
leaf-level. SAMPLED collects data based on a 1% sampling of pages in the heap or index. The DETAILED mode scans all pages (heap or index). DETAILED is the slowest, but most accurate option.
Designating NULL or DEFAULT is the equivalent of the LIMITED mode.
In this example, the sys.dm_db_index_physical_stats dynamic management view is queried for all objects in the AdventureWorks database with an average fragmentation percent greater than 30: USE AdventureWorks
GO
SELECT OBJECT_NAME(object_id) ObjectName, index_id,
index_type_desc,
avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats
(DB_ID('AdventureWorks'),NULL, NULL, NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 30
ORDER BY OBJECT_NAME(object_id)
This returns the following (abridged) results:
ObjectName index_id index_type_desc avg_fragmentation_in_percent
BillOfMaterials 2 NONCLUSTERED INDEX 33.3333333333333
CountryRegion 1 CLUSTERED INDEX 50
DatabaseLog 0 HEAP 54.1666666666667
Employee 1 CLUSTERED INDEX 57.1428571428571
Employee 2 NONCLUSTERED INDEX 66.6666666666667
...
This second example returns fragmentation for a specific database, table, and index: SELECT OBJECT_NAME(f.object_id) ObjectName,
i.name IndexName, f.index_type_desc,
f.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats
(DB_ID('AdventureWorks'),
OBJECT_ID('Production.ProductDescription'), 2,
NULL, 'LIMITED') f INNER JOIN sys.indexes i ON
i.object_id = f.object_id AND i.index_id = f.index_id
This returns:
ObjectName IndexName index_type_desc avg_fragmentation
in_percent ProductDescription AK_ProductDescription_rowguid NONCLUSTERED INDEX 66.6666666666667
How It Works
The first example started off by changing the database context to the AdventureWorks database: USE AdventureWorks
GO
Since the OBJECT_NAME function is database-context sensitive, changing the database context ensures that you are viewing the proper object name.
Next, the SELECT clause displays the object name, index ID, description, and average fragmen- tation percent:
SELECT OBJECT_NAME(object_id) ObjectName, index_id, index_type_desc,
avg_fragmentation_in_percent
The index_type_desc column tells you if the index is a heap, clustered index, nonclustered index, primary XML index, or secondary XML index.
Next, the FROM clause referenced the sys.dm_db_index_physical_stats catalog function. The parameters were put in parentheses, and include the database name and NULL for all other parame- ters except the scan mode:
FROM sys.dm_db_index_physical_stats
(DB_ID('AdventureWorks'),NULL, NULL, NULL, 'LIMITED')
Since sys.dm_db_index_physical_stats is referenced like a table (unlike 2000’s DBCC
SHOWCONTIG), the WHERE clause is used to qualify that only rows with a fragmentation percentage of 31% or greater be returned in the results:
WHERE avg_fragmentation_in_percent > 30
The query returned several rows for objects in the AdventureWorks database with a fragmentation greater than 30%. The avg_fragmentation_in_percent column shows logical fragmentation of nonclus- tered or clustered indexes, returning the percentage of disordered pages at the leaf level of the index. For heaps, avg_fragmentation_in_percent shows extent level fragmentation. Regarding extents, recall that SQL Server reads and writes data at the page level. Pages are stored in blocks called extents, which consist of eight contiguous 8KB pages. Using the avg_fragmentation_in_percent, you can determine if the specific indexes need to be rebuilt or reorganized using ALTER INDEX.
In the second example, fragmentation was displayed for a specific database, table, and index. The SELECT clause included a reference to the index name (instead of index number):
SELECT OBJECT_NAME(f.object_id) ObjectName, i.name IndexName,
f.index_type_desc,
f.avg_fragmentation_in_percent
The FROM clause included the specific table name, which was converted to an ID using the OBJECT_IDfunction. The third parameter included the index number of the index to be evaluated for fragmentation: FROM sys.dm_db_index_physical_stats (DB_ID('AdventureWorks'), OBJECT_ID('Production.ProductDescription'), 2, NULL, 'LIMITED') f
The sys.indexes system catalog view was joined to the sys.dm_db_index_physical_stats func- tion based on the object_id and index_id.
INNER JOIN sys.indexes i ON i.object_id = f.object_id AND i.index_id = f.index_id
The query returned the fragmentation results just for that specific index.