Index Bloat in Postgres: Why It Matters, How to Identify, How to Resolve
Index bloat in Postgres can cause problems, but it’s easy to miss.
I’ve written about how vacuum problems can prevent PostgreSQL from …
Read MoreIndex Usage Stats Bug Fixed in SQL Server 2012 SP2 CU12 / SP3 CU3
on • 2 min read
Category:
indexing
Microsoft has just released a new round of cumulative updates for SQL Server 2012, and the release notes indicate that a fix to the sys.dm_db_index_usage_stats dynamic management view has been included in these updates. This is good news for index tuners using SQL Server 2012.
As of SP2 + CU12 and SP3 + CU3, SQL Server 2012 will persist index usage information even when ALTER INDEX REBUILD is run.
What About SQL Server 2014?
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.
What About Missing Index DMVs info?
These DMVs still get reset/cleared on index rebuilds. Vote to fix this bug here.
Summary of sys.dm_db_index_usage_stats and Missing Index DMV Behavior
| 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 (planned as of 5/18) . 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. |
Want the backstory? Read more in my post, “What Resets sys.dm_db_index_usage_stats and Missing Index DMVs.”
RELATED POSTS
