Query Store Added to SQL Server 2016
In order to understand a system's performance you need to have baseline data. The Query Store is a tool provided by SQL Server to capture baseline data. The Query Store is new SQL Server 2016 and is available in all editions. There are some Query Store related bugs in the early versions of Express and Standard editions so make sure you have the latest updates if you are using those versions. It is estimated that the overhead of the Query Store being enabled is 1-3% but ancedotal evidence suggests that you need to verify the 1-3% overhead on your system.
You cannot enable Query Store on master, tempdb, or model databases. You enable Query Store on a per-database level. The Query Store is stored internally and will be restored when you restore a database.
A db_owner is needed to force/unforce plans.
Query Store is 2 Stores
Runtime stats store
- compile time
- last exec time
- duration
- CPU
- logical reads
- physical reads
- writes
Plan Store
- Query text
- Query plan
Usage Scenarios
Query Store is used for proactively analyzing a workload and testing changes when upgrading old sql server versions to newer version. Upgrade testing for software and hardware changes. You can use the query store to tune top resource consuming queries.
To learn more I would suggest this excellent PluralSight course: https://www.pluralsight.com/courses/sqlserver-query-store-introduction.