X hits on this document

91 views

0 shares

0 downloads

0 comments

33 / 48

CHAPTER 28 PERFORMANCE TUNING

(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

ProductDescription

_ _ AK ProductDescription rowguid

index_type_desc

NONCLUSTERED INDEX

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

,

'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,

667

Document info
Document views91
Page views91
Page last viewedFri Dec 02 23:00:07 UTC 2016
Pages48
Paragraphs987
Words15822

Comments