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

, , ,

14 Responses to What Resets sys.dm_db_index_usage_stats and Missing Index DMVs?

  1. Ishtiaq Ahmed March 8, 2016 at 9:07 am #

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

  2. raulggonzalez March 23, 2016 at 1:20 am #

    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 🙂

    • Kendra Little March 23, 2016 at 9:40 am #

      Check the chart again– information is still cleared when a database is taken offline and service restart.

  3. John Ness February 27, 2017 at 4:01 am #

    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.

    • John Ness February 27, 2017 at 4:11 am #

      … on a second note: I guess you wanted to include `deets.inequality_columns’ instead.

      • Kendra Little February 27, 2017 at 9:25 am #

        Whoops! Yes, that is what I meant. Thanks, I corrected the post.

  4. Lawrence Dugger May 4, 2017 at 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: )

    • Kendra Little May 4, 2017 at 9:39 am #

      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.

Trackbacks/Pingbacks

  1. Index Usage Stats Bug Fixed – Curated SQL - March 8, 2016

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

  2. Queries do Dia a Dia: Rotina para criar um Histórico de Utilização de Índices - June 7, 2016

    […] http://littlekendra.com/2016/03/07/sql-server-2016-rc0-fixes-index-usage-stats-bug-missing-index… […]

  3. Index Usage Stats Insanity – sys.dm_db_index_usage_stats (Dear SQL DBA) - by Kendra Little - June 30, 2016

    […] More detail: http://littlekendra.com/2016/03/07/sql-server-2016-rc0-fixes-index-usage-stats-bug-missing-index… […]

  4. SQL Server Desired Enhancements | SQL RNNR - July 13, 2016

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

  5. SQL Server Desired Enhancements | Tech News - July 14, 2016

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

  6. Can I Use Statistics to Design Indexes? (Dear SQL DBA Episode 18) - by Kendra Little - October 6, 2016

    […] 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). […]

Leave a Reply