X hits on this document

92 views

0 shares

0 downloads

0 comments

34 / 48

668

CHAPTER 28 PERFORMANCE TUNING

  • 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_ID function. 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

T h e s y s . i n d e x e s s y s t e m c a t a l o g v i e w w a s j o i n e d t o t h e s y s . d m _ d b _ i n d e x _ p h y s i c a l _ s t a t s f u n c - t i o n b a s e d o n t h e o b j e c t _ i d a n d i n d e x _ i d .

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.

Displaying Index Usage

Similar to query performance tuning, creating useful indexes in your database is often part art and part science. Indexes can slow down data modifications while at the same time speeding up SELECT queries. You must balance the cost/benefit of index overhead with read activity versus data modifi- cation activity. Every additional index added to a table may improve query performance at the expense of data modification speed. On top of this, index effectiveness changes as the data changes, so an index that was useful a few weeks ago may no longer be useful today. One great difficulty in SQL Server 2000 was figuring out which indexes were not being used—either never or rarely used at all. This is important because adding indexes slows down updates. If you’re going to have indexes on a table, they should be put to good use on high priority queries. If an index is unused by SQL Server, it’s just dead weight.

Now in SQL Server 2005, you can see whether or not an index is being used by querying the sys.dm_db_index_usage_stats dynamic management view. This view returns statistics on the num- ber of index seeks, scans, updates, or lookups since the SQL Server instance was last restarted. It also returns the last dates the index was referenced.

In this example, the sys.dm_db_index_usage_stats dynamic management view is queried to see if the indexes on the Sales.Customer table are being used. Prior to referencing sys.dm_db_index_usage_stats, two queries will be executed against the Sales.Customer table, one returning all rows and columns and the second returning the AccountNumber column for a specific TerritoryID:

SELECT * FROM Sales.Customer

SELECT AccountNumber FROM Sales.Customer WHERE TerritoryID = 4

A f t e r e x e c u t i n g t h e q u e r i e s , t h e s y s . d m _ d b _ i n d e x _ u s a g e _ s t a t s d y n a m i c m a n a g e m e n t v i e queried: w i s

Document info
Document views92
Page views92
Page last viewedSat Dec 03 00:33:15 UTC 2016
Pages48
Paragraphs987
Words15822

Comments