Automatic Plan Correction

The simple ability that Query Store offers (detailed in Chapter 6) is a way to track query and plan behavior over time. With that ability, Microsoft simply wrote functionality into SQL Server that takes advantage of that information. When you can tell that, for a given query, performance degraded after a recompile, getting the last good plan just makes sense.

While there is doubtless a lot more to this process internally, externally, that’s what’s happening.

Within SQL Server, this behavior is not on by default. Within Azure SQL Database, it is.

Tuning Recommendations

First, you have to be running SQL Server 2017 or greater, or be in Azure SQL Database. Then, Query Store must be enabled. From this point, SQL Server is already capturing the data needed to identify regressed plans.

To see this in action, we’re going to use the tables from MakeBigAdventure again (as a reminder, the script can be downloaded from here: http://dataeducation.com/thinking-big-adventure/). You may need to recreate the tables depending on which experiments you have in place from throughout the book. Listing 22-1 shows the setup needed.

CREATE INDEX ix_ActualCost ON dbo.bigTransactionHistory (ActualCost);

GO

--a simple query for the experiment

CREATE OR ALTER PROCEDURE dbo.ProductByCost

(@ActualCost MONEY)

AS

SELECT bth.ActualCost

FROM dbo.bigTransactionHistory AS bth

    JOIN dbo.bigProduct AS p

        ON p.ProductID = bth.ProductID

WHERE bth.ActualCost = @ActualCost;

GO

--ensuring that Query Store is on and has a clean data set

ALTER DATABASE AdventureWorks SET QUERY_STORE = ON;

ALTER DATABASE AdventureWorks SET QUERY_STORE CLEAR;

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

GO

Listing 22-1

Creating indexes and procedures and preparing Query Store

The following code can be affected by the Parameter Sensitive Plan Optimization, so for these tests, I’m going to disable it. With everything set up, we’re going to execute the query, using one parameter value to establish a pattern of behavior. With that in place, I’ll remove the plan from cache and then establish a new pattern of behavior, all as shown in Listing 22-2.

--establish a history of query performance

EXEC dbo.ProductByCost @ActualCost = 54.838;

GO 30

--remove the plan from cache

DECLARE @PlanHandle VARBINARY(64);

SELECT  @PlanHandle = deps.plan_handle

FROM    sys.dm_exec_procedure_stats AS deps

WHERE   deps.object_id = OBJECT_ID('dbo.ProductByCost');

IF @PlanHandle IS NOT NULL

    BEGIN

        DBCC FREEPROCCACHE(@PlanHandle);

    END

GO

--execute a query that will result in a different plan

EXEC dbo.ProductByCost @ActualCost = 0.0;

GO

--establish a new history of poor performance

EXEC dbo.ProductByCost @ActualCost = 54.838;

GO 15

Listing 22-2

Establishing two different query performance histories

I have very intentionally executed the query both 30 times in the first instance and 15 in the second. That’s because these minimums must be met. The first establishes a baseline of behavior. The second validates that performance has really degraded. After executing these, we can take a look to see if Microsoft has a recommendation. We just have to query sys.dm_db_tuning_recommendations as shown in Listing 22-3.

SELECT ddtr.TYPE,

       ddtr.reason,

       ddtr.STATE,

       ddtr.score,

       ddtr.details

FROM sys.dm_db_tuning_recommendations AS ddtr;

Listing 22-3

Retrieving tuning recommendations

The results of this query are shown in Figure 22-1.

A table displays the result of a query and has 5 columns and 1 row. The column headers are type, reason, state, score, and details, and the first entry, force last good plan is highlighted.

The type lets us know what kind of tuning opportunity this is (there will be more over time). In this case, FORCE_LAST_GOOD_PLAN. We’ll explore why that is in just a minute. The reason given is that performance went from 1.03ms to 4,607ms, a distinct degradation of performance. The state value lets us know that this is purely advisory since we haven’t yet enabled the behavior. There’s a score; higher is definitely something to pay attention to. Finally, we get the details as shown here:

{"planForceDetails":{"queryId":3,"queryHash":"0x5F791E9D3FE1F510","regressedPlanId":2,"regressedPlanHash":"0x04CB9DE3C20315A3","regressedPlanExecutionCount":15,"regressedPlanErrorCount":0,"regressedPlanCpuTimeAverage":4.607720933333334e+006,"regressedPlanCpuTimeStddev":3.505710886737305e+006,"recommendedPlanId":1,"recommendedPlanHash":"0x6F1B019D25F6B1A2","recommendedPlanExecutionCount":30,"recommendedPlanErrorCount":0,"recommendedPlanCpuTimeAverage":1.033666666666667e+003,"recommendedPlanCpuTimeStddev":1.756888031593228e+002},"implementationDetails":{"method":"TSql","script":"exec sp_query_store_force_plan @query_id = 3, @plan_id = 1"}}

Let’s unpack that information so you know what’s there in Table 22-1.

Table 22-1

Details from sys.dm_db_tuning_recommendations

planForceDetails
	queryID	3
	queryHash	0x5F791E9D3FE1F510
	regressedPlanId	2
	regressedPlanHash	0x04CB9DE3C20315A3
	regressedPlanExecutionCount	15
	regressedPlanErrorCount	0
	regressedPlanCpuTimeAverage	4.607720933333334e+006
	regressedPlanCpuTimeStddev	3.505710886737305e+006
	recommendedPlanId	1
	recommendedPlanHash	0x6F1B019D25F6B1A2
	recommendedPlanExecutionCount	30
	recommendedPlanErrorCount	0
	recommendedPlanCpuTimeAverage	1.033666666666667e+003
	recommendedPlanCpuTimeStddev	1.756888031593228e+002
implementationDetails
	method	TSql
	Script	exec sp_query_store_force_plan @query_id = 3, @plan_id = 1

We have yet to enable automated tuning, yet we could put this to work in our own systems. Not only has it identified a query that is clearly regressed based on the execution plan change, but it has provided us with the precise T-SQL to fix the situation.

The question is, how did this happen? Figure 22-2 shows the original execution plan for the first execution of the stored procedure in Listing 22-2.

A diagram of the original execution plan of the stored procedure, d b o dot product by cost.

Since the query returns only a single row for the value of 54.838, this plan makes sense. By executing it 30 times, I establish a basis of behavior for the query. Please note that 30 seems to work, but that’s not a guaranteed value. Microsoft may move that up or down as they see fit.

After the plan is removed from cache, I reexecute the query, but this time with a value of 0.0, which results in the execution plan in Figure 22-3.

A diagram of the execution plan of d b o dot product by cost, after using the value of 0.0.

Since 12 million of the 30 million-row table is returned by this query, this execution plan also makes a lot of sense. However, you can clearly see that one of these plans is optimized for a very large data set, while the other plan is optimized for a very small data set. That difference alone is why performance went from just over 1ms to 4,607ms.

We can get even more sophisticated in querying the tuning recommendations. It’s possible to pull the information from within the JSON and combine it with retrieving the execution plans as shown in Listing 22-4.

WITH DbTuneRec

AS (SELECT ddtr.reason,

           ddtr.score,

           pfd.query_id,

           pfd.regressedPlanId,

           pfd.recommendedPlanId,

           JSON_VALUE(ddtr.STATE, '$.currentValue') AS CurrentState,

           JSON_VALUE(ddtr.STATE, '$.reason') AS CurrentStateReason,

           JSON_VALUE(ddtr.details, '$.implementationDetails.script') AS ImplementationScript

    FROM sys.dm_db_tuning_recommendations AS ddtr

        CROSS APPLY

        OPENJSON(ddtr.details, '$.planForceDetails')

        WITH

        (

            query_id INT '$.queryId',

            regressedPlanId INT '$.regressedPlanId',

            recommendedPlanId INT '$.recommendedPlanId'

        ) AS pfd)

SELECT qsq.query_id,

       dtr.reason,

       dtr.score,

       dtr.CurrentState,

       dtr.CurrentStateReason,

       qsqt.query_sql_text,

       CAST(rp.query_plan AS XML) AS RegressedPlan,

       CAST(sp.query_plan AS XML) AS SuggestedPlan,

       dtr.ImplementationScript

FROM DbTuneRec AS dtr

    JOIN sys.query_store_plan AS rp

        ON rp.query_id = dtr.query_id

           AND rp.plan_id = dtr.regressedPlanId

    JOIN sys.query_store_plan AS sp

        ON sp.query_id = dtr.query_id

           AND sp.plan_id = dtr.recommendedPlanId

    JOIN sys.query_store_query AS qsq

        ON qsq.query_id = rp.query_id

    JOIN sys.query_store_query_text AS qsqt

        ON qsqt.query_text_id = qsq.query_text_id;

Listing 22-4

Combining multiple result sets to get detailed recommendations

With this information in hand, you have everything you need to make your own choice as to whether or not to use plan forcing to fix the problem. You may even decide other solutions are needed. However, these mechanisms give you a fast way to identify problematic queries that may need tuning.

The information in sys.dm_db_tuning_recommendations doesn’t survive a restart or failover of the instance. If the database goes offline, this data is also lost. You’ll need your own mechanisms to protect this data if you want it to survive a reboot.

Enabling Automatic Tuning

There are two different ways to enable automatic tuning. One is for Azure, and the other is for a SQL Server instance. You must also be running Query Store on the database in question, which is likely since Query Store is now on by default in both SQL Server 2022 and Azure. Azure supports two mechanisms: the portal and T-SQL. SQL Server requires that everything be done through T-SQL. I’ll show you the Azure portal, and then I’ll use T-SQL on SQL Server. It will work the same within Azure when using T-SQL.

Note

The Azure portal is frequently updated. Screen captures in this book may be out of date at the time you read it. You may see some difference in the graphics and layout.

Azure Portal

Connect to your Azure account and then open an Azure SQL Database to get started. The settings will be on the left side of the screen by default, with details on the right side, something like Figure 22-4.

A webpage displays the Azure S Q L database portal, where the settings on the left side have a detailed view on the right side.

You can scroll down the left side of the screen until you see Figure 22-5.

A context menu of Intelligent Performance has 4 options. The option of automatic tuning is highlighted.

Selecting “Automatic tuning” from there, or choosing the button that exists on the bottom of the page, will open the details for automatic tuning within your Azure SQL Database as shown in Figure 22-6.

A webpage displays the features of automatic tuning of the Azure S Q L database. For each option, the desired state can be switched from on, off, or inherit.

I’ve already switched my FORCE PLAN from INHERIT to ON. I then have to click on Apply at the top of the screen.

We’ll be covering the other two options later in this chapter in the “Azure SQL Database Automatic Index Management” section.

From this point forward, any tuning recommendations will be automatically applied. You can see the recommendations and any actions on the Performance Recommendations page visible in Figure 22-7.

A webpage displays the recommendation and tuning history sections of the portal.

This part of the behaviors within Azure is basically the same as on an instance of SQL Server. You can manually apply recommendations if you have the automation turned off. This information can also be lost since it’s stored in the same DMVs as a SQL Server instance.

SQL Server

SQL Server Management Studio doesn’t have anything like the Azure portal when it comes to automatic tuning. Instead, we’ll simply use the T-SQL syntax supplied in Listing 22-5.

ALTER DATABASE CURRENT SET AUTOMATIC_TUNING(FORCE_LAST_GOOD_PLAN = ON);

Listing 22-5

Enabling automatic tuning using T-SQL

To disable it, you simply run the same command to OFF. You can substitute a database name for the word CURRENT I used in the example.

Automatic Tuning in Action

With automatic tuning in place, we can simply re-run Listing 22-2 to see the plan handled automatically. However, if you’re not sure what the status of your automatic tuning is, you can query the settings using Listing 22-6.

SELECT NAME,

       desired_state,

       desired_state_desc,

       actual_state,

       actual_state_desc,

       reason,

       reason_desc

FROM sys.database_automatic_tuning_options;

Listing 22-6

Validating the status of automatic tuning

Before re-running Listing 22-2, you might want to clear both Query Store and the cache. That ensures that you’re starting from a clean slate. I only recommend that for testing and development systems. On a production system, you can pull the information for a single query from Query Store and the same with the cache.

After running Listing 22-2, I can query sys.dm_db_tuning_recommendations using the query in Listing 22-3. Figure 22-8 shows the results.

A table of results has 5 columns and 1 row, the column headers are query i d, reason, score, current state, and current state reason, the row entry that reads 3, is highlighted.

The CurrentState value is now Verifying. This means that SQL Server will continue measuring performance to validate that choosing the last plan was a good decision. If performance doesn’t improve or gets worse, then the forced plan will be removed by SQL Server, completely automatically. If there are errors or timeouts, you’ll also see the plan forcing removed. That will also mark the plan as error_prone in the sys.dm_db_tuning_recommendations.

If the server were to restart, all the information in sys.dm_db_tuning_recommendations is removed, as I’ve stated several times already. However, any plans that are forced, such as the one for dbo.ProductByCost, will remain forced. However, the verification process, if it wasn’t complete, won’t be completed.

If you look at the plan through the Query Store DMVs, reports, or even look for the Use Plan property in the execution plan, they’ll all be there as if it were a normally forced execution plan. To all respects, it is a normal forced plan.

Azure SQL Database Automatic Index Management

Azure SQL Database is defined as a Platform as a Service (PaaS). In this case, the platform on offer is a SQL Server database. This means that patching, backups, high availability, corruption, and more are all managed by Microsoft through the Azure platform. Adding in the concept of automatic index creation and index drops is perfectly in line with the idea of a PaaS offering of a database.

Since Microsoft has full control over the hardware running within their data centers in Azure, they can also put to work all the information they have with machine learning and analysis. These are incorporated into their management of Azure SQL Database in general and the Automatic Index Management in particular.

Please note that Microsoft doesn’t gather private information from your queries, data, or any of the information stored there. It simply uses the query metrics to measure behavior. You need to know this because there is still misinformation regarding this topic being passed around.

Before we continue with executing the queries, we have to finish preparing the Azure SQL Database. If you look at Figure 22-9, you can see that I’ve enabled both index creation and index dropping on my instance.

A webpage displays the 3 possible automatic tuning, force plan, create index, and drop index, with their desired states all turned on.

You also have to ensure that you have Query Store turned on. It’s on by default in Azure SQL Database, so unless you’ve explicitly turned it off, it’s available. If you’ve turned Query Store off, turn it back on.

The process does require quite a bit of information before it functions. Eventually though, you’ll see Figure 22-10.

A webpage displays a newly-made index tuning recommendation, on the recommendation's sections with a table with 3 columns, action, recommendation description, and impact.

If you look at the queries from Listing 22-7, each one has a missing index recommendation. They were intentionally written to query data not covered by a good index in order to get scans and Key Lookup operations. These are the precise kinds of queries that are covered by that somewhat mechanical performance tuning process.

We can see the details in the Azure portal as shown in Figure 22-11.

A webpage for Create index. Under the estimated impact options are impact and disk space needed, and below are the details of index name, index type, schema, table, index key columns, and included columns.

In this case, the index has not yet been created, but it soon will be. After the index is created, it goes into a process that validates that it worked. Because Microsoft can capture metrics over time, it can use that information to determine if the index worked. In this case, it will work extremely well, radically improving performance.

You can immediately apply the recommendation by clicking on the “Apply” button shown in the figure. If the index was already automatically created, you would have a “Revert” button that would drop the index. You can also review the script ahead of applying it, or if it were applied, see what script had been run.

The accuracy of the process is quite high. Since it can only create nonclustered indexes, not clustered ones, this process can’t solve all indexing issues in queries. Also, it can’t create columnstore indexes when the query pattern is better suited to that style of index.

The process can also drop indexes that you already have in place. However, it won’t drop a clustered index, ever. Also, it won’t drop unique indexes. There are many reasons for this, but one of the principal ones is that it can be quite hard to determine exactly which queries benefit from a unique index. Since the optimizer can simply use the fact that there exists a unique index as part of determining estimated row counts, it doesn’t even have to actually use the index in the plan to get a performance benefit. In short, the process errs on the side of being conservative and cautious.

Summary

As more and more people are managing larger data estates, the ability to be on top of every possible query shrinks. This is why having the ability to automatically get the last good plan can help performance. This type of problem increases on the cloud as people manage large numbers of databases. Getting the simpler aspects of query tuning automated, such as index creation, means that you can spend time on the more complex aspects. Still, as with so many other things, it’s always best to test these mechanisms thoroughly in order to understand their benefits and costs in your own system.

The next and final chapter of the book will be an exhaustive checklist of tuning recommendations: a Query Tuning Methodology.