I recently wrote a post with a similar title, reviewing the dm_db_index_physical_stats so it seemed logical to pick a closely linked dmv for this post .

Once again, let’s start off by looking at this in it’s most basic form

The first thing you will notice is that this dmv doesn’t have any parameters – it is actually a view rather than a function so we will need to filter records out by using a series of predicates in the WHERE clause;

This dmv tracks all usage of each index in every database. It’s values are reset when the SQL Service is restarted and when a database is detached or set to offline – such as when the database option AUTO_CLOSE is set to ON. The first 3 columns are pretty self-explanatory, the database_id column can be resolved into the relevant database name using the DATABASE_NAME() function, the object_id can be resolved into a table name using the OBJECT_NAME() function and the index_id can be replaced by the index name once we join to the indexes table

The remaining columns, in the main, have the words user, system or last in them in one combination or another. So, where you see user you can understand that the column is a count of user processes that have accessed the index in some way or another, where you see system then you are seeing a count of internal processes accessing the index and where you see last then that is the date on which the access last took place.

Columns that have seek, scan or lookup in their names are counting times that a process has reached into the index to get data to satisfy a query. Where a column has update in the name, it is counting where a process has carried out a change to some data and the index has had to be updated. With that information in mind let’s take a look at the first 4 columns: [ddius].[user_seeks], [ddius].[user_scans], [ddius].[user_lookups] and [ddius].[user_updates]. These are providing us information on how many times a user process has accessed data in the index, seeks are the most efficient method to get information, followed by lookups and lastly scans. So ideally seeing high figures in the seeks and lookups column is desirable. Having said that, not all scans are bad news. Sometimes the most effective way to get information from a table is to scan from the lowest value needed through to the highest value needed. e.g. When getting all records from a product range where the Product IDs are consecutive the optimizer may calculate that it is more efficient to scan the range of values rather than seek many times to the individual rows.

If you see a row for an index where the updates are higher than the other operations then this should be a warning sign that the index is taking up valuable resources during updates but providing very little use to data extraction requests. Look to remove the index once you have confirmed that the values are not skewed by a server restart that has wiped out details of a high usage period. The index might be specifically there to support a monthly, quarterly or even annual data analysis and could cause huge problems if it is missing. What you could consider is to disable or drop the index other than for the period of time when the analysis is taking place. This way you are avoiding the conflict of updates versus selects.

To see when an index was last used then simply query the [ddius].[last_user_seek], [ddius].[last_user_scan], [ddius].[last_user_lookup] or [ddius].[last_user_update] columns. Seeing recent dates for all of these mean that the index is regularly in use, supporting queries that your system is executing, if the dates are very old then once again investigate to see if the index can be dropped or should be kept.

Disclaimer – Jonathan is a Friend of Red Gate and as such, whenever they are discussed, will have a generally positive disposition towards Red Gate tools. Other tools are often available and you should always try others before you come back and buy the Red Gate ones. All code in this blog is provided “as is” and no guarantee, warranty or accuracy is applicable or inferred, run the code on a test server and be sure to understand it before you run it on a server that means a lot to you or your manager.