This post is part of an ongoing SQL Scripts series, in which I list useful queries that I use in my day to day database administration.
If you are interested in code that will Persist And Aggregate Index Statistics for your server, take a look at my SQL Server Metrics Pack project that is available on GitHub.
Why Should I Check Index Usage?
Checking index usage statistics is a great way to find out how often indexes are being used, and identify if you have indexes that are not being used at all. Unused indexes are unnecessary overhead, and need to be cleaned up whenever possible. By regularly checking your index usage statistics, you can determine which ones are used heavily, and which ones you need to get rid of.
How Do I Find Usage Information For Indexes?
SQL Server actually keeps up with how often an index is used. It does this behind the scenes, and you can find this information by querying the built in sys.indexes and sys.dm_db_index_usage_stats DMVs. Below is a query that I use regularly to check the usage of indexes in my databases.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
SELECT DB_NAME() AS DatabaseName ,SCHEMA_NAME(s.schema_id) +'.'+OBJECT_NAME(i.OBJECT_ID) AS TableName ,i.name AS IndexName ,ius.user_seeks AS Seeks ,ius.user_scans AS Scans ,ius.user_lookups AS Lookups ,ius.user_updates AS Updates ,CASE WHEN ps.usedpages > ps.pages THEN (ps.usedpages - ps.pages) ELSE 0 END * 8 / 1024 AS IndexSizeMB ,ius.last_user_seek AS LastSeek ,ius.last_user_scan AS LastScan ,ius.last_user_lookup AS LastLookup ,ius.last_user_update AS LastUpdate FROM sys.indexes i INNER JOIN sys.dm_db_index_usage_stats ius ON ius.index_id = i.index_id AND ius.OBJECT_ID = i.OBJECT_ID INNER JOIN (SELECT sch.name, sch.schema_id, o.OBJECT_ID, o.create_date FROM sys.schemas sch INNER JOIN sys.objects o ON o.schema_id = sch.schema_id) s ON s.OBJECT_ID = i.OBJECT_ID LEFT JOIN (SELECT OBJECT_ID, index_id, SUM(used_page_count) AS usedpages, SUM(CASE WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count) ELSE lob_used_page_count + row_overflow_used_page_count END) AS pages FROM sys.dm_db_partition_stats GROUP BY object_id, index_id) AS ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id WHERE OBJECTPROPERTY(i.OBJECT_ID,'IsUserTable') = 1 --optional parameters AND ius.database_id = DB_ID() --only check indexes in current database AND i.type_desc = 'nonclustered' --only check nonclustered indexes AND i.is_primary_key = 0 --do not check primary keys AND i.is_unique_constraint = 0 --do not check unique constraints --AND (ius.user_seeks+ius.user_scans+ius.user_lookups) < 1 --only return unused indexes --AND OBJECT_NAME(i.OBJECT_ID) = 'tableName'--only check indexes on specified table --AND i.name = 'IX_Your_Index_Name' --only check a specified index order by i.name |
You will note that in the WHERE clause I have specified several optional parameters, some of which are commented out. These parameters will allow you to tailor this script to your needs, checking for as few or as many indexes as you want.
What Should I Look For In These Index Usage Statistics?
First, it’s important to note that the usage statistics you see are only aggregated since the last SQL Server restart. Every time SQL Server is restarted, these statistics are cleared out and reset to zero! If you want a way to persist index statistics across server restarts see this post.
By looking at the seeks, scans, and lookups returned by the above query, we can easily see how often an index is used. If all of these are zero, it means than the specified index has not had any seeks, scans, or lookups since the last SQL Server restart (or, since the index was created, if it is a recent addition). The lastseek, lastscan, and lastlookup columns give you the date and time of when that specific operation occurred, or will be NULL if that operation has not occurred yet.
The updates column will show you how many data modifications have occurred in the specified index. Remember, every time you INSERT, UPDATE, or DELETE data in a table, all of the associated indexes on that table must have their data modified as well. (ex. inserting a record into a table also inserts a record into all matching indexes on that table). This is why it is so important to get rid of any unused indexes, because SQL Server has to maintain the data in them regardless of whether they are used or not. Indexes with a large number of updates, but no seeks, scans, and lookups are good candidates for removal.
Another good use for these index usage statistics is to see how well SQL Server likes any new indexes that you deploy. Every time I add an index, I keep check on these stats for the first 30 days or so, to determine if it is getting used like I want it to. Sometimes you know almost immediately (Wow! 1 million seeks in an hour!), or in other cases (It has been a month, why isn’t SQL Server using this index???)
And this is just the start! There are several more ways you can use this information. For a much more detailed tutorial, I encourage you to check out Red Gate’s Tune Your Indexing Strategy with SQL Server DMVs article.
Terrific post. Incredibly useful. The first line is a bit misleading if you are reviewing across all databases, I think better would be: DB_NAME(ius.database_id) AS DatabaseName
Thanks Jeff!