Query Store – Deepthi Goguri's SQL Server Blog

Query Store Fundamentals: Did you know?

February 3, 2022February 3, 2022 by dbanuggets, posted in Query Store

Did you enable Query store or have a plan to enable this amazing feature on your databases? There are many points that need to be remembered for effectively using Query Store on your databases. I have gathered some of the points as I learn about the Query Store. Hope the below list will be informative to you as well.

Query Store feature cannot be disabled in Azure SQL Database single database and Elastic Pool. When you are using on-prem environment, you need to enable on database you would like to collect the query information.
Use TF 7745 to avoid writing QS data to disk before SQL Server shutdown or during the failover. The general behavior of SQL Server is to write the Query Store data collected to disk before the SQL Server shutdown. This can take time to flush all collected data to disk and restart the SQL Server or during the failover situations. Using TF 7745 can cause the data collected be lost until the flush out interval period mentioned in the settings. If you can afford losing the data for that period of time, SQL Server restart or failover can take less time. SHUTDOWN WITH NOWAIT can also be used in the place of TF 7745 during SQL Server shutdown.
Use TF 7752 to avoid synchronous load of Query Store while database is in the process of starting up and be available online. Regular behavior of synchronous load can take time to recover the Query Store in full. Until the Query Store is completely recovered, Query executions will be in hault and cannot execute. This is not a good situation if your database is huge. By using the TF 7752, asynchronous load can happen later after database becomes online. This trace flag is enabled by default in SQL Server 2019
For in-memory OLTP, run time stats information is collected only when you enable using sys.sp_xtp_control_query_exec_stats (Transact-SQL). This setting needs to be enabled on each server restart as it does not persist on SQL Server restarts.
Did you know what the circle, square and triangle mean in Query Store? Size of the circle also have a message. Circle represents the successful execution, the more number of executions has the bigger size, rectangle represents a cancelled execution, triangle means an error caused during execution. For the below example, I executed the stored procedure 20 times, so the size of the circle you see is big and I stopped the same query execution in between to show a rectangle.

6. If your workloads have many ad-hoc queries, use Optimize for Ad hoc Workloads option to save cache memory as recommended by Microsoft. There is also a force parameterization option that you can enable at the database level to parameterize the ad-hoc queries so they reuse the plans. This can be an alternative option for Optimize for Ad hoc workloads. Randolph West and BrentOzar have explained the reasons why one option is better than the other and when to use one over the other. Thanks to Randolph and Brent for your detailed explanation! Check these posts for sure to know more!

7. Max plans per query are set as 200 execution plans per query by default. You can change this setting based on your workload and requirements.

8. If MAX_STORAGE_SIZE_MB is reached, Query Store goes into read-only mode. Remember, the storage size is only checked at the flush-out intervals. If the storage size is reached to the maximum limit before reaching the flush-out intervals, that’s when the Query store goes to read-only mode. In read-only mode, the Query Store can read the forced plan information and apply a forced plan but cannot write any information into the Query Store.

9. To avoid reaching the maximum storage size, make sure the “size-based cleanup mode” is always set to AUTO which is a default. This option when set in AUTO mode avoids Query Store from going to read-only mode by deleting the old data from Query Store once it reaches 90% capacity of its Query Store maximum storage size limit. It clears up until it reaches 80% of space and this avoids the need of changing to read-only mode.

10. The QS QUERY_CAPTURE_MODE, AUTO will avoid ad-hoc queries with small resource consumption by default. This is recommended mode by default. This mode is enabled by default in Azure SQL Database, SQL Server 2019

11. Deleting plan cache data doesn’t delete the Query Store data. Query Store data is persisted upon the SQL Server restarts. The cleanup policy can be set by using the STALE_QUERY_THRESHOLD_DAYS setting.

12. Last by not least, there is a query that can delete the ad hoc queries from the Query Store. This will avoid query store space from filling up quickly. Follow this article to find the query that can delete ad-hoc queries

Thanks for reading!

Resources:

Query Store Fundamentals: Query Store Hints

January 19, 2022 by dbanuggets, posted in Query Store

Query Store hints is another amazing feature added and is available in the preview mode in Azure SQL Database, managed instances, elastic pools and hyperscale databases as well. Query Store hints are just like the hints you use in your queries but the difference is you do not have to change the query code to apply these hints using Query store specifically designed stored procedure- sys.sp_query_store_set_hints. Cool, right?

Query Store hints are very useful when you need to use the hints and force the optimizer to use those hints but without changing the underneath query. This feature is used for specific queries in some circumstances. Some of the examples that can take the advantage of this feature are the parameter sensitive queries where recompile is needed to create an optimal plan each time it executes, set the MAXDOP value, memory grant size setting for bulk operations, etc. (Source: Microsoft Docs)

You also have the choice to choose the hints like forcing the legacy cardinality estimation for the queries not performing well on the newer versions of SQL Server, using different compatibility level for a particular query as the database compatibility level is another.

For the complete list of hints that are supported and not supported, please see it here.

Now, let’s see an example of a stored procedure where we can apply the Query Store hints. This stored procedure have sensitive parameters and we need to use recompile to make sure it generates the optimal plan for each execution. I am using Azure SQL Database Adventureworks for this demo. Query store is already enabled on this database by default. Let’s create the stored procedure.

IF EXISTS (
		SELECT *
		FROM sys.objects
		WHERE type = 'P'
			AND name = 'Salesinformation'
		)
	DROP PROCEDURE dbo.Salesinformation
GO

CREATE PROCEDURE dbo.Salesinformation @productID [int]
AS
BEGIN
	SELECT [SalesOrderID]
		,[ProductID]
		,[OrderQty]
	FROM [Sales].[SalesOrderDetailEnlarged]
	WHERE [ProductID] = @productID;
END;

Let me clear out the Query Store data and plan cache before running the test queries.

ALTER DATABASE [AdventureWorks]

SET QUERY_STORE CLEAR;

/* Clean the procedure cache */
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Execute the stored procedure with two different parameters (the first parameter results around 30K rowsand the second parameter results 380 rows)

/* Executing the sp resulting in many rows with this parameter */
EXEC dbo.Salesinformation 870

/* This parameter will result in only couple of rows, it reuses the same execution plan */
EXEC dbo.Salesinformation 942

If you observe the execution plan for both the executions, the parameter 942 only results in 380 rows but it is doing the clustered index scan, which is not optimal in this scenario.

To fix this usually, we will use recompile as a runtime hint or in the stored procedure header. With the Query Store hints, all we need to get is the query id and the hint information we want to apply. This is done in two steps. First, grab the query id by querying the system catalog views and filtering on the query text by using the like operator.

/* Find the query ID associated with the query
Source: https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints?view=azuresqldb-current
*/
SELECT query_sql_text
	,q.query_id
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
WHERE query_sql_text LIKE N'%@productID int%'
	AND query_sql_text NOT LIKE N'%query_store%';
GO

The query_id is 1 for this stored procedure. Now, we can use the stored procedure sp_query_store_set_hints and pass the parameter values query_id and the hint (recompile) in this case to fix the problem.

/* Set the Query hint by using the sp sp_query_store_set_hints
Source: https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints?view=azuresqldb-current
*/
EXEC sp_query_store_set_hints @query_id = 1
	,@value = N'OPTION(RECOMPILE)';
GO

Now, execute the stored procedure again with the same parameters and see if the recompile occured.

/* Execute the sp again with two parameters */
EXEC dbo.Salesinformation 870

EXEC dbo.Salesinformation 942

The stored procedure is recompiled as the query hint has been applied and you can see the difference in the execution plans. For the parameter value with 380 records, it recompiled to create an execution plan using an index seek and a key lookup which is optimal for this execution.

To check for the query hints that are currently enabled, run the below query using the catalog views

/* Check for the Query hints that are enabled 
Source: https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints?view=azuresqldb-current*/
SELECT query_hint_id
	,query_id
	,query_hint_text
	,last_query_hint_failure_reason
	,last_query_hint_failure_reason_desc
	,query_hint_failure_count
	,source
	,source_desc
FROM sys.query_store_query_hints;
GO

The query_hint_id is shown as 1. As you update the hints on this query_id, this number increases. The query_hint_text always shows the currently active hint that has been applied. The history of the query_hints has not been displayed.

If you would like to use the MAXDOP hint set to 1 for the parallelism used in the execution plan. Update the hint to MAXDOP by running the stored procedure below. Run the stored procedure again with the parameters to see the hint being applied.


/* MAXDOP HINT */
EXEC sp_query_store_set_hints @query_id = 1
	,@value = N'OPTION(MAXDOP 1)';
GO

/* Clean the procedure cache */
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

EXEC dbo.Salesinformation 870

EXEC dbo.Salesinformation 942

If you observe the execution plans, the hint has been updated to MAXDOP. If you observe, the “NonParallelPlanReason” is shown as MAXDOPsetToOne and the recompile hint that has been previously applied is not active anymore. If you check the query store hints active list by using the system catalog views, updated MAXDOP hint is only shown but the Query_hint_id is being updated to 2.

We can use multiple query store hints in the same stored procedure like below and execute the stored procedure again with two parameters.


/* Multiple query hints */
EXEC sp_query_store_set_hints @query_id = 1
	,@value = N'OPTION(RECOMPILE,MAXDOP 1)';
GO

/* Clean the procedure cache */
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

EXEC dbo.Salesinformation 870

EXEC dbo.Salesinformation 942

If you observe the plan and the details, both the recompile and MAXDOP query hints have been applied together. If you check the status of the query hints, this time the number of the Query_hint_id will be 3.

To remove the query hints that have been active on the query_id, use sp_query_store_clear_hints by providing the query_id to remove the Query Store hints on specific query_id.

/* Remove the Query hints from Query Store */
EXEC sp_query_store_clear_hints @query_id = 1;
GO

To make sure the Query Store hints are disabled on the query_id, recheck by running the below query:

/* Recheck for the Query hints to make sure Query hints are diabled */
SELECT query_hint_id
	,query_id
	,query_hint_text
	,last_query_hint_failure_reason
	,last_query_hint_failure_reason_desc
	,query_hint_failure_count
	,source
	,source_desc
FROM sys.query_store_query_hints;
GO

In this blog post, we have only tested some of the Query Store hints that are supported. There are many other hints that are supported and do not forget to read the entire blog post here from Microsoft to know the details of the hints that are being supported and not supported.

Summary:

In this post, we have seen what Query Store hints are and tried a couple of examples to see how the preview mode feature works. If you have a need to use any of the supported hints in your queries but are unable to edit and modify the text of the query because of any reasons like the third-party applications, upgraded the database to newer SQL Servers but have to use the older cardinality estimation on any particular query not performing well or use older compatibility level for any particular query without interfering with the database level compatibility, Query store hints can be very useful.

Thanks for reading!

Resources:

Query Store Fundamentals: How Automatic plan correction works?

January 5, 2022January 5, 2022 by dbanuggets, posted in Query Store

How wonderful will that be if SQL Server has a way of automatically tune our Queries based on our workloads, amazing! Right?

Thanks to Microsoft for introducing the automatic tuning feature in SQL Server 2017 and available in Azure SQL Database. Automatic tuning has two features. Automatic plan correction and Automatic index correction (Source: Microsoft)

So, what is this automatic option, and how it works?

Once you enable the automatic plan correction, this feature continuously monitors and sniffs the parameter-sensitive queries that are causing the performance problem and monitors the changes in the execution plans, and always looks for the last good plan or the better plan that is newly created. For example, the Automatic plan correction feature has forced a query plan based on the ongoing workload and for any reason, a new plan is created (many reasons can cause the new plan creation like forced plan failures, index rebuilds, statistics updates etc.) and if the existing forced plan is not the best plan any more than the newly generated plan, this feature will go ahead and unforce the existing forced plan and force the newly generated plan. If on the other side, the newly generated plan is not optimal when compared with the existing forced plan, it keeps the existing forced plan as forced and continues to monitor the regression.

Why do you need Automatic plan correction?

As a production DBA, monitoring the generated plans and manually comparing the plans to force the best plan can be difficult. SQL Server engine chooses the optimal plan for our workloads but if your query is generating many execution plans for those parameter sensitive queries, enabling this feature will save a lot of time and energy for you to focus on other tasks. You can also find the reason why the automatic plan correction feature has unforced/forced a plan by using sys.dm_db_tuning_recommendations. It also provides recommendations and corrective action to fix the problem and other information like the reason for the plan change, the detection time for the regression, the script to force the plan that will be optimal and fix the problem. According to Microsoft, “There should be enough regression between the plans for implementing the recommendations, and that is caused when the estimated CPU gain is more than 10 seconds or the errors that are caused by the forced plan is more than the currently generated new plan (Source: Microsoft).

To enable the automatic plan correction:

ALTER DATABASE <yourDatabase>
SET AUTOMATIC_TUNING ( FORCE_LAST_GOOD_PLAN = ON );

Let us use the Azure SQL Database for our demo to demonstrate how the automatic plan correction works. Once we enable the automatic plan correction to be turned on, let us make sure the settings are updated and ready to go.

/* Checking the tuning options if the option is enabled */
SELECT *
FROM sys.database_automatic_tuning_options

Create a stored procedure, clearing out the query store clear out the plan cache before beginning the workload

/* Creating the Stored procedure */
IF EXISTS (
		SELECT *
		FROM sys.objects
		WHERE type = 'P'
			AND name = 'Salesinformation'
		)
	DROP PROCEDURE dbo.Salesinformation
GO

CREATE PROCEDURE dbo.Salesinformation @productID [int]
AS
BEGIN
	SELECT [SalesOrderID]
		,[ProductID]
		,[OrderQty]
	FROM [Sales].[SalesOrderDetailEnlarged]
	WHERE [ProductID] = @productID;
END;

/* Clearing the Query store */
ALTER DATABASE [AdventureWorks]

SET QUERY_STORE CLEAR;

/* Clean the procedure cache */
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Create the first workload, check for the execution plan in the Query Store. The below parameter will only generate 385 records.

/* Creating the first workload */
EXEC dbo.Salesinformation 942 
GO 50

Clearing out the procedure cache

/* Clean the procedure cache */
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE

Now, create the regression. This parameter generates 237K records. That causes the regression as it is parameter-sensitive.

/* Creating Regression */
EXEC dbo.Salesinformation 707 
GO 10

Run the same stored procedure with the first-time parameter again (Parameter value 942) and check the Query store again.

/* Run the workload again */
EXEC dbo.Salesinformation 942 
GO 30

When you observe, plan ID 1 is automatically forced by the automatic plan correction plan feature. Amazing, right? If you look at the reason by querying the sys.dm_db_tuning_recommendations, you can check the reason for the plan change and other information.

/* Check for the Automatic plan correction in the Query Store */
/* Check for the tuning recommendations */
SELECT *
FROM sys.dm_db_tuning_recommendations

With the continuous monitoring for the regressed plans and performance of your queries, forcing the last known good plan is one of the best features Microsoft offers.

There are certain limitations to the plan forcing. Please check them here.

Summary:

Manually monitoring the forced plans is a tedious task. As we know that there can be better plans generated for the queries with the forced plans later down the road. In those scenarios, it will be really tough to monitor each of those forced plans, unforce, and force the newly generated optimal plan for our queries. The automatic plan correction feature takes that burden by automatically doing the unforcing/forcing of the plans based upon the regressions caused. The last known good plan is ensured to be forced automatically.

Thanks for reading!

Resources:

Query Store Fundamentals: Why my plan forcing has failed?

December 27, 2021 by dbanuggets, posted in Query Store

One of the advantages of using Query store is the ability to force a plan. Once you force a plan, there may be reasons when the optimizer still chose to create a new execution plan without using the forced plan. During this time, a failure reason is stored in the Query Store. When the forced plan cannot be used, the optimizer goes through the regular compilation and optimization phases to create a new plan but it doesn’t actually fail the query itself. It executes in a normal way with the new plan being created.

But what causes these failures and how to know the reason behind these forced plan failures?

When you alter/drop the schema of the objects used inside the query plan you forced can cause the forced plan failures.

Let see with examples.

Let’s create a stored procedure, execute it and see the actual execution plan

IF EXISTS (
		SELECT *
		FROM sys.objects
		WHERE type = 'P'
			AND name = 'StockInfo'
		)
	DROP PROCEDURE dbo.StockInfo
GO

CREATE PROCEDURE dbo.StockInfo @Quantity [int]
AS
BEGIN
	SELECT [fo].[Order Key]
		,[si].[Lead Time Days]
		,[fo].[Quantity]
	FROM [Fact].[Order] AS [fo]
	INNER JOIN [Dimension].[Stock Item] AS [si] ON [fo].[Stock Item Key] = [si].[Stock Item Key]
	WHERE [fo].[Quantity] = @Quantity;
END;

USE WideWorldImportersDW
GO

EXEC dbo.StockInfo 360;

To work on this example, let’s create the index and see if the index will be used.

USE [WideWorldImportersDW]
GO

CREATE NONCLUSTERED INDEX OderQuantity ON [Fact].[Order] ([Quantity]) INCLUDE (
	[Order Key]
	,[Stock Item Key]
	)
GO

Rerun the stored procedure and check if the index is being used

USE WideWorldImportersDW
GO

EXEC dbo.StockInfo 360;

As the new plan is using the index, let’s force this plan so we can later modify this index object and see how the forced plan failures can be caused.

As I forced the plan using the index, I will now go ahead and drop the index and execute the stored procedure again.

USE [WideWorldImportersDW]
GO

DROP INDEX OderQuantity ON [Fact].[Order]

USE WideWorldImportersDW
GO

EXEC dbo.StockInfo 360;

Did you observe, the query itself doesn’t fail but a new plan is generated although there is a forced plan. If you observe the newly generated plan doesn’t have the index in it.

Let’s go ahead and see the reason for this failure in Queries with forced plans using GUI. The reason for the failure is NO_INDEX as we dropped the index being used in the forced plan. There is no index that was used in the forced plan and so is the reason.

Once you create the exact index that has been dropped, it uses the forced plan back again.

USE [WideWorldImportersDW]
GO

CREATE NONCLUSTERED INDEX OderQuantity ON [Fact].[Order] ([Quantity]) INCLUDE (
	[Order Key]
	,[Stock Item Key]
	)
GO

/* Rerun the sp */
USE WideWorldImportersDW
GO

EXEC dbo.StockInfo 360;

Altering the index used in the forced plan can also cause the failure. I will add a column to the index and rerun the stored procedure again. Check for the plan again.

USE [WideWorldImportersDW]
GO

CREATE INDEX OderQuantity ON [Fact].[Order] (
	[Quantity]
	,[Order Key]
	)
	WITH (DROP_EXISTING = ON);

/* Rerun the sp */
USE WideWorldImportersDW
GO

EXEC dbo.StockInfo 360;
GO

Plan 2 is being used.

Open the Queries with forced plans and see the failure reason. NO_PLAN is the reason as there is no forced plan associated with that index definition. Remember, only the last failure reason is shown here and only the last_force_failure_count is shown but not the history of the reasons are shown. We can capture this information using Extended events shown at the ending of this post.

If you have queries with forced plans and those queries have execution plans using the three-part name queries (database.schema.object), renaming the database name will cause failure as well (Source: Microsoft)

If you observe, only the latest failure reason is shown in the Queries with forced plan report using GUI or by using sys.query_store_plan.

Code Source: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15
USE [QueryStoreDB];
GO

SELECT p.plan_id, p.query_id, q.object_id as containing_object_id,
    force_failure_count, last_force_failure_reason_desc
FROM sys.query_store_plan AS p
JOIN sys.query_store_query AS q on p.query_id = q.query_id
WHERE is_forced_plan = 1;

To capture the history of the failure reasons, you can capture using the extended event “query_store_plan_forcing_failed“. Let’s create the Xevent:

CREATE EVENT SESSION [Querystoreforcedplanfailures] ON SERVER 

ADD EVENT qds.query_store_plan_forcing_failed
ADD TARGET package0.event_file(SET filename=N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\qserror.xel'),
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE=OFF)
GO

Enabled the Extended event and captured the information of when the Query Store plan forcing failed and the reason for the failure. Dropped the index “OderQuantity” first which captured the failure and later changed the definition of the index “OderQuantity” by adding a column. Both the failures were captured along with the error number, plan_id and the query_id information.

You can also use the extended event to notify when there is a forced plan failure by using the event “qds.query_store_notify_force_failure_failed“

Summary:

In this blog post, we have seen the reasons of why plan forcing fails and how to view them using GUI/sys.query_store_plan/extended events. Once you forced a plan doesn’t mean that it will always be forced and used. It can create a new plan by going through the regular optimization processes. Altering or dropping the objects within the forced execution plan or changing the name of the database with active forced plans can cause the failures.

Thanks for reading!

Resources:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15

Query Store Fundamentals: Capture Performance Baselines

December 7, 2021December 7, 2021 by dbanuggets, posted in Query Store

Prior to SQL Server 2016, it is difficult to figure out the reason for the changes in the plan as the procedure cache only stores the latest plans and the history of the plans were not stored. When a query is sent to SQL Server, the optimizer can choose an already existing plan from the plan cache or choose a different plan because of some reasons like the changes in the cardinality estimation, rebuilding indexes or update statistics. The new plan that gets generated may be optimal or non-optimal. Using the Query Store feature, plan regressions can be identified easily.

The Query Store feature was especially useful to me when I upgraded several databases to the newer versions of SQL Server. With the new cardinality estimation released in SQL Server 2014, the optimizer changes are linked to the compatibility level of the database. Query store helps to capture the baselines during the database upgrades by collecting the plan information for all the queries as they were prior to the upgrade. This is possible by placing the database in the same older compatibility mode after the upgrade. Once all the Queries execute collecting the Query plan information with the old compatibility mode, update the database compatibility to the newer version of SQL Server.

This will help us in identifying any plan changes after the database upgrades with the new compatibility mode. New versions of SQL Server create better execution plans with the new cardinality estimation but there may be some queries that work better with older cardinality estimates. If you see there are plan regressions for your queries after upgrading to the latest compatibility level of the database, you can simply choose the last good plan generated using the old compatibility version of SQL Server from the Query Store. This will save you from a lot of time and from frustration in identifying the reasons for the slow-performing queries after the database upgrade.

With the new cardinality estimates, most of the queries run faster than before but there may be cases where you see the better running queries performing slowly after the upgrades. This use case is especially for those queries.

Let’s see an example of capturing a baseline using the Query Store feature for database upgrades.

In this scenario, we are upgrading a database from SQL Server 2012 to SQL Server 2016.

After upgrading the database to SQL Server 2016, keep the database compatibility mode the same as SQL Server 2012.

/* restoring the database WideWorldImportersDW from 
https://github.com/Microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImportersDW-Full.bak
*/

/* restore database WideWorldImportersDW
from
disk='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\WideWorldImportersDW-Full.bak'
with move
'WWI_Primary' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\WideWorldImportersDW.mdf',
move 'WWI_UserData' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\WideWorldImportersDW.ndf',
move 'WWI_Log' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\WideWorldImportersDW_log.ldf',
move 'WWIDW_InMemory_Data_1' to 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\WWIDW_InMemory_Data_1'

restore filelistonly from disk=
'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\WideWorldImportersDW-Full.bak'
*/

/* free the cache */

dbcc freeproccache

/* enable the Query store on the database */

ALTER DATABASE WideWorldImportersDW
SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

/* set query store options 
Source code: https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15
*/
ALTER DATABASE WideWorldImportersDW
SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 3000,
    MAX_STORAGE_SIZE_MB = 500,
    INTERVAL_LENGTH_MINUTES = 15,
    SIZE_BASED_CLEANUP_MODE = AUTO,
    QUERY_CAPTURE_MODE = AUTO,
    MAX_PLANS_PER_QUERY = 1000,
    WAIT_STATS_CAPTURE_MODE = ON
);

/* change the compatability of database to 110 */

Use Master
Go
ALTER DATABASE WideWorldImportersDW SET COMPATIBILITY_LEVEL = 110;

/*  
Query from the Microsoft docs 
https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15#adaptive
Placed the Query to run as a stored procedure
*/

USE WideWorldImportersDW
GO
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'StockInfo')
DROP PROCEDURE dbo.StockInfo
GO
CREATE PROCEDURE dbo.StockInfo
@Quantity [int]
AS
BEGIN
SELECT [fo].[Order Key], [si].[Lead Time Days], [fo].[Quantity]
FROM [Fact].[Order] AS [fo]
INNER JOIN [Dimension].[Stock Item] AS [si]
       ON [fo].[Stock Item Key] = [si].[Stock Item Key]
WHERE [fo].[Quantity] = @Quantity;
END;

/*clear the Query store */

ALTER DATABASE WideWorldImportersDW SET QUERY_STORE CLEAR;

/*clear the cache */

dbcc freeproccache

/*Execute the stored procedure */

USE WideWorldImportersDW
GO
EXEC dbo.StockInfo 360;

/* change the compatability level of the database to 130 */

Use Master
Go
ALTER DATABASE WideWorldImportersDW SET COMPATIBILITY_LEVEL = 130;


/* rerun the stored procedure */

USE WideWorldImportersDW
GO
EXEC dbo.StockInfo 360;

Below is the screenshot of the Execution plans. Plan Id 2 is the plan generated when the database is still in SQL Server 2012 Compatibility mode. After upgrading the database to SQL Server 2016 compatibility mode, Plan Id 3 is created. When we hover the mouse to see the total duration, Plan 2 was performing well then plan 3. This query performed well with the older compatibility mode. As we now have the baseline information captured by the Query Store, we can go ahead and force the plan Id 2 which is the last good plan for that query. Capturing the baseline is really helpful when you have those specific queries which don’t perform well with the latest cardinality estimation.

Summary:

In this post, I have explained the usage scenario when Query Store was very helpful to me to capture the performance baselines during the database upgrades.

There are several other use cases where Query Store is very helpful like identifying the top resource consuming queries, quickly fixing the performance issues caused by the parameter sensitive queries, automatic plan correction which is available from SQL Server 2017 and above which will monitor the regressions continuously and force/unforce the last good plan based upon the plan comparisons and Query Store hints which is in preview mode in Azure SQL.

The main advantage of the Query Store feature is to quickly fix the performance issues without changing the underlying code. This can be very helpful in scenarios where it takes time to performance tune the query code itself to fix the performance problem.

In the next post, we will learn how automatic plan correction works.

Resources:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios?view=sql-server-ver15

Query Store Fundamentals: How does Query Store Work?

December 2, 2021December 2, 2021 by dbanuggets, posted in Query Store

In the previous post, we learned about the basics of the Query Store. In this session, we will learn how Query Store works behind the scenes.

When a query is submitted to the SQL Server, it checks if there is already a plan available for the query in the procedure cache. If the plan is not found, Query goes through the compilation and optimization phases.

The query is checked for recompilation. During this phase, if the plan in the procedure cache is not similar to the forced plan for that query (If the plan has been forced for that query), the query goes through the recompilation and a similar forced plan is generated (i.e, the forced plan is applied just like the PLAN Hint). If the plan in the cache is the same as the forced plan, it will go ahead and use the same plan.

Before the query executes, the query text and the query plan are stored in the plan store which is in-memory. This information is stored asynchronously. Once the query executes, the run time stats information is captured in the runtime stats store which is in memory as well and stored asynchronously. The runtime stats are captured in memory and flushed out to the Query Store schema asynchronously as per the Data Flush Interval (Minutes) settings for the Query store. The query text and the query plan information are sent to the Query store schema with minimal latency (Source: Microsoft). If there is memory pressure, the runtime stats information can be sent to the disk before hitting the data flush interval.

*Based on the resource: https://docs.microsoft.com/en-us/sql/relational-databases/performance/how-query-store-collects-data?view=sql-server-ver15*

Know the Query store settings and what each setting actually mean:

When you enabled Query Store, you cannot always depend on the defaults. It is very important to know what each of the setting means and why it is important to set it right based on your workload.

DATA_FLUSH_INTERVAL_SECONDS (type: bigint) determines how often the data that has been collected in the memory is flushed out to the disk and stored on the primary data file of the database and this is depended on the interval we chose. By default, the interval is 15 minutes.

CLEANUP_POLICY determines how long you want the Query Store to save the data. The default is 30 days (STALE_QUERY_THRESHOLD_DAYS) which can be changed as per your needs.

MAX_STORAGE_SIZE_MB (bigint) determines the amount of space the Query Store can use. By default, it is 100MB for SQL Server 2016/2017. For SQL Server 2019, the default size is 1GB. Remember that, the size of the max storage size is checked when ever the data is flushed to disk based on the interval settings. If the size is full meanwhile, the query store will to be changed to read-only mode. If you chose the SIZE_BASED_CLEANUP_MODE is set then the cleanup mechanism is triggered automatically. Once the cleanup is completed, the Query Store will be change back to read-write (Source: Microsoft Docs)

INTERVAL_LENGTH_MINUTES is the interval where the runtime statistics are aggregated. If we capture the runtime stats for every query individually, that will fill up the Query store size pretty quickly. For that reason, the aggregate information is collected. For example, if you have a query being run 100 times in the interval of 15 minutes, there is only one entry and one row allocated for this query with the aggregated information between that interval. Lets say the interval number is 1. The next interval number 2 starts after 15minutes. For that interval number 2, if the same query runs again, it will get a single row entry no matter how many times it executes during that interval number 2.

SIZE_BASED_CLEANUP_MODE is AUTO by default. The cleanup process will be automatically triggered when the MAX size of the Query Store reaches 90%. This option helps Query store not to go to read only mode. This process cleans up the oldest queries and the least expensive data first. It is suggested not to turn off this option.

Summary:

In this blog post, we learnt about how the Query Store actually works. We are going to see the advancements made to the Query store feature in SQL Server 2022, which we are going to learn in the coming blog posts.

It is important to know what and how we setup the options and settings for the Query store. For the Azure SQL database, once the defaults are applied and enabled it is difficult to change these configuration values without effecting the Query store operations. Remember, the Query store cannot be disabled on the Azure SQL database and elastic pool.

Thanks for reading!

Resources:

Query Store Fundamentals Series: Know the basics

November 15, 2021November 15, 2021 by dbanuggets, posted in Query Store

In this blog post, we will learn the basics of the Query Store feature in SQL Server.

Introduced in SQL Server 2016, the Query Store feature will store the Query plans, Queries, resource consumption information along with the run time stats information for your queries. This feature is not automatically enabled on your databases. It is a database-level option only. You need to enable the Query Store on the database to capture the queries and query information. You cannot enable the Query store on master or tempdb databases. The information captured by the Query Store is stored in the database you enabled the feature on, in the internal tables created in the PRIMARY filegroup and this cannot be changed. (Microsoft docs reference).

Query Store feature is enabled by default if you are using Azure SQL Database and disabled by default on Azure synapse analytical database.

So, what are the advantages if we enable the Query Store feature on your database?

Very quickly identify the top resource consuming queries. Query Store captures CPU, IO, Memory, Duration, Logical Reads and Writes, Physical Reads, CLR Time, DOP, Row Count, Log Memory Used, Temp DB Memory Used, and Wait Time. You may say, I already have third party monitoring tool capturing the top resource consuming queries but look at the next several points to know Query store can offer.
Query store captures the history of the execution plans for your queries. You can easily compare the difference between the plans by placing them side by side.
If there are many execution plans generated for a query and you believe one of the plans is working better than others, you can force an execution plan of your choice with out changing any applications. This is very beneficial to quickly solve the query performance issues.
Query Store feature can be used to capture the performance baselines. Personally, I used this feature when we upgraded the databases to new versions of SQL Server.
We will be learning about the Automatic plan correction in the upcoming posts, available from SQL Server 2017 which depends on the Query Store feature that is enabled on the database. Automatic plan correction feature will monitor the regression between the execution plans and force the last good plan for your queries automatically.
If you are in Azure, Query store hints is currently in preview. Just like the Query hints, you can use the Query Store hints to a Query by using its Query_id but without modifying the underlying code. Query hints can be used by using a stored procedure sys.sp_query_store_set_hints.

Plan cache will also capture the execution plans, right? So, what is the difference?

Plan cache Vs Query store

Plan cache	Query Store
Flushed out on every server restart	Retains the data (CLEANUP_POLICY)
Stores latest plans	Collects plans over time
Evicted on memory pressure	Flush out intervals
Difficult to know why the plan changed	Useful when troubleshooting performance problems

How to enable Query Store?

You can enable it by using the GUI or using T-SQL.

Operation modes:

Read write: This mode will read the information from the Query store (reading for any forced plan information or reading information by automatic plan correction etc) and write the queries, related resource consumption information into the Query store.

Read only: This mode will only read the information from the Query store. Queries will not be captured and written to the Query Store.

Off: Query Store is in a turned-off state.

How to set what Queries can be captured?

We can set this policy by using the capture modes available:

All: Will capture every query including every execution of ad hoc queries. Remember, this can quickly fill up your query store max size if you have many ad-hoc queries running on the database. This is not a recommended capture mode.

Auto: Will does not capture the queries that are not worth consideration. The threshold of what queries are captured is decided by Microsoft based on the query execution count, compile, and runtime duration. This is the recommended capture mode from Microsoft.

None: No queries will be captured by the Query Store.

Custom: You can customize the capture policy. During the mentioned capture policy time threshold, the conditions are evaluated, and based upon that the query will be captured in the Query Store.

Conclusion: In this blog post, we have gone through the overview of what the Query store feature is and the benefits of it being enabled on your user databases, the difference between the plan cache and Query Store, different operation modes, and capture modes. In the coming part, we will learn about the query store options and how the query store works behind the scenes.

Thank you for reading!

Resources:

https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15