Index related DMVs and DMFs - sys.dm_db_index_usage_stats

By : Dinesh Priyankara Oct 09, 2007

Printer friendly

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:

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.