Index Selection - Inside the SQL Server Query Optimizer - page 128

non-clustered indexes, will also include heaps, listed as i.index_id equal to 0, and
clustered indexes, listed as i.index_id equal to 1. For the purposes of this section, you
may want to just focus on non-clustered indexes, which include i.index_id values 2 or
greater. Since heaps and clustered indexes contain the table's data, they may not even be
candidates for removal in the first place.
By inspecting the user_seeks , user_scans , and user_lookup values of your
non-clustered indexes you can see how your indexes are being used, and you can inspect
the user_updates values to see the amount of updates performed on the index. All of
this information will help to give you a sense as to how useful an index actually is. Bear in
mind that all I'll be demonstrating is how to call up information from this DMV, and what
sort of situations will trigger different updates to the information it returns. How you
react to the information it returns is a task I leave to you.
As an example, run the following code to create a new table with a non-clustered index:
SELECT * INTO dbo . SalesOrderDetail
FROM Sales . SalesOrderDetail
CREATE NONCLUSTERED INDEX IX_ProductID ON dbo . SalesOrderDetail ( ProductID )
Listing 4-26.
If you want to keep track of the values for this example follow these steps carefully, as
every query execution may change the index usage statistics. When you run the following
query, it will initially contain only one record, which was created because of table access
performed when the index on Listing 4-26 was created.
SELECT DB_NAME ( database_id ) as database_name ,
OBJECT_NAME ( s . object_id ) as object_name , i . name , s .*
FROM sys . dm_db_index_usage_stats s join sys . indexes i
ON s . object_id = i . object_id AND s . i.index_id = i . i.index_id
and s . object_id = object_id ( 'dbo.SalesOrderDetail' )
Listing 4-27.