SQL with Manoj

In my [previous blog post] on “SQL Server 2016 Enhancements” we discussed about the new features coming in. Query Store is on of those feature that will add a lot value in troubleshooting and getting insights on Query & overall performance of a database.
 

–> Query store is a new component in SQL Server that captures Queries, Query Plans, Runtime Statistics, etc. in a persistent store inside a database. You can think of it as a aircraft’s black box, for your database.

It can also enforce policies to direct the SQL Server Query Processor to compile queries to be executed in a specific manner, such as forcing plans.

It is a database-scoped persistent store of query workload history. Query store is primarily targeting administrative scenarios for performance troubleshooting and identifying regressed workloads.

Query store collects query texts and all relevant properties, as well as query plan choices and performance metrics. This collection process works across restarts or upgrades of the server and across recompilation of indexes

It provides many configurable options for customization and it integrates with existing Query Execution Statistics, Plan forcing, and Manageability tools.
 

–> Query Store lowers the bar dramatically for performance troubleshooting and allows you to perform several kinds of scenarios:
– Conduct system-wide or database-level analysis and troubleshooting
– Access the full history of query execution
– Quickly pinpoint the most expensive queries
– Get all queries whose performance regressed over time
– Easily force a better plan from history
– Safely conduct server restarts or upgrades
– Identify issues with upgrades
 

–> Typical performance troubleshooting workflow:

 

–> All data that query store stores will be available through the following Views:
– sys.query_store_query_text
– sys.query_store_query
– sys.query_store_plan
– sys.query_context_settings
– sys.query_store_runtime_stats_interval
– sys.query_store_runtime_stats

By combining these views in various queries, you will gain necessary insights about user workload for a period of time when Query Store was active. Query Store also has a rich UI component that uses several of these same DMVs as shown below:

I’ve collated all these details from SQL Server white papers and other sources from Microsoft.
 

Check my [next post] on how to enable the Query store on a database from SSMS.