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.
Attribution: Bug photo by Tanguy Sauvin courtesy unsplash.com
19 Comments. Leave new
[…] Kendra Little reports that rebuilding an index no longer clears out sys.dm_db_index_usage_stats: […]
Done with my vote, up to now we have 29 Votes
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 🙂
Check the chart again– information is still cleared when a database is taken offline and service restart.
[…] http://littlekendra.com/2016/03/07/sql-server-2016-rc0-fixes-index-usage-stats-bug-missing-index… […]
[…] More detail: http://littlekendra.com/2016/03/07/sql-server-2016-rc0-fixes-index-usage-stats-bug-missing-index… […]
[…] For more information, you could read this article by Kendra Little – here. […]
[…] For more information, you could read this article by Kendra Little – here. […]
[…] 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). […]
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.
… on a second note: I guess you wanted to include `deets.inequality_columns’ instead.
Whoops! Yes, that is what I meant. Thanks, I corrected the post.
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: )
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.
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?
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.)
please is this true in sql 2017 and sql 2019 too
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.
[…] Here’s a summary of when these stats get rebuilt. I give credit to Kendra Little’s post here. […]