Rebuilding Indexes Will Reset Index Usage Statistics in SQL Server 2012

We all know that statistics get updated when you rebuild your indexes, right?

OK, for those of you that are not aware of such things here is a blog post from Benjamin Nevarez (blog | @BenjaminNevarez) on the topic. He does a great job of explaining the subtle differences involved with various index and statistics maintenance methods.

However there is an additional consequence to this maintenance activity, something I rarely see or hear discussed. I was prompted to have this discussion as a result of a question from an attendee during one of my talks last month at SQL Server Live: What happens to the usage statistics for indexes after they are rebuilt?

The TL;DR answer is this: they get reset.

Using AdventureWorks2012 here’s my example. First, let’s run this query (courtesy of Tim Ford (blog | @sqlagentman) via Simple-Talk and modified to only show details against AdventureWorks2012):

USE AdventureWorks2012;
GO

SELECT OBJECT_NAME(ddius.[object_id], ddius.database_id) AS [object_name] ,
 ddius.index_id ,
 ddius.user_seeks ,
 ddius.user_scans ,
 ddius.user_lookups ,
 ddius.user_seeks + ddius.user_scans + ddius.user_lookups
 AS user_reads ,
 ddius.user_updates AS user_writes ,
 ddius.last_user_scan ,
 ddius.last_user_update
 FROM sys.dm_db_index_usage_stats ddius
 WHERE ddius.database_id > 4 -- filter out system tables
 AND OBJECTPROPERTY(ddius.OBJECT_ID, 'IsUserTable') = 1
 AND ddius.index_id > 0 -- filter out heaps
 AND DB_NAME(ddius.database_id) = 'AdventureWorks2012'
 ORDER BY ddius.user_scans DESC

Running that query after a service restart I get three rows returned:

I find this to be an odd result set, considering that the service has just been restarted. But let’s save that for discussion later and press on with the task at hand.

Next I will need to run a query against AdventurwWorks2012 in order to use an index and thus get some details inserted into this DMV. This query will do nicely:

SET NOCOUNT ON

SELECT ProductID ,
 Name,
 ProductNumber ,
 Color,
 Size ,
 SizeUnitMeasureCode ,
 WeightUnitMeasureCode,
 Weight,
 DaysToManufacture ,
 ProductLine ,
 Class,
 Style
 FROM Production.Product
 WHERE ProductID = 319

 GO 100

Now if I go back and look at my index usage I will see four rows returned:

I can see there are four rows and one of the rows is for the Product table. Now I will rebuild that index and we can see what happens to the index usage statistics. The following code will rebuild the index for us:

USE AdventureWorks2012;
GO
ALTER INDEX PK_Product_ProductID ON Production.Product
REBUILD;
GO

And when I go looking for index usage info I find three rows are returned same as before:

It is worth noting that doing a REORGANIZE of the index does not have any affect on the index usage statistics, because the REORGANIZE does not update any statistics (just as Benjamin stated in his post). It is also worth noting that the MSDN article for sys.dm_db_index_usage_stats does not mention that the DMV is reset when statistics are updated. Here is what is listed in the remarks section:

“The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started. In addition, whenever a database is detached or is shut down (for example, because AUTO_CLOSE is set to ON), all rows associated with the database are removed.”

If you recall I had three rows returned when we started our experiment earlier. According to the MSDN article I should not have any returned when I first start an instance. So why are they there?

I can use the following query to find statements that have hit my instance since the last restart:

SELECT creation_time, last_execution_time, st.text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st

In the twentysomething rows returned I can quickly find one of interest, it is this statement:

(@p1 timestamp, @p2 timestamp)select top 1000 column fulltextkey as k, column fulltextall, dst.docid , dst.ts  from [Production].[ProductReview] t WITH (READPAST), [AdventureWorks2012].[sys].[fulltext_index_docidstatus_610101214] dst WITH (READPAST) where column fulltextkey = dst.docid and dst.status = 0 and dst.ts > @p1 and dst.ts <= @p2 order by dst.ts OPTION(MAXDOP 1)

Any ideas yet?

I started to suspect that perhaps AdventureWorks2012 has some tables with a full text index. A little more T-SQL and I can confirm this:

SELECT OBJECT_NAME(object_id)
FROM sys.fulltext_indexes
WHERE is_enabled = 1

The same three object names as what I saw in my original result set. If I disable those three fulltext indexes then restart the instance the original query will return zero rows. I think it is good to know that when looking at index usage statistics you are seeing data that includes the use of full text indexes.

Why I Am Bothering To Tell You This

Ask any SQL Server DBA the following question: "When is the data reset for a DMV?" The most likely answer will be "after the instance has been restarted". A handful of folks will even go so far as to say "when you manually reset them" using something like DBCC SQLPERF. I don't know too many people that would ever answer "when you rebuild indexes".

If you are currently capturing index usage details for your SQL Server instance then you should be aware that they get reset when indexes are rebuilt. If you are capturing usage statistics infrequently, say weekly or monthly, then it is likely that your data is not as accurate as you might think. I don't like the idea of people making decisions based upon bad or incomplete data, so that's why I'm sharing this with you today.

If you are capturing index usage statistics I would urge you to rethink the collection times. For example, it would be a good idea to collect them before any index maintenance jobs as opposed to just collecting them at some random time.

UPDATED: This is a known issue with SQL 2012. In previous versions of SQL Server, the index usage stats were NOT reset. You can see the Connect item filed by Joe Sack (blog | @josephsack) here: http://connect.microsoft.com/SQLServer/feedback/details/739566/rebuilding-an-index-clears-stats-from-sys-dm-db-index-usage-stats