… continuing from my [previous post] on Query Store.
Here we will see how can we enable it on a Database:
–> Right click on the Database you want to enable Query Store and select Properties. Now select the new feature Query Store at the bottom left side as shown below:
Set the Enable option to True, and click OK.
–> Alternative: You can also enable the Query Store by this simple ALTER DATABASE Statement:
USE [TestManDB] GO ALTER DATABASE [TestManDB] SET QUERY_STORE = ON GO
–> After enabling the Query Store you can check the Database, expand it in Object Explorer, you will see a new folder with the same name “Query Store”, on expanding it you will see 4 reports, as shown in below snapshot:
–> You can check more about Query Store on MSDN BoL [here] with more coverage on what information/stats it captures and how you can Query them.
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
–> All data that query store stores will be available through the following Views:
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.