Query Store Fundamentals: Query Store Hints

Source: Image

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:

  1. https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-hints?view=azuresqldb-current
  2. https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-query-store-set-hints-transact-sql?view=azuresqldb-current

T-SQL Tuesday #146 Preconceived Notions

Thanks to Andy Yun for hosting this month of T-SQL Tuesday. Andy’s challenge for this month of T-SQL Tuesday is to share our learnings that have changed our preconceived opinions.

There are many learnings throughout my career as a DBA but in this post, I would like to share something interesting I have learned in the recent months from Pam Lahoud about tempdb.

I always had an opinion of changing the schema of the temp tables or adding indexes later after the temp table creation doesn’t affect in any way but it’s actually not a good idea. Yes, you heard that right. Altering or adding indexes later after temp table creation within the stored procedures will be marked for deletion and so the cached temp table metadata cannot be reused. It is suggestable to create the index as you create the temp table using the inline index syntax.

Please read the full article here written by Pam Lahoud to know in detail how this best practice can help reduce the tempdb metadata contention. Pam also mentioned other best practices in a detailed in-depth presentation on tempdb internals at EightKB Conference. Here is the link to it.

Also, do not forget to read this wonderful article by Erik Darling on indexing temp tables. Erik explained in detail when can you actually be creating the indexes on temp tables by using the inline indexing syntax, when it can be useful, and cases where it is not an option.

I am looking forward to reading and learning from other SQL community members about what their preconceived notions are.

Thanks for reading!

Query Store Fundamentals: How Automatic plan correction works?

Image source: Pexels

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:

  1. https://docs.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver15
  2. https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-plan-transact-sql?view=sql-server-ver15#plan-forcing-limitations

Query Store Fundamentals: Why my plan forcing has failed?

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

T-SQL Tuesday #145: Pandemic, Presentations and Perspectives!

Thanks to Xavier Morera for hosting December month of T-SQL Tuesday!

Image

I never experienced how an in-person conference feels like. I started my speaking career during the pandemic through a virtual event New Stars of Data last year 2020.

I see different perspectives about virtual and in-person events. To be very sincere, I love virtual events for many reasons. Virtual events have given me and other new speakers a chance to speak at different conferences worldwide without having to travel.

As I never experienced an in-person event before, I am really looking forward to attending them in near future.

For this T-SQL Tuesday fun, Xavier asked a couple of questions to answer.

  1. Which is your favorite conference and why?
  2. Which is the best venue that you have visited for a tech conference?
  3. Who is the best presenter that you have ever listened to?
  4. Which location would you like for your next event to take place and why Costa Rica?

Which is your favorite conference and why?

My favorite conference will always be the one that has given me the stage and chance as a new speaker, that’s New Stars of Data.

I cannot stop with one conference 🙂 My next Favourite conference is DataWeekender which is also an online data platform event. PASS Data Community Summit 2021 and Data Platform Virtual Summit 2021 is equally my favorite one along with New stars of data and Dataweekender. To list all of my favourite conferences, the list is definitely going to be big. All the above conferences encourage diversity and give a chance for the new speakers to share the same stage as the experienced speakers. New stars of data organized by the DataGrillen team is especially dedicated to new speakers. Their support for new speakers is just incredible. DataGrillen team also organized Dativerse specially dedicated to the speakers coming from underrepresented communities. They also started Dataminutes which is a ten-minute lightning talk conference and now the New Star of Data- Speaker improvement library. DataGrillen team continuously comes up with new ideas to help support the community in every way possible.

DataWeekender team has a style that I love. I just love the way they organize, advertise the event in the most entertaining way possible with an amazing logo. They encourage new speakers from diverse backgrounds and support them.

I am thankful to PASS Summit 2021 and DPS 2021 for encouraging new speakers coming from diverse backgrounds to submit to their conferences and giving them incredible support and a chance to share the stage with very well-known speakers out there. For many new speakers, it is a dream to speak at big events. As these conferences were virtual, many professionals used this wonderful opportunity to challenge themselves and took their first step in their speaking journey. If you can count the number of new upcoming speakers from the SQL Community for the past two years, you will be truly amazed. I believe we need to keep online conferences alive even when we choose to have other in-person conferences. This will help people who cannot afford to travel to other places or countries due to various reasons like family situations/young kids/visa waiting process etc but still interested in presenting and learning from other speakers.

Which is the best venue that you have visited for a tech conference?

I never attended an in-person event before 😛

Who is the best presenter that you have ever listened to?

That’s a really tough question. Every speaker is special in their own way. I have one speaker who actually inspired me to start thinking about speaking. BrentOzar valuable suggestions from one of his podcast interviews helped me think about becoming a speaker. I cannot thank Brent enough for his continuous support of the community. Kudos to you Brent.

Which location would you like for your next event to take place and why Costa Rica?

If my first in-person event will be in Costa Rica, how amazing that would be? I mean, look at the below picture from Tortuguero (‘Land of Turtles’). Who would not love to spend their time watching sunrise and sunset here?

The picturesque beaches of Tortuguero, Costa Rica during sunset
Source: https://www.nomadicmatt.com/travel-blogs/my-favorite-destinations-in-costa-rica/

Thanks again Xavier for hosting this month of T-SQL Tuesday!

Query Store Fundamentals: Capture Performance Baselines

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.

Image: https://www.pexels.com/

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?

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:

  1. https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver15
  2. https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options?view=sql-server-ver15
  3. https://docs.microsoft.com/en-us/sql/relational-databases/performance/how-query-store-collects-data?view=sql-server-ver15

Query Store Fundamentals Series: Know the basics

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?

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.
  6. 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 cacheQuery Store
Flushed out on every server restartRetains the data (CLEANUP_POLICY)
Stores latest plansCollects plans over time
Evicted on memory pressureFlush out intervals
Difficult to know why the plan changedUseful 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

T-SQL TUESDAY #143: Short code examples

Thank you John McCormack for hosting this month of T-SQL Tuesday.

For the past couple of years as a DBA, I migrated several databases and used many handy scripts that helped me made my work easier. These scripts may be simple but if you have a migration project involving several SQL Servers with some hundreds of databases, test and production database migrations becomes tedious. I would like to share some of then here which you might already known them very well.

  1. I used this script answered by AlexK (look for the script with most accepted answer) to kill all connections to a database. I used this script in Development/Test/QA/Production during database migrations. A very handy script. I used this script while migrating 200 servers from SQL Server 2000 to SQL Server 2016.
  2. Other Script that I have used for migrations are sp_help_revlogin to transfer the logins during migrations.
  3. When using transactional replication between the servers, I used to see several distribution job failures when a new snapshot generated or during the subscriber reinitialization. The snapshot generating the .sch files for replication with ANSI_PADDING turned OFF instead of ON. I found a very handy PowerShell script answered by Asaf Mohammad in Microsoft forum which will search the .sch file having SET ANSI_PADDING OFF in all the folders and subfolders of the snapshot and replace them with SET ANSI_PADDING ON. Thanks to Asaf for saving many hours. As per the recommendation from this forum, I used this script as a second step in my snapshot agent job so this setting is fixed within the snapshot agent job. After using this PowerShell script, my distribution job worked fine.

None of the scripts I mentioned above were written by me. It is the hard work of other professionals who created these scripts and freely shared the scripts to the world helping other professionals. Kudos to all of them. There are several other scripts that I use on regular basis but I wanted to keep this post short and only mention the ones which I used frequently since past couple of years.

I am looking forward to see what other SQL family members post about their favorite handy scripts!