SQL 2012 Statistics & Query Plans

I noticed that updating statistics did not remove what I determined to be a bad query plan.  I thought this was odd.  I searched connect and found a bug submitted by Kimberly Tripp.

https://connect.microsoft.com/SQLServer/feedback/details/769338/update-statistics-does-not-cause-plan-invalidation

I thought a-ha!  If she thinks it is a bug it is probably a bug.  But it turns out that this is by design and the data has to change in order for the plan to change. While this makes sense it is different than previous versions.

The morale of the story is to think before recollecting stats.  Did the data change?  If not then don’t update stats. In order for stats to have an impact it means the data changed but did not hit the threshold for auto update stats to have run.

If you think the plan is bad for some reason then recompile or delete the plan handle. 

Advertisements
This entry was posted in Database Administration and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s