What Resets sys.dm_db_index_usage_stats and Missing Index DMVs?

photo-1416138782774-a2149bc6d102Managing indexes got trickier with SQL Server 2012. SQL Server has tracked and reported statistics on how often indexes are used and requested since SQL Server 2005. As of SQL Server 2012, suddenly all that information started getting reset whenever anyone ran ALTER INDEX REBUILD.

Confusingly for users, this only happened with one specific command: REBUILD. ALTER INDEX REORGANIZE has never reset index usage stats or missing index requests.

In this post I’ll cover new changes in behavior in SQL Server 2016 RC0, encourage you to vote for Connect bug #2446044 to fix missing index requests from being reset by REBUILD, and close with a chart describing the behavior of different commands in different versions of  SQL Server.

The Bug with sys.dm_db_index_usage_stats : Fixed!

Joe Sack filed Connect bug #739566 back in April 2012 on this issue. That bug was closed and marked as “won’t fix” for a while, but was recently reactivated. (Yay!)

Testing SQL Server 2016 RC0 today, I see that the bug for index_usage_stats has been fixed in this release! When I generate scans, seeks, and updates against an index, running ALTER INDEX REBUILD no longer resets the information. I can still tell which indexes have been used and which have not since the database came online, just like we had  in SQL Server 2008 R2 and prior.

The Bug with Missing Index DMVs: Still There

Another problem was introduced in SQL Server 2012 that seems to have slipped by here. Running ALTER INDEX REBUILD against any index on a table clears out all missing index requests that have accrued for the table.

I still see this problem occurring in 2016 RC0. Here’s what it looks like.

First, I run a query that generates missing index requests against the SQLIndexWorkbook database a bunch of times:

SET NOCOUNT ON;
GO
USE SQLIndexWorkbook
GO

DECLARE @garbage INT
SELECT 
    @garbage = NameCount
FROM agg.FirstNameByYear
WHERE  
    FirstNameId = 210;
GO 974

I verify that this generated missing index requests using the following query:

SELECT 
    deets.statement as db_schema_table,
    missin.avg_total_user_cost as [avg_est_query_cost],
    missin.avg_user_impact as [est_%_improvement],
    missin.user_scans,
    missin.user_seeks,
    missin.unique_compiles,
    deets.equality_columns,
    deets.inequality_columns,
    deets.included_columns
FROM sys.dm_db_missing_index_group_stats as missin
JOIN sys.dm_db_missing_index_groups as groups on missin.group_handle=groups.index_group_handle
JOIN sys.dm_db_missing_index_details as deets on groups.index_handle=deets.index_handle;
GO

Sure enough, it did. Here’s a partial screenshot of the output:

missing_index_request

I run the following code to rebuild one index on the table. In this case it’s the clustered primary key:

ALTER INDEX pk_aggFirstNameByYear on agg.FirstNameByYear REBUILD;
GO

After this completes, I get zero results from the missing index query for this table. They have been cleared.

And that’s a big bummer. Having this half fixed is arguably even more confusing.

In SQL Server 2008R2 and prior, index requests were not cleared upon rebuild. That’s much more desirable, as you may well have nightly or weekly index maintenance that kicks in and selectively rebuilds indexes on some tables.

Vote for Connect Bug #2446044 to Let the Product Team Know You’d Like this to Change

I think I know why the issue with missing indexes wasn’t fixed. We forgot to file a bug. Many of us knew the bug on index usage stats had been filed and had been closed for some time, and none of us thought to open a separate bug about the missing index DMVs.

Oops.

I created Connect bug #2446044 for this problem. Please vote for this bug– it just takes a second to create an account if you don’t have one.

Quick Rundown: What Happens In Which Version with Which Commands

Here’s the behavior you should expect to see by the version of SQL Server:

SQL Server Version sys.dm_db_index_usage_stats Missing Index DMVs
SQL Server 2005 & SQL Server 2008R2 Reset on database offline/restart. Reset on any index drop/disable/create on that table. Reset on database offline/restart.
SQL Server 2012 Reset on ALTER INDEX REBUILD of that index until SP2+CU12 or SP3+CU3 . Reset on database offline/restart. Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.
SQL Server 2014 Reset on ALTER INDEX REBUILD of that index until SP2. Reset on database offline/restart. Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.
SQL Server 2016  Reset on database offline/restart. Reset on any index drop/disable/create on that table. Reset on database offline/restart. Reset on ALTER INDEX REBUILD of any index on the table.

Notes:

Don’t forget to vote!

Attribution: Bug photo by Tanguy Sauvin courtesy unsplash.com

Previous Post
SQL Code Basics: Reusable Event Logging Utility
Next Post
The Case of DATETIME2 and Partition Elimination

Related Posts

18 Comments. Leave new

[…] Kendra Little reports that rebuilding an index no longer clears out sys.dm_db_index_usage_stats: […]

Reply

Done with my vote, up to now we have 29 Votes

Reply

great, all the efforts done by many to persist that information in vane… thanks Microsoft for keeping us busy.

well, at least the persisted info will have an use when the server is restarted 🙂

Reply

[…] For more information, you could read this article by Kendra Little – here. […]

Reply

[…] For more information, you could read this article by Kendra Little – here. […]

Reply

[…] Use SQL Server’s “Missing index requests” to see where SQL Server is asking for an index. This isn’t perfect — it has to make those requests in a hurry, and the requests get cleared out when you take the database offline (or rebuild an index on the table). […]

Reply

Thanks for a great article. I’m currently trying to improve deadlocks in an application and your statement is pretty handy. I have so far reduced statements with a cost of 600+ down to 300+ and digging deeper with the help of various scripts and tools (Brent’s stuff, Kendra Little’s scripts and Jonathan Kehayias’ scripts).

In noticed that you’re selecting ‘deets.included_columns’ twice in your statement.

Reply
Lawrence Dugger
May 4, 2017 9:36 am

I am still seeing the reset behaviour after re-indexing on Microsoft SQL Server 2016 (RTM-CU2) (KB3182270) – 13.0.2164.0 (X64) Sep 9 2016 20:13:26 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )

Reply

    Are you seeing missing index requests reset, or index usage information reset?

    Post SQL Server 2016 RTM, I have only seen the missing index requests reset on index rebuild.

    Reply

With SQL 2012 SP4 is the resetting of sys.dm_db_index_usage_stats addressed or is there a CU that also needs to be applied?

Reply

    2012 SP4 includes everything released prior. However, I would test out the latest CU released after SP4 just to get the other fixes that have been released since SP4 (and Microsoft now recommends doing that, not just going with SPs.)

    Reply

please is this true in sql 2017 and sql 2019 too

Reply

    Yes, the behavior has not changed since SQL Server 2016, to my knowledge. In 2016+, I would encourage you to use Query Store to look at missing index requests, in any case, as it allows you to relate the request to individual queries, which I find much more useful than the DMVs.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu