ALTER INDEX REBUILD Clears Missing Index Recommendations (SQL 2016 RC0) - by Kendra D Little

Status : 

 


132
0
Sign in
to vote
ID 2446044 Comments
Status Active Workarounds
Type Bug Repros 5
Opened 3/7/2016 10:06:41 AM
Access Restriction Public

Description

In SQL Server 2016 RC0, I am seeing a fix for the issue that rebuilding an index clears index usage stats (Connect bug 739566).  Please note that I had to choose CTP3 in the Version dropdown as there is no entry for RC0 yet.

However, I see that rebuilding any index on a table still clears out all missing index recommendations that have accrued for the table. This was not the case in SQL Server 2008 R2 and prior, it stopped working in SQL Server 2012 at the same time "usage stats" were cleared on rebuild.

ALTER INDEX REORGANIZE does not clear missing index recommendations, which is very confusing for most users.

To be clear, it makes sense from a user perspective that creating or dropping an index should clear missing index recommendation data, as recommendations might change. While in theory the same thing is possible from an index rebuild as it can cause plans to change, the SQL Server 2008R2 and prior behavior of not clearing the index recommendations on rebuild is much more helpful and very desirable.
Sign in to post a comment.