Issue with sys.dm_db_index_usage_stats in SQL Server 2012

I’d be struggling to remember a week where I hadn’t run a query against the dmv sys.dm_db_index_usage_stats to find unused or under used indexes (or even heaps). It would have to be in the top shelf of many DBA’s toolboxes. So I was alarmed when I came across this post from Joseph Sack over at SQL Skills.

The gist of this is if you rebuild an index in SQL Server 2012 then that indexes’ stats are removed from the DMV! It doesn’t seem to be the case with reorganize, just with rebuilds. So the take away is to treat results from sys.dm_db_usage_stats with suspicion, be aware of when you last ran a rebuild against the index. The best advice I’ve seen is to snapshot the DMV before running index maintenance.

I wanted to see this for myself, and add my 2 cents worth so I ran the following tests.

Create a table:

if exists(select 1 from sys.objects where name = 'table' 
          and type = 'U')
	drop table [table];
go

create table [table]
  (
  [id] int identity(1,1),
  [value] varchar(20) 
  default convert(varchar(20),datepart(millisecond,getdate()))
  );
go

Add a clustered and a non clustered index:

create clustered index ix_cls on [table](id);
go

create nonclustered index ix_noncls on [table](value);
go

Populate it with some fake data:

insert [table]([value])
	values(default);
go 1000

Run a couple of query’s that use the indexes:

select * from [table] where [id] = 498;
select * from [table] where [value] = 498;

Because of the way values are populated you might need to tinker with the where clause of the second select to hit the non-clustered index, or rerun the drop and create table script until you get both indexes being used.

Check usage stats:

select 
	OBJECT_NAME(us.[object_id]) as [Table],
	i.name as [Index],
	user_seeks,
	user_scans,
	last_user_seek,
	last_user_scan
from sys.dm_db_index_usage_stats us
inner join sys.indexes i
	on us.index_id = i.index_id 
        and us.[object_id] = i.[object_id]
where database_id = db_id()
and OBJECT_NAME(us.[object_id]) = 'table'

Reorganize the indexes:

alter index ix_cls on [table] reorganize
alter index ix_noncls on [table] reorganize

Check usage stats:

select 
	OBJECT_NAME(us.[object_id]) as [Table],
	i.name as [Index],
	user_seeks,
	user_scans,
	last_user_seek,
	last_user_scan
from sys.dm_db_index_usage_stats us
inner join sys.indexes i
	on us.index_id = i.index_id 
        and us.[object_id] = i.[object_id]
where database_id = db_id()
and OBJECT_NAME(us.[object_id]) = 'table'

Rebuild the indexes:

alter index ix_cls on [table] rebuild
alter index ix_noncls on [table] rebuild

Check the stats:

select 
	OBJECT_NAME(us.[object_id]) as [Table],
	i.name as [Index],
	user_seeks,
	user_scans,
	last_user_seek,
	last_user_scan
from sys.dm_db_index_usage_stats us
inner join sys.indexes i
	on us.index_id = i.index_id 
        and us.[object_id] = i.[object_id]
where database_id = db_id()
and OBJECT_NAME(us.[object_id]) = 'table'

Results: I ran the above tests against the SQL Server Builds that I had available with the following results:

Build Reorganize Rebuild
9.0.5000 Usage stats persist. Usage stats persist.
10.0.5500 Usage stats persist. Usage stats persist.
10.50.2500 Usage stats persist. Usage stats persist.
11.0.3000 Usage stats persist. Usage stats cleared.
11.0.3401 Usage stats persist. Usage stats cleared.

It seems that this bug/feature has been introduced in SQL Server 2012 (as of the time of writing 11.0.3401 is the latest build of SQL Server 2012, SP1, CU8). My recommendation is that you keep this in mind when reviewing index usage.

About these ads
This entry was posted in SQL Server. Bookmark the permalink.

5 Responses to Issue with sys.dm_db_index_usage_stats in SQL Server 2012

  1. Pingback: (SFTW) SQL Server Links 28/02/14 - SQL Server - SQL Server - Toad World

  2. S.Kusen says:

    Did you submit this as a bug on Microsoft Connect?

  3. Pingback: (SFTW) SQL Server Links 28/02/14 • John Sansom

  4. perry Whittle says:

    Since a reorg doesn’t touch stats it makes sense that usage would persist. An index rebuild will automatically rebuild stats, guess that’s the logic here. Either way, MS will probably tell you its by design.
    The new identity feature has caused some confusion too, seems they made a lot of “fixes” and “enhancements” that no one actually wants.
    They obviously not aware of the term “if it ain’t broke don’t fix it!
    Regards Perry

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s