Index related DMVs and DMFs - sys.dm_db_index_usage_stats

Index related DMVs and DMFs - sys.dm_db_index_usage_stats

By : Dinesh Priyankara Oct 09, 2007

Examining statistics of indexes is useful for optimizing the performance of queries. Statistics help us determine the usage and worth of indexes. There are many different methods to determine their usability; , which gives core statistics of indexes. The first article in the index-related DMVs and DMFs series discussed the output of sys.dm_db_index_physical_stats, while this, the second of the series, will explore some output columns of the sys.dm_db_index_usage_stats DMV.

This view outputs the counts of seek, scan, lookup and update operations performed by both users and the system. In addition, it also returns the last time each operation was performed. Let’s start examining them. As usual, we will create a table with an index and insert some records.

-- query 1
if object_id(N'dbo.TestTable', N'U') is not null
begin
    drop table dbo.TestTable
end
go
create table dbo.TestTable
(
    Id int identity(1,1) primary key,
    SomeValue1 varchar(50) not null,
    SomeValue2 varchar(50) not null,
    SomeDate datetime not null
)
go
create index ix_TestTable_SomeDate on dbo.TestTable(SomeDate)
go

Before inserting records to the table, let’s query the view and see.

-- query 2
select * from sys.dm_db_index_usage_stats
where database_id = db_id() and object_id = object_id(N'dbo.TestTable', N'U')

The result contains no records. This is because SQL Server starts collecting statistics when the index is first accessed. Simply execute below query and see.

-- query 3
select * from dbo.TestTable

Since no records have been inserted in the table, it returns nothing. But if you execute query 2 again, you will see one record. Let’s make the output more meaningful - change the query like below by joining the sys.indexes catalog view.

-- query 4
select i.name, s.*
from sys.dm_db_index_usage_stats s
       inner join sys.indexes i
              on i.object_id = s.object_id and i.index_id = s.index_id
where s.database_id = db_id() and i.object_id = object_id(N'dbo.TestTable', N'U')
order by s.index_id

Now examine the output record - it represents the clustered index of the table. Note the user_scan column. The value of it is “1” which indicates that index has been scanned by the user. The last_user_scan¬ shows the last scan operation time performed by the user. Query 3 caused SQL Server to start collecting statistics of the index because it accessed the index. SQL Server starts by initializing all counters to zero and increments the proper counter by one base on the operation performed by either user or system, in this case “scan”. Note that the counters are set back to empty if the service is restarted. This can happen when the database is shut down (When the AUTO_CLOSE is ON) too. Let’s insert records to the table now.

-- query 5
declare @count int
set @count = 0
while (@count < 10000)
begin
       insert into dbo.TestTable
              (SomeValue1, SomeValue2, SomeDate)
       values
              (newid(), newid(), convert(varchar(12), dateadd(d, @count, getdate())))
       set @count = @count + 1
end

Execute query 4 again and check the result. Here is mine:

Name	user_scans	user_updates	last_user_scan	last_user_update
PK__TestTable__619B8048	1	1000	2007-09-25 09:52:13.130	2007-09-25 09:57:44.663
ix_TestTable_SomeDate	0	1000	NULL	2007-09-25 09:57:44.663

Now the result-set contains two records, one for the clustered index and another for the non-clustered index. The values of user_updates for both indexes are 1000 since we inserted 1000 records.

What does user_scans give us?

By looking at this column, we can see the number of times that index has been scanned. When a request comes to the SQL Server, the way of accessing the resources is decided by the relational engine. If the engine decides to scan the index, the index will be scanned by the storage engine. One way to assess the added index is by looking at this column. Assume that the ix_TestTableSomeDate is specifically created for below query.

-- query 6
select Id, SomeValue2 from dbo.TestTable where year(SomeDate) = 2007

If you execute the above query and check the execution plan, you will see that the index has been used for the plan. You can run query 4 and check the user_scans column too. You will see that it has been increased by one. Assume that a certain period of time has gone by; now by running query 4, you notice that user_scans for the clustered index has gone up but not for the non-clustered index, which indicates that the non-clustered index is not being used for the query. Of course, if you run the query for the year 2008, you will see that the index is not used but the clustered index is scanned. This is because the relational engine decides that a clustered index scan is better than a non-clustered index scan for the year 2008. If this happens; the period of time, having an index on SomeDate column is useless and maintenance of it is an overhead, hence you can remove it. Note that you may need to examine other queries whether it is beneficial for them before removing.

What does user_seeks gives us?

This is just like the user_scans column but it is increased by one when the index is used for a seek operation. The relational engine decides to do a seek operation for a query like below;

-- query 7
select id, SomeValue2 from dbo.TestTable where SomeDate = '2026-12-27'

If you execute the above query, and execute query 4 again, you will see a value of “1” for user_seeks column for non-clustered index. This column gives clear indication whether the index is used properly too.

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.

Finally what else sys.dm_db_index_usage_stats gives us?

Though we can drill down the usage of a particular index by examining individual columns, we can get the overall picture of usage of all indexes. One important view is indexes that are not used. The below query gives will highlight this:

-- query 12
select object_name(i.object_id) as tablename, i.name as indexname
from sys.indexes i
       left outer join sys.dm_db_index_usage_stats s
              on s.object_id = i.object_id
                     and s.index_id = i.index_id
                     and s.database_id = db_id()
where objectproperty(i.object_id, 'IsUserTable') = 1
       and i.index_id > 0 -- 0 indicates the heap       
and s.object_id is null

This returns all the indexes that have not been used for any requests by users or the system from the time SQL Server is started. If the service has been running for months without any interruptions and the usage of the indexes returned by the query is very low we can either remove them from the system or modify them after analyzing them. One thing needs to be made checked before removing - there may be some indexes that have been created for read-only tables (so, no updates) and they are used by some scheduled (eg. quarterly) jobs. If one of the indexes returned from above query is used by such a job, removing the index from the system will be badly affected to the job. Therefore check the purpose of the indexes before removing them.

This query returns the list of indexes ordered by the usage. Indexes that are listed at the top of the result-set may not be as beneficial as indexes listed at the bottom.

-- query 13
select object_name(i.object_id) as tablename, i.name as indexname,
       s.user_seeks + s.user_scans + s.user_lookups + s.user_updates as usage
from sys.indexes i
       inner join sys.dm_db_index_usage_stats s
              on s.object_id = i.object_id
                     and s.index_id = i.index_id
                     and s.database_id = db_id()
where objectproperty(i.object_id, 'IsUserTable') = 1
       and i.index_id > 0
order by usage

There is a possibility of being misguided by this query too. We saw that after inserting records into the table, the value of the user_updates became 1000 but there were no values for other columns. That type of indexes may be listed at the bottom of the output, hence it leads us to assume the index is heavily used. Because of that, it would be better to analyze the bottom part of result-set separately by examining individual columns such as user_scans and user_seeks.