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 ;
go
create 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 (id);
go
create nonclustered index ix_noncls on (value);
go

Populate it with some fake data:

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

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

select * from  where [id] = 498;
select * from  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  reorganize
alter index ix_noncls on  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  rebuild
alter index ix_noncls on  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:

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.