CHAPTER 28 ■ PERFORMANCE TUNING
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')
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
object_id = f.object_id AND
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