X hits on this document

89 views

0 shares

0 downloads

0 comments

35 / 48

CHAPTER 28 PERFORMANCE TUNING

S E L E C T i . n a m e I n d e x N a m e , u s e r _ s e e k s , u s e r _ s c a n s last_user_seek, last_user_scan FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON ,

  • s.

    object_id = i.object_id AND

  • s.

    index_id = i.index_id

WHERE database_id = DB_ID('AdventureWorks') AND s.object_id = OBJECT_ID('Sales.Customer')

This returns:

IndexName

IX PK

_ _

Customer Customer

_ _

TerritoryID CustomerID

1

0

2005-10-15 17:13:35.487

0

1

NULL

user seeks user scans last user seek

_

_

_

_

last_user_scan NULL 2005-10-15 17:13:34.237

How It Works

The sys.dm_db_index_usage_stats dynamic management view allows you to see what indexes are being used in your SQL Server instance. The statistics are valid since the last SQL Server restart.

In this recipe, two queries were executed against the Sales.Customer table. After executing the queries, the sys.dm_db_index_usage_stats dynamic management view was queried.

The SELECT clause displayed the name of the index, the number of user seeks and user scans, and the dates of the last user seeks and user scans:

SELECT i.name IndexName, user_seeks, user_scans, last_user_seek, last_user_scan

The FROM clause joined the sys.dm_db_index_usage_stats dynamic management view to the sys.indexes system catalog view (so the index name could be displayed in the results) on the object_id and index_id:

FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON

  • s.

    object_id = i.object_id AND

  • s.

    index_id = i.index_id

The WHERE clause qualified that only indexes for the AdventureWorks database be displayed, and of those indexes, only those for the Sales.Customer table. The DB_ID function was used to get the database system ID, and the OBJECT_ID function was used to get the table’s object ID:

WHERE database_id = DB_ID('AdventureWorks') AND s.object_id = OBJECT_ID('Sales.Customer')

The query returned two rows, showing that the PK_Customer_CustomerID clustered index of the Sales.Customer table had indeed been scanned recently (most likely by the first SELECT * query) and the IX_Customer_TerritoryID nonclustered index had been used in the second query (which qualified TerritoryID = 4).

Indexes assist with query performance, but also add disk space and data modification overhead. Using the sys.dm_db_index_usage_stats dynamic management view, you can monitor if indexes are actually being used, and if not, replace them with more effective indexes.

Using the Database Engine Tuning Advisor

If a performance issue is stumping you, or you’d like to check your current indexing strategy for possible improvements, use the Database Engine Tuning Advisor tool.

This recipe will demonstrate how to use the Database Engine Tuning Advisor to create recom- mendations for a specific query used in the AdventureWorks database:

669

Document info
Document views89
Page views89
Page last viewedFri Dec 02 18:41:14 UTC 2016
Pages48
Paragraphs987
Words15822

Comments