What Resets sys.dm_db_index_usage_stats and Missing Index DMVs?

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

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:

[table]

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.

[/table]

Notes:

• This table was updated May 18, 2016 following the release of fixes for SQL Server 2012 in Cumulative Updates. A comment on the Connect Item on sys.dm_db_index_usage_stats says the fix is planned to be released in the upcoming release of SQL Server 2014 SP2.
• Missing index recommendations have always been cleared for a table whenever you create, drop, or disable an index on it. That makes total sense and is desirable, as the optimizer may make quite different decisions with different indexes available.

Don’t forget to vote!

Attribution: Bug photo by Tanguy Sauvin courtesy unsplash.com