As with any SQL Server feature, maximizing its potential involves tuning that feature for your specific environment. Common advice would be to begin with the defaults, then compare these with commonly agreed best practices and adjust those to what makes sense for your use case if necessary. Often there will be a widely accepted set of best practices within the community for typical deviations from Microsoft defaults. Sometimes defaults that appear “dumb” are often set that way, not because Microsoft is ignorant of its product or user base but for backward compatibility reasons.
In this chapter, we will discuss the steps for implementing and configuring the Query Store feature. We will cover the default configuration both on-premise and in the Azure SQL Database Platform as a Service (PaaS) offering. There will be a detailed breakdown of all the Query Store configuration options and the Query Store catalog views related to the configuration (for a list of all the Query Store–related catalog views, look to Chapter 5).
After this, we will commence a deep dive into best practices for configuring the Query Store. We will talk about how parameterization and parameterization settings can impact Query Store as well as the impact of renaming objects. Trace flags to improve recovery times and important performance-related patches for versions 2016 and 2017 will also be highlighted.
For those of you leveraging the capabilities of In-Memory OLTP, the use of Query Store with natively compiled stored procedures will be covered. We will also discuss an exciting new feature introduced in SQL 2017, known as automatic plan regression correction (APRC). What it entails and how to enable or disable it will be covered.
We will close the chapter with a discussion of the various query store error states and details on how to maintain the Query Store as a DBA.
Query Store Defaults
In this section, we will look at the default configuration options for the Query Store in SQL Server. Default options in SQL Server are not always the optimal configuration setting for your environment, but changing them does merit careful consideration. An important point to note is that the Query Store is disabled by default in the on-premise product for versions 2016, 2017, and 2019 at time of this writing. However, in the Microsoft Platform as a Service (PaaS) offering Azure SQL Database, it is enabled by default. For Azure SQL Database Managed Instances, a managed Infrastructure as a Service (IaaS) offering, the Query Store is supported but also disabled by default.
To enable Query Store, run the following T-SQL command in Listing 3-1:
ALTER DATABASE [<Database Name>] SET QUERY_STORE=ON;
Listing 3-1
T-SQL to enable Query Store on a database
To enable Query Store on all databases on your instance you can run the code in Listing3-2:
DECLARE @SQL NVARCHAR(MAX) = N'';
SELECT @SQL += REPLACE(N'ALTER DATABASE [{{DBNAME}}] SET QUERY_STORE=ON ',
'{{DBName}}', [name])
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND [name] NOT IN ('master', 'tempdb')
ORDER BY [name];
EXEC (@SQL);
Listing 3-2
Turn Query Store on all databases
The Query Store cannot be enabled in either the master or the tempdb system databases. Enabling the Query Store in the model database does not actually capture any Query Store data in the model database, but this configuration change will be reflected in newly created databases from that point forward. In the msdb system database, the Query Store will behave no differently than the Query Store in a user database. A table of options for Azure SQL Database and regular on-premise SQL server is presented in Table 3-1.
Table 3-1
Configuration options forQuery Store
Configuration Name
Options
Description
Default
OPERATION_MODE
OFF
READ_WRITE
READ_ONLY
Mode of operation for the Query Store
OFF (SQL 2016 and 2016)READ_WRITE for Azure SQL Database
CLEANUP_POLICY(STALE_QUERY_THRESHOLD_DAYS)
BIGINT
Dictates CLEAN_UP policy (0 is never)
30 days (or 7 for Azure SQL Database Basic Edition)
DATA_FLUSH_INTERVAL_SECONDS
BIGINT
Flush interval frequency for buffered Query Store data
900 (15 minutes)
MAX_STORAGE_SIZE_MB
BIGINT
The maximum size of Query Store in MB
100 (SQL Server 2016 and 2017), 1000 (SQL Server 2019), (1024 for SQL Azure Database Premium and 10 for SQL Azure Database Basic Edition)
INTERVAL_LENGTH_MINUTES
1, 5, 10, 15, 30, 60, or 1440
Aggregation interval for statistics
60
SIZE_BASED_CLEANUP_MODE
AUTO
OFF
Attempt to clean up if approaching capacity
AUTO
QUERY_STORE_CAPTURE_MODE
AUTO
ALL
CUSTOM
NONE
Query capturing behavior
AUTO (SQL Server 2016 and 2017), ALL (SQL Server 2019)
ALL (Azure SQL Database)
MAX_PLANS_PER_QUERY
INT
How many distinct plans to keep per query
200. Not available in SQL Server 2016
WAIT_STATISTICS_CAPTURE_MODE
ON
OFF
Specifies rather to capture wait statistics for queries
ON in SQL Server 2017 and 2019 and Azure SQL Database. Not available in SQL Server 2016
Configuration Options
Query Store has many options to configure to properly be set up to collect data in the most beneficial way for your database. This section will explain those options and give you recommendations on what the best settings would be and how to set those options. First, we will go through all the options and what they mean and look at code on how to change them. Then we will see what is available in the GUI and how to change options there.
OPERATION_MODE
This setting sets the operational mode of the Query Store. The Query Store operates in either read-only or read and write mode. In read-only mode, existing data will remain in the Query Store, but no new queries will be captured. If the Query Store reaches capacity, it will change from READ_WRITE to READ_ONLY mode. If capturing new queries is desired, which it typically will be, it is important to monitor to ensure that the Query Store has its OPERATION_MODE set correctly. The T-SQL syntax for setting this is in Listing 3-3.
ALTER DATABASE [<Database Name>] SET QUERY_STORE ( OPERATION_MODE = READ_WRITE );
Listing 3-3
T-SQL to set Query Store operation mode
The Query Store has the concept of an actual state and the desired state. Where the desired state is READ_WRITE, and the actual state is READ_ONLY, there will be an associated reason which will be displayed in the catalog view sys.database_query_store_options under the column readonly_reason. The associated catalog views for the Query Store will be covered in Chapter 5. The default setting is READ_WRITE.
CLEANUP_POLICY (STALE_QUERY_THRESHOLD_DAYS)
The Query Store has a configurable number of days to store Query Store data. The Query Store settings SIZE_BASED_CLEANUP_MODE and QUERY_STORE_CAPTURE_MODE will also have an impact on the data stored in Query Store, so it is possible for queries that do not exceed the stale threshold not to be stored. The default for STALE_QUERY_THREHOLD_DAYS is 30 days (or 7 days if using Azure SQL Database Basic edition). The T-SQL for configuring this setting is in Listing 3-4.
ALTER DATABASE [<Database Name>] SET QUERY_STORE ( CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = <Value> ) );
Listing 3-4
T-SQL to set STALE_QUERY_THRESHOLD_DAYS
DATA_FLUSH_INTERVAL_SECONDS
As discussed in Chapter 2, for performance purposes the Query Store will buffer data and write it asynchronously to disk at configurable intervals. This interval is known as the data flush interval, and it is specified in seconds. As will be discussed in greater detail in the section on Best Practices, this option requires careful consideration as it can have a considerable impact on the performance of the Query Store. Anyone familiar with indirect checkpoints and target recovery intervals in SQL Server will have a grasp of the trade-offs involved in setting the data flush interval for the Query Store. A short flush interval will result in more aggressive I/O spikes when the Query Store data is flushed to disk. This can lead to performance degradation of the I/O subsystem. A longer data flush interval should allow SQL Server to spread the I/O load over a longer time frame at the expense of greater data loss in the event of a systemic failure (since more of the Query Store data will be held in volatile memory). The default flush interval is 900 seconds (15 minutes). The T-SQL for configuring this setting is in Listing3-5.
ALTER DATABASE [<Database Name>] SET QUERY_STORE ( DATA_FLUSH_INTERVAL_SECONDS = <Value> );
Listing 3-5
T-SQL to set DATA_FLUSH_INTERVAL_SECONDS
MAX_STORAGE_SIZE_MB
This setting determines the maximum storage size for the Query Store data for an individual database in megabytes. The default setting of 1000 MB is quite small for any database with even a moderate level of activity. If this threshold is reached, the Query Store will change state from READ_WRITE to READ_ONLY. Even with SIZE_BASED_CLEANUP_MODE set to AUTO (more on this later), it is still possible for the Query Store to reach maximum capacity especially during periods of high activity. It is crucial to set the maximum storage size for the Query Store appropriately for your environment, considering your desired history retention set by the STALE_QUERY_THRESHOLD_DAYS value. The T-SQL for configuring this setting is in Listing3-6:
ALTER DATABASE [<Database Name>] SET QUERY_STORE ( MAX_STORAGE_SIZE_MB = <Value> );
Listing 3-6
T-SQL to set MAX_STORAGE_MAX_MB
INTERVAL_LENGTH_MINUTES
The INTERVAL_LENGTH_MINUTES is an important setting as it determines what interval data is aggregated into for viewing later. You can only select from values of 1, 5, 10, 14, 60, and 1440 minutes. The default value is 60 minutes. The smaller the interval, the more disk space it will take up but the more granular data you will have. The T-SQL for configuring this setting is in Listing 3-7.
ALTER DATABASE [<Database Name>] SET QUERY_STORE ( INTERVAL_LENGTH_MINUTES = <Value> );
Listing 3-7
T-SQL to set INTERVAL_LENGTH_MINUTES
SIZE_BASED_CLEANUP_MODE
The SIZE_BASED_CLEANUP_MODE default value is AUTO which means it will automatically clean up the data based on the MAX_STORAGE_SIZE_MB and CLEANUP_POLICY settings. The other option is to set it to OFF. This setting is set to tell Query Store to clean up data automatically before reaching the number of days specified with the CLEANUP_POLICY setting if it reaches the MAX_STORAGE_SIZE_MB value first. So, if you set Query Store to keep 30 days of data and it reaches your max size of 2 GB at day 28, it will start purging data at the point and time. The T-SQL for configuring this setting is in Listing 3-8.
ALTER DATABASE [<Database Name>] SET QUERY_STORE ( SIZE_BASED_CLEANUP_MODE = <Value> );
Listing 3-8
T-SQL to set SIZE_BASED_CLEANUP_MODE
QUERY_STORE_CAPTURE_MODE
The QUERY_STORE_CAPTURE_MODE default is ALL for both SQL Server on-premise and for Azure SQL Database. Another option is NONE, which tells Query Store to not capture new queries only, it continues to capture runtime statistics for queries that have already been captured by Query Store. The third option AUTO tells SQL Server not to capture queries that do take up significant resources or are not executed often. The T-SQL for configuring this setting is in Listing 3-9.
ALTER DATABASE [<Database Name>] SET QUERY_STORE ( QUERY_STORE_CAPTURE_MODE = [<Value>] );
Listing 3-9
T-SQL to set QUERY_STORE_CAPTURE_MODE
For the CUSTOM option for QUERY_STORE_CAPTURE_MODE, there are three options you can use to control how that data will be stored. This option was introduced to help with controlling what data was captured for ad-hoc workloads in SQL Server 2019. The STALE_CAPTURE_POLICY_THRESHOLD accepts a number for days or hours, from 1 hour up to 7 days that a query must exceed on of the values in the next three options in for the data for query to be captured. The three options that control what will be captured in the CUSTOM mode and operate in an OR manner are listed below:
EXECUTION_COUNT – Specifies how many times a query must be executed in the time period.
TOTAL_COMPILE_CPU_TIME_MS – Specifies the total CPU compile time a query must use in the time period.
TOTAL_EXECUTION_CPU_TIME_MS – Specifies the total CPU execution time the query must use in the time period.
The T-SQL for configuring the CUSTOM setting is in Listing 3-10.
ALTER DATABASE [<Database Name>]
SET QUERY_STORE = ON
(
QUERY_CAPTURE_MODE = CUSTOM,
QUERY_CAPTURE_POLICY = (
STALE_CAPTURE_POLICY_THRESHOLD = 24 HOURS,
EXECUTION_COUNT = 30,
TOTAL_COMPILE_CPU_TIME_MS = 1000,
TOTAL_EXECUTION_CPU_TIME_MS = 100
)
);
Listing 3-10
T-SQL to set QUERY_STORE_CAPTURE_MODE forCUSTOM mode
MAX_PLANS_PER_QUERY
The MAX_PLANS_PER_QUERY default is 200 plans. This setting is not available in SQL Server 2016. That may seem like a large number, but on some systems, there are thousands of plans per query, but 200 is a good starting point. The higher this setting though, the more disk space you will need if you have a large number of queries with a large number of plans. If you notice that you are hitting that limit, you can run the code in Listing 3-11 to see what your max number of query plans currently in plan cache is and use it determine what you would like this setting to be set to.
SELECT query_hash,
COUNT (DISTINCT query_plan_hash) distinct_plans
FROM sys.dm_exec_query_stats
GROUP BY query_hash
ORDER BY distinct_plans DESC;
Listing 3-11
T-SQL to find the number of plans in the cache per query
The T-SQL for configuring this setting is in Listing 3-12.
ALTER DATABASE [<Database Name>] SET QUERY_STORE ( MAX_PLANS_PER_QUERY = <Value> );
Listing 3-12
T-SQL to set MAX_PLANS_PER_QUERY
WAIT_STATISTICS_CAPTURE_MODE
The WAIT_STATISTICS_CAPTURE_MODE default is ON. This setting is not available in SQL Server 2016. The only other option for this setting is OFF. Wait statistics are discussed more in Chapter 9. The T-SQL for configuring this setting is in Listing 3-13.
ALTER DATABASE [<Database Name>] SET QUERY_STORE ( WAIT_STATISTICS_CAPTURE_MODE = <Value> );
Listing 3-13
T-SQL to set WAIT_STATISTICS_CAPTURE_MODE
Changing Configuration Using the GUI
By default, Query Store is turned off on-premise SQL Server databases and turned on by default in Azure SQL Database. To view the properties and change them, you would connect to your SQL Server instance, right-click on your database, and click on Properties. From there, click on the Query Store link on the left-hand side of the properties window. See Figure 3-1 to see example of what the properties windows looks like.
Figure 3-1
Query Store properties window
Note
You cannot change or see the MAX_PLANS_PER_QUERY or WAIT_STATISTICS_CAPTURE_MODE options in the GUI or the three options that come with the CUSTOM QUERY_STORE_CAPTURE_MODE setting.
You can see how much space the database is taking up and Query Store is taking up on the pie chart on the left. On the pie chart on the right, you can see the used and available space in Query Store. You also can purge all the data from Query Store with the Purge Query Data button.
Query Store Configuration Catalog View
There is one catalog view that holds the settings for Query Store: sys.database_query_store_options. For more on the catalog views, see Chapter 5. You can use the sys.database_query_store_options catalog view to view the settings for Query Store in the database. To view the configuration settings for Query Store, use the query in Listing 3-14.
SELECT *
FROM sys.database_query_store_options
Listing 3-14
T-SQL to view Query Store Options
We will look at some other queries in the Maintaining Query Store section later in this chapter.
Query Store Configuration Best Practices
The first setting that should be configured differently than the default is MAX_STORAGE_SIZE_MB. The default setting here is much too small to capture a 30-day or even longer workload. The general guideline is to start with 2048 MBs and if you must keep a longer retention period or have a large ad-hoc workload to adjust it up from there. Keep in mind that this data is stored in the PRIMARY filegroup, so if you are doing piecemeal restores, this will affect your recovery times, so you don’t want to set the size too high.
The next setting for on-premise SQL Servers that should be changed is QUERY_STORE_CAPTURE_MODE. This setting should be changed from ALL to AUTO unless you need to capture queries that take up insignificant resources or execute very few times. Capturing insignificant queries just causes more work for Query Store and takes more disk space in Query Store. So before leaving this setting to ALL, take those things into consideration.
The recommended setting for SIZE_BASED_CLEANUP_MODE is to leave the setting on AUTO because if the MAX_STORAGE_SIZE_MB is reached because it cannot clean up data, the OPERATION_MODE will be automatically switched to READ_ONLY mode leaving in a state where you are not collecting new data.
The next setting you may consider changing is INTERVAL_LENGTH_MINUTES. The recommended setting is the default of 60 unless you need data at a more granular level. In that case, going as low as 15 would be advisable as systems processing 60,000 transactions per second on the right hardware have been able to keep up aggregating that data. If you have more of an ad-hoc workload vs. stored procedure or parameterize workload, you are going to want to keep this setting higher as it will be writing more data.
The recommended setting for WAIT_STATISTICS_CAPTURE_MODE is ON because it gives more troubleshooting insight for your queries. Turning this setting OFF handicaps your ability to use a great feature built into Query Store for seeing what queries are causing which wait statistics to occur on your server by your database.
Parameterization and Query Store
We have touched on this topic a bit when talking about the QUERY_STORE_CAPTURE_MODE setting for query store. Let’s explore what this means more. First let’s discuss the difference between paramterize queries and ad-hoc queries. A parameterized query comes in as a stored procedure, function, trigger, or via sp_executesql. What makes it parameterized is that each variable that comes in has a predefined data type that SQL Server uses each time the query is called. For example, see the code in Listing 3-15 where we query for the LastName from a Customer table, but we have the data type fixed to 20 characters.
CREATE PROCEDURE dbo.GetName
@LastName VARCHAR(20)
AS
SET NOCOUNT ON;
SELECT FirstName,
LastName
FROM dbo.Customer
WHERE LastName = @LastName;
GO
EXEC dbo.GetName @LastName = 'Boggiano';
Listing 3-15
Stored Procedure Demonstrating a Parametrized Call to SQL Server
So, what this means is that every time this procedure is called it gets rolled up into one record per runtime interval in Query Store because it always has the variable defined as VARCHAR(20). Now with an ad-hoc query doing the same thing, see Listing 3-16.
SELECT FirstName,
LastName
FROM dbo.Customer
WHERE LastName = 'Boggiano';
Listing 3-16
T-SQL for Ad-hoc query
When this query is compiled, it stores the variable for LastName as eight characters, but if you run the same query with the LastName as Smith, it will store the query with a variable as five characters giving you two records per runtime interval in Query Store. The data doesn’t get aggregated and therefore makes it harder to tune and troubleshoot queries that are of the same type. Ad-hoc queries also bloat the size of Query Store due to the number of unique questions it must keep track of.
Impact of “Drop and Create” vs. “Alter”
Query Store stores the object_id of the procedures, triggers, and functions that execute inside of the database. By altering these objects, you preserve the object_id of the object; if you use the “drop and create” method, you will no longer have the same object_id so the data will no longer be aggregated together going forward. So, if you make a change to try to improve performance and you decide to go and compare the performance in Query Store to prior runtime intervals, you will not be able to view the results in the Query Store reports, you will have to query the catalog views. In SQL Server 2017, the syntax “CREATE OR ALTER” was introduced so you can avoid the need to code for a DROP/CREATE operation.
Impact of Database Renaming
Inside execution plans all objects are referenced as three-part names database.schema.object because you have the potential of doing cross-database queries. Renaming a database will cause plan forcing to fail, causing recompilation of all queries using those forced plans on each execution.
Reducing Recovery Times with Trace Flags
By default, all queries are blocked from running in the database until Query Store is loaded if Query Store is enabled on the database. This is where trace flag 7752 comes into play. By default, this behavior is on in Azure SQL Database, but on-premise you can turn on this trace flag and have Query Store load asynchronously, and in the background will be in a read-only state until it is completely loaded, so queries can process while Query Store loads, but you will not be capturing them. You can tell this is an issue on your system if you notice the wait stat QDS_LOADDB being high after a restart of your SQL Server instance. With this being the default behavior in Azure SQL Database it is quite possible it will become the default behavior in the on-premise product so we might want to go ahead and enable it now and get the benefits of faster startup times for our SQL Server instances. This also has the same effects on failovers.
Trace flag 7745 controls rather your SQL Server instance takes the time to flush all the Query Store data to disk while SQL Server instance is being shut down. This can take a considerable amount of time that you may not be willing to wait to depend on what you have your DATA_FLUSH_INTERVAL_SECONDS set to and/or the number of databases with Query Store enabled on your instance. The recommendation here would be to turn on this trace flag on because if you tell SQL Server to shut down; you don’t want to wait for data to be flush to disk, you want to get your SQL Server instance back up and running as fast as possible.
Query Store and Memory-Optimized Tables
Memory-optimized tables are tables that are stored entirely in memory. Therefore, Query Store tracks a limited number of metrics when tracking queries that executed against memory-optimized tables because those tables are stored in memory. Query Store does not track I/O due to the table residing entirely in memory and query memory used. It does, however, track other metrics such as duration, CPU time, the degree of parallelism, and the row count. When viewing reports discussed in Chapter 4, keep this in mind if you use memory-optimized tables.
Configuring Query Store for Natively Compiled Stored Procedures
Similarly, to memory-optimized tables, natively compiled stored procedures are treated differently by Query Store. Query plans and query text are stored in Query Store by default for natively compiled stored procedure and a flag denotes these procedures in the catalog view sys.query_store_plan. However, runtime statistics are not stored in the sys.query_store_runtime_stats catalog view by default. To capture runtime statistics, you must use the procedure sys.sp_xtp_control_query_exec_stats. The procedure can be used to capture statistics for all natively compiled procedures at the instance level or just particular ones you need to troubleshoot. There is a performance overhead associated with collecting these statistics. Listing 3-17 shows you how to set up a natively compiled stored procedure to capture runtime statistics.
DECLARE @dbid INT = DB_ID('<database>');
DECLARE @object_id INT = OBJECT_ID('<InMemoryProcedure>');
EXEC sys.sp_xtp_control_query_exec_stats
@new_collection_value = 1,
@database_id = @dbid,
@xtp_object_id = @object_id;
Listing 3-17
Setup Query Store to capture runtime statistics for a natively compiled stored procedure
Listing 3-18 shows you how to capture statistics for all natively compiled stored procedures on an instance.
EXEC sys.sp_xtp_control_query_exec_stats
@new_collection_value = 1;
Listing 3-18
Setup Query Store to capture runtime statistics for all natively compiled stored procedures on the instance
To turn off the collection of statistics, run the same code in Listings 3-16 or 3-17 and change the parameter @new_collection_value to 0.
Note
These settings are reset to not capture statistics on the event that the SQL Server instance is shut down or restarted. If you need it to persist in collecting statistics, you will need to set up a startup stored procedure or a SQL Agent job that runs at startup.
Enabling and Disabling Automatic Plan Regression Correction (APRC)
To enable automatic plan regression correction (APRC), you can run the following code in Listing 3-19 for one database or the code in Listing 3-20 to enable Query Store on all databases where query store is enabled and is a READ_WRITE state. What is APRC and how it works is discussed more in Chapter 9.
ALTER DATABASE [<Database>]
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );
Listing 3-19
T-SQL to enable APRC
DECLARE @SQL NVARCHAR(MAX) = N''
SELECT @SQL += REPLACE(N'ALTER DATABASE [{{DBNAME}}] SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON ',
'{{DBName}}', [name])
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND is_query_store_on = 1
ORDER BY [name];
EXEC (@SQL);;
Listing 3-20
T-SQL to enable APRC where database is online and Query Store is enabled
To turn off Query Store, we run the same queries with OFF instead on as seen in code listings above as seen in Listings 3-21 and 3-22.
ALTER DATABASE [<Database>]
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = OFF );
Listing 3-21
T-SQL to disable APRC
DECLARE @SQL NVARCHAR(MAX) = N'';
SELECT @SQL += REPLACE(N'ALTER DATABASE [{{DBNAME}}] SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = OFF ',
'{{DBName}}', [name])
FROM sys.databases
WHERE state_desc = 'ONLINE'
AND is_query_store_on = 1
ORDER BY [name];
EXEC (@SQL);
Listing 3-22
T-SQL to disable APRC where database is online and Query Store is enabled
Maintaining Query Store
After you have Query Store configured, there are some items you may need to keep an eye out for. Sometimes the state of Query Store will change from READ_WRITE to READ_ONLY or ERROR and you will need to know when this happends and correct this. You also need to monitor the space usage to determine if you are capturing the write amount of data for the period of time you need and to make sure you don’t run out of space. You also need to know if any plans you forced are failing and how to track those. You may also need to remove plans and queries from Query Store. Finally, you may need to reset the wait statistics.
Monitoring Desired vs. Actual State
First, to maintain Query Store, you will want to make sure the desired_state and the actual_state of Query Store match. You can run the code in Listing 3-23 to check in single database or the code in Listing 3-24 to check all the databases in Query Store enabled on your instance.
SELECT DB_NAME() database_name,
actual_state_desc,
desired_state_desc
FROM sys.database_query_store_options
WHERE desired_state_desc <> actual_state_desc
Listing 3-23
T-SQL to check Query Store’s desired state vs. actual state
T-SQL to check Query Store’s desired state vs. actual state on all databases
Query Store Read-Only States
Query Store can enter the READ_ONLY state from READ_WRITE without telling for a number of reasons. The reason is stored in the readonly_reason as a bit map in the sys.query_store_options catalog view. In Table 3-2 you will find a list of the reasons.
Table 3-2
Query Storeread-only statereasons
readonly_reason bit map
Description
1
Database is in read-only mode
2
Database is in single-user mode
4
Database is in emergency mode
8
Database is a secondary replica (Always On and Azure SQL Database geo-replication)
65536
Reached limit set by MAX_STORAGE_SIZE_MB
131072
The number of different statements has reached memory limit. In this case you should consider remove queries from the Query Store or upgrading service tier
Applies to Azure SQL Database
262144
In-memory size limit has been hit. Items will be persisted to disk until space is freed up in memory. Query Store will be temporarily in read-only mode
Applies to Azure SQL Database
524288
Database has reached disk size limit so Query Store can no longer grow
Applies to Azure SQL Database
How to Fix Query Store in Error State
Starting in SQL Server 2017, Query Store can enter an error state. This can cause potential performance problems when you depend on manual plan forcing or auto plan regression correction. This is a rare race condition that can occur without you knowing. The recommended fix is to first try to turn OFF Query Store and place it in READ_WRITE mode. If that doesn’t work, the persisted data is corrupted on disk, so we run sys.sp_query_store_consistency_check. Finally, clear Query Store of all data. In Listing 3-25 you have code to run against all the databases with Query Store in ERROR state to fix the ERROR state.
DECLARE @SQL AS NVARCHAR(MAX) = N'';
SELECT @SQL += REPLACE(N'USE [{{DBName}}]
--Try Changing to READ_WRITE
IF EXISTS (SELECT * FROM sys.database_query_store_options
WHERE actual_state=3)
BEGIN
BEGIN TRY
ALTER DATABASE [{{DBName}}] SET QUERY_STORE =
OFF
ALTER DATABASE [{{DBName}}] SET QUERY_STORE =
READ_WRITE
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
END
--Run sys.sp_query_store_consistency_check
IF EXISTS (SELECT * FROM sys.database_query_store_options
WHERE actual_state=3)
BEGIN
BEGIN TRY
EXEC
[{{DBName}}].sys.sp_query_store_consistency_check
ALTER DATABASE [{{DBName}}] SET QUERY_STORE =
ON
ALTER DATABASE [{{DBName}}] SET QUERY_STORE
(OPERATION_MODE = READ_WRITE)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
END
--Run purge Query Store
IF EXISTS (SELECT * FROM sys.database_query_store_options
WHERE actual_state=3)
BEGIN
BEGIN TRY
ALTER DATABASE [{{DBName}}] SET QUERY_STORE
CLEAR
ALTER DATABASE [{{DBName}}] SET QUERY_STORE
(OPERATION_MODE = READ_WRITE)
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage
END CATCH;
END
'
,'{{DBName}}', [name])
FROM sys.databases
WHERE is_query_store_on = 1;
EXEC (@SQL);
Listing 3-25
T-SQL to fix error state on all databases on instance
Tip
Due to the potential performance, impact recommendation would set the above code as a SQL Agent job to run a regular basis so you don’t get caught off guard when the Query Store is in an ERROR state.
Monitoring Space Usage
If you do set the CLEANUP_POLICY to AUTO as discussed earlier in the chapter, you will need to monitor the space usage yourself to make sure Query Store doesn’t switch to read-only mode. By default, Query Store will, at 90% capacity, clean up to an 80% capacity. If you have the MAX_STORAGE_SIZE_MB size set too low and have a large amount of transactions coming through, it is possible for Query Store to grow bigger than the max size specified. In Listing 3-26, you will find code to monitor space for a single database on your instance that is at 90% capacity.
USE [<Database>];
GO
SELECT current_storage_size_mb,
max_storage_size_mb,
FROM sys.database_query_store_options
WHERE CAST(CAST(current_storage_size_mb AS
DECIMAL(21, 2)) / CAST(max_storage_size_mb AS
DECIMAL(21, 2)) * 100 AS DECIMAL(4, 2)) >= 90
AND size_based_cleanup_mode_desc = 'OFF';
Listing 3-26
T-SQL code to check if Query Store is at 90% capacity
Tip
You will want to put this code in a SQL Agent Job and add code to email you to alert when Query Store is near capacity, so that you may address it.
How to Clear Query Store
You may find it necessary to clear the data manually from Query Store. You may use one of the following two methods in Listings 3-27 and 3-28 to this.
ALTER DATABASE [<Database Name>] SET QUERY_STORE CLEAR ALL;
Listing 3-27
T-SQL to clear all the data out of Query Store
USE [<Database>];
GO
EXEC sys.sp_query_store_flush_db;
Listing 3-28
Stored procedure to clear all the data out of Query Store
Failed Plan Forcing
Plan forcing can fail for a number of reasons such as an index in the plan being changed or dropped, online index rebuilds while trying to write to the index, or a hint conflict. It’s important to track failed plans so you know if you are getting the performance gain you expected when you forced the plans. There are two ways to track forced plans. The first is with T-SQL. You can query the catalog view sys.query_store_plan to see the last failure reason and failed count as seen in Listing 3-29.
SELECT plan_id,
force_failure_count,
last_force_failure_reason
FROM sys.query_store_plan
Listing 3-29
Query of plans with failed forced plans
Because the above method only shows the last forced plan reason then increments a counter and is per database, you may want to set up an extended events session that can track failed plan forcing across your SQL Server instance and set up a SQL Agent job to query and send out alerts. In Listing 3-30 you will find an extended events session that can set up to capture failed plan forcing.
Extended events session to capture failing force plans
Removing Plans and Queries
There are times when you may want to remove a plan from Query Store. You can use the procedure sys.sp_query_store_remove_plan to remove any plan from Query Store. When you run the procedure, it also removes the runtime statistics associated with that plan. It requires the EXECUTE permission on the database and DELETE permission on the Query Store catalog views. See Listing 3-31 for an example of how to remove a plan from Query Store.
Stored procedure to remove a query from the Query Store
Similarly, there are times when you may want to remove a query from Query Store, such as when you have several ad-hoc queries taking up space in Query Store. You can use the procedure sys.sp_query_store_remove_query to remove any query from Query Store. When you run the procedure, it also removes the runtime statistics. It requires the EXECUTE permission on the database and DELETE permission on the Query Store catalog views. See Listing 3-32 for an example of how to remove a query from Query Store by filling in the query_id.
Stored procedure to remove a query from the query store
Reset Statistics for a Plan
The stored procedure sys.sp_query_store_reset_exec_stats clears runtime statistics for a given plan but leaves the plan in Query Store. It requires the EXECUTE permission on the database and DELETE permission on the Query Store catalog views. See Listing 3-33 for an example of how to reset statistics for a plan from Query Store by filling in the plan_id.
In this chapter we have learned about all the configuration options for Query Store and the recommended settings. We talked about the effect of having parameterized queries on Query Store vs. ad-hoc queries. Then we talked about the impact of using the drop and create process for stored procedures, functions, and triggers on seeing statistics for those objects. We talked about how to reduce the shutdown and startup times of the SQL Server instance by using trace flags. We covered how to capture data for natively compiled stored procedures. Finally, we discussed various methods to maintain Query Store.
