Query Store will be available in all editions of SQL Server 2016.
This is a huge plus because Query Store serves as a data recorder for your query execution plans. It’ll help you troubleshoot :parameter sniffing issues, connection settings issues, plan regressions, bad stats, and much more.
It is a rich persisted db of query execution over time for SQL Server/Azure DB and is a central starting point for Query Tuning and Troubleshooting
– Query Store collects and persists:
– all query texts and to compile time stats
– all plan choices and runtime metrics – and allows you to force plans from history
What can you do with it?
Analyze performance and details of queries whether they are cached or not
Analyze the history of queries and plans across server restarts
Look at properties and statistics for queries not available in DMVs
What queries consume the most/least cpu, i/o, memory over time?
Compare performance before/after a change (A/B Testing)
Plan stability after upgrading to SQL Server 2016
Are ad-hoc queries killing performance filling up your cache?
What is the standard deviation of performance for a query?
What % of time is the performance of my query spent on compile?
What is the performance of queries with different SET options?
Keep performance information in the backup of the database
Allows a simpler and more robust method to force plans vs plan guides
Upgrades are also easier in SQL 2016 with Query Store – you control when Optimizer changes are enabled
and can also quickly fix regressions with Query Store
See Books Online’s section on Query Store to start learning, .
