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:
Deepthi Goguri's SQL Server Blog 
One thought on “Query Store Fundamentals: Capture Performance Baselines”