MORE ABOUT ME

Rebuilding Indexes Will Reset Index Usage Statistics in SQL Server 2012

reset

03 Jan Rebuilding Indexes Will Reset Index Usage Statistics in SQL Server 2012

resetWe 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 | @sqlagentmanvia 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:

usage_stats

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:

usage_statsI 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:

usage_stats

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

  • http://www.patrickkeisler.com/ Patrick Keisler

    It looks like this functionality must have changed in SQL Server 2012. In SQL Server 2008 and 2008 R2, an index rebuild did not reset the metrics captured in sys.dm_db_index_usage_stats.

    • ThomasLaRock

      I didn’t even think to check that, I just assumed that it worked the same in all versions. I just did a quick test on a 2008 R2 instance and indeed the usage stats are not removed.

      Very, very interesting!

      • http://www.patrickkeisler.com/ Patrick Keisler

        I just checked SQL 2005, and the metrics are kept there as well, so it has definitely changed for SQL 2012.

        • ThomasLaRock

          OK, the plot thickens. Let me see if I can dig up any extra info on this behavior.

  • http://twitter.com/sqlagentman Tim Ford

    I was surprised by this and I routinely present on the subject of DMVs (thanks for using my script by the way. I hope you blew the dust off of it first.) I do believe I smell a Connect item forthcoming if one is not already out there on this. Hmmmm? You want to check or do you want me to?

  • Feodor Georgiev

    Thomas, there is another quite big problem with the index statistics DMVs: have you ever wondered what happens when the datatype limits are reached for the columns of the DMVs?
    Keep in mind that the DMVs have a specific datatype for each column, and I have personally seen a index usage statistics DMV containing negative numbers.
    Actually, when the limit of the datatype is reached, it reverts to the largest negative number and it starts going towards the 0 and towards the positive numbers from there, as you use the indexes.
    Funny, eh?

    • ThomasLaRock

      No, I had not seen that for the index usage DMVs, but I have seen that behavior in other areas for SQL Server. IIRC, the GUI used to show a negative number for data file size in SQL 2005 once in a while. I haven’t seen it in years though.

      • Feodor Georgiev

        It only makes sense – the datatypes for the user_scans column from sys.dm_db_index_usage_stats is bigint, so as soon as the limit is reached, it will have to go back from the start.
        Whether it is to the boundry of the negative numbers or to 0, the results of the DMVs will be skewed.
        In other words, your post has a very good point – we can’t trust the DMVs over a very log periods of time.

        • ThomasLaRock

          Oh yes, it makes perfect sense as to why it happens.

  • Marios Philippopoulos

    This is terrible news, we have to get Microsoft to fix this for SQL 2012.

    I collect index stats every 12 hrs but I only archive this info after instance restarts! Looks like I should start archiving it after index rebuilds as well.

    Thank you for this post,
    Marios Philippopoulos

    • ThomasLaRock

      Happy to help!

  • Roni Vered Adar

    We’re working on 2008, and I was just about to write a comment that rebuilding doesn’t reset the index usage statistics, until I’ve read the other comments :)

    It’s a very important behavior change in 2012.