CHAPTER 28 ■ PERFORMANCE TUNING
2, NULL, 'LIMITED')
INNER JOIN sys.indexes i ON
object_id = f.object_id AND
index_id = f.index_id
_ _ AK ProductDescription rowguid
avg_fragmentation in_percent 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
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,