Here we’ll introduce the functionality of the sys.dm_db_index_usage_stats DMV, which you can use to learn about the operations performed by your indexes. It is especially helpful in discovering indexes that are not used by any query, or are only minimally used. Indexes that are not being used will provide no benefit to your databases, but will use valuable disk space, slow your update operations, and should be considered for removal.
The sys.dm_db_index_usage_stats DMV stores the number of seek, scan, lookup, and update operations performed by both user and system queries, including the last time each type of operation was performed, and its counters are reset when the SQL Server service starts. Keep in mind that this DMV, in addition to nonclustered indexes, will also include heaps, listed as index_id equal to 0, and clustered indexes, listed as index_id equal to 1. For the purposes of this section, you may want to just focus on nonclustered indexes, which include index_id values 2 or greater. Because 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 nonclustered indexes, you can see how your indexes are being used, and you can also look at 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.
As an example, run the following code to create a new table with a nonclustered index:
If you want to keep track of the values for this example, follow these steps carefully, because 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 IX_ProductID index was created:
However, the values that we will be inspecting in this exercise—user_seeks, user_scans, user_lookups, and user_updates—are all set to 0. Now run the following query, let’s say, three times:
SELECT * FROM dbo.SalesOrderDetail
This query is using a Table Scan operator, so, if you rerun our previous query using the sys.dm_db_index_usage_stats DMV, it will show the value 3 on the user_scans column. Note that the column index_id is 0, denoting a heap, and the name of the table is also listed (as a heap is just a table with no clustered index). Run the next query, which uses an Index Seek, twice. After the query is executed, a new record will be added for the nonclustered index, and the user_seeks counter will show a value of 2.
Now, run the following query four times, and it will use both Index Seek and RID Lookup operators. Because the user_seeks for the nonclustered index had a value of 2, it will be updated to 6, and the user_lookups value for the heap will be updated to 4.
Finally, run the following query once:
Note that the UPDATE statement is doing an Index Seek and a Table Update, so user_seek will be updated for the index, and user_updates will be updated once for both the nonclustered index and the heap. Here is the final output of our query using the sys.dm_db_index_usage_stats DMV (edited for space):
Finally, drop the table you just created:
DROP TABLE dbo.SalesOrderDetail