Query Data Store (SQL 2016 new feature)

One common problem in performance of SQL server is when the old execution plan is flushed out of plan cache and SQL server creates another plan that performs dreadfully.  I came across this type of problems especially when upgrading my SQL server to new version or edition or when restarting the SQL server. Identifying the issue was time consuming.

The new version of SQL server brought another feature that will store the history of query plans, captures performance data for each query plan, identifies the most expensive queries and earmarks those queries that have degraded over time, gives DBA’s an ability to force old plans, allows to fix plan regressions and above all avoid the risk of query being degraded by (upgrades and SQL server restarts).

The new query store plan is configured on database level rather than instance level.  The new feature is disabled by default and only be configured at database level. The new feature can be found on database properties page. It is also named as query store rather than query data store as I mentioned above. I am sure query data store matches what it does. There are various parameters that can be changed as shown below.  The most important of all is stale query threshold in days. It is the duration of retaining the query data store statistics. I haven't played with this feature yet and cannot say how good it is but from outset I am excited about it.