Index related DMVs and DMFs - sys.dm_db_index_usage_stats

What does user_lookups gives us?

You might already have noticed that this column for the clustered index gets updated whenever we execute query 6 and query 7. This is because SQL Server needs to perform a Key Lookup operation in order to get the columns’ values that are not available in the index. If this happens for a table with many columns and many records the Key Lookup operation may hinder the performance. You may measure it by looking at IO values too. If you see a growth in user_lookups of a clustered index relatively to either seek or scan operation of the non-clustered index, and if you believe that IO operations that are involved with related queries are high, you may need to consider modifying the index. For example, if you add the SomeValue2 column as included column to the index, you can avoid Key Lookup operation because it makes the index as covered index and it has all values for giving out data to the above queries.

-- query 8
drop index ix_TestTable_SomeDate on dbo.TestTable
go
create index ix_TestTable_SomeDate on dbo.TestTable(SomeDate)  include (SomeValue2)
go

Once you run query 8 and run either query 6 or query 7, you will notice that there are no lookup operations for both queries. Run the query 4 after running these and examine the output. The value of user_lookup has not increased.

What does user_updates give us?

This shows the number of updates performed by the user for the index. You probably have noticed that initially the values for both clustered and non-clustered columns are zero but after inserting records, the values of both rows become 1000. Try the below queries and see change of user_updates column.

-- query 9
-- this causes both clustered index and non-clustered index updates
update dbo.TestTable
set SomeValue2 = newid()
where SomeDate = '2026-12-27'

-- run query 4

-- query 10
-- this causes only clustered index update
update dbo.TestTable
set SomeValue1 = newid()
where SomeDate = '2026-12-27'

-- run query 4

-- query 11
-- this causes both clustered index and non-clustered index updates
delete dbo.TestTable
where SomeDate = '2026-12-27'

-- run query 4

Query 9 and query 11 update both indexes but query 10 updates only the clustered index. Note that the non-clustered index gets updated when query 9 is executed because SomeValue2 has been added to the index as an included column. Again it gets updated with query 10 because the query updates the keys of the index.

Again this column can help us understand the usability of the index. Sometime we get misguided by either user_seeks or user_scans columns values because they get increased by the UPDATE and DELETE statements. If we have created the index for querying, we expect the increase of user_seeks (or user_scans) and if one of counters has been increased because of either UPDATE or DELETE, it may lead us to think as the index is being used for queries. Because of this, we need to be careful when judging the index by looking at either user_seeks or user_scans; consider the value of user_updates if the value is relatively high too.