SQL Server’s Query Store is generally considered as its flight recorder i.e. that black box which records everything about each query you execute on SQL Server. So, any time, you want to analyze a query for its performance, any analysis for all the queries which have run in last x duration or any queries that are or have been putting stress on your database, this is where you will have to go inside to determine the cause and effect of all your queries on the database.
We will be covering this topic in four different posts as mentioned below and out of which first post is covered in this article –
- Part-I Query Store concept and some key points about it
- Part-II Setting up Query Store and various configuration parameters
- Part-III Some ready made queries provided by Microsoft to take a sneak peak into Query Store data and
- Part-IV Query Store catalog views data model
Without any delay, let’s quickly jump to the point and look at some of the fundamental aspects of Query Store –
- This is a database-level feature i.e. setup at each database independently, not at the instance/server level
- Available with all editions of SQL Server
- You can not enable this on master or tempdb database
- It captures the SQL Server Estimated Query Execution Plans, not Actual Execution Plans
- It extracts every query from each executing statement i.e. if a stored procedure is executed, it captures all the queries inside stored procedure as independent statements
- Every data it captures is written back to disk but in an Asynchronous way i.e. it is first written in the memory and based on the configured parameters, it is written to the disk, no instantly
- This feature is available SQL Server 2016 and onwards and you need to enable this explicitly
- However, Azure SQL databases have this featured enabled, by default.
- To look at the data captured by Query Store, you need to have “VIEW DATABASE STATE” permissions in the database
- Since this is a database level feature so you can query catalog views to check if it is enabled or not as shown in this post also
How to check, if it is enabled or not –
SELECT is_query_store_on, * FROM sys.databases
OR
-- recommended for Azure SQL Database
SELECT * FROM sys.database_query_store_options
_state_desc column indicates that this is OFF i.e. not enabled.
Note: Since this is a database level feature and enabled by default on Azure SQL Database so only the second query gives you its status. If you use the first query, you still may see that it’s not enabled. So, it’s advisable to query query_store_options catalog view to check the status.
To conclude this post, one of the best use case for Query Store is any situation when you want to analyze a query’s historical execution performance and its query plan over a period of time. It also gives you an easy way to determine if lot of ad-hoc queries are being run on the database of if there are queries which can be made parameterized so that their plan is cached into memory.