© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2022
G. FritcheySQL Server 2022 Query Performance Tuninghttps://doi.org/10.1007/978-1-4842-8891-7_8

8. Query Recompilation

Grant Fritchey1  
(1)
Grafton, MA, USA
 
In order to minimize the overhead from compiling execution plans, the plans are stored in a memory space called the plan cache. When prepared statements, whether as stored procedures or the other mechanisms for creating parameterized queries, are used, the process takes advantage of the plans being in cache. However, a number of things can cause plans to be removed from cache. Sometimes, this is a good thing. Either data has changed, statistics have been updated, or something has happened so a different execution plan may enhance performance. Sometimes, this is a bad thing, and you get a lot of recompiles, placing excessive load on the processor and interfering with the general good behavior of queries on the system. This chapter covers the following topics:

  • The benefits and drawbacks of recompilation

  • How to identify the statements being recompiled

  • Analyzing the causes of recompilation

  • Methods to avoid recompilation

Benefits and Drawbacks of Recompilation

Compiling and recompiling execution plans is an expensive operation. However, as data changes over time, statistics will also change. The optimizer can find better execution and processing strategies as the data distribution and statistics get updated. The addition of new indexes and constraints on a given table may also benefit from a new execution plan.

Since the recompiles are always done at the statement level instead of at the procedure, two things happen. First, you’re likely to see a higher overall recompile count than if entire procedures were recompiled. However, this is offset by the second thing, which is reduced time and processing by only recompiling the individual statement that needs it.

The standard recompile process is modified when plan forcing is enabled from the Query Store. The recompile process will still occur, but the recompiled plan will not be used. Instead, the forced plan will be substituted. In addition, if the Query Store is forcing a query hint, then that hint will be applied when the recompile process occurs. However, if the forced plan has been marked as invalid, due to structural changes or something else, then the recompiled plan will be used. For more on the Query Store, refer back to Chapter 6.

To understand how recompilation can be beneficial, I’m going to create this procedure in Listing 8-1.
CREATE OR ALTER PROCEDURE dbo.WorkOrder
AS
SELECT wo.WorkOrderID,
       wo.ProductID,
       wo.StockedQty
FROM Production.WorkOrder AS wo
WHERE wo.StockedQty
BETWEEN 500 AND 700;
Listing 8-1

Creating the dbo.WorkOrder procedure

If I execute the procedure, it results in the execution plan visible in Figure 8-1.

A screenshot represents the execution plan in the S Q L server for the d b o. work order procedure.

Figure 8-1

Execution plan with runtime metrics for the dbo.WorkOrder procedure

We’ll be talking more about indexes in Chapters 9 and 10, but suffice to say, an index scan in this scenario is less than ideal. We could improve performance by adding an index to the WorkOrder table similar to Listing 8-2.
CREATE INDEX IX_Test ON Production.WorkOrder (StockedQty, ProductID);
Listing 8-2

Creating a new index on the WorkOrder table

When the index gets created, SQL Server will automatically mark plans that reference the Production.WorkOrder table for recompile. This means the optimizer can now consider using our new index. After creating the index and rerunning the procedure, Figure 8-2 displays the resulting execution plan with runtime metrics.

A screenshot represents the resulting execution plan in the S Q L server for the d b o. work order procedure.

Figure 8-2

New execution plan for dbo.WorkOrder

The principal reason for a recompile comes after statistics get updated. SQL Server will then automatically mark queries that used those statistics as needing to be recompiled. Any time a statement gets marked, it will get recompiled at its next execution, and the application calling the query will wait until the recompile is complete.

Listing 8-3 cleans up the test index created in Listing 8-2.
DROP INDEX Production.WorkOrder.IX_Test;
Listing 8-3

Removing the new index on the WorkOrder table

You may not always generate a better plan after a recompile. Listing 8-4 shows a problematic query within a procedure, using SELECT * with no filtering criteria.
CREATE OR ALTER PROCEDURE dbo.WorkOrderAll
AS
--intentionally using SELECT * as an example
SELECT *
FROM Production.WorkOrder AS wo;
Listing 8-4

Creating the procedure dbo.WorkOrderAll

I’m going to create the procedure, but I don’t want to execute it just yet. When I do execute it, because it’s returning all rows and all columns, the best way to satisfy the query will always be a Clustered Index Scan of Production.WorkOrder (we’ll talk about why in Chapter 9). Even if I was to try adding a nonclustered index, it’s not going to help the query, and it’s going to lead to a recompile.

Before we execute the query, I’m going to set up an Extended Events session to capture recompile events as shown in Listing 8-5.
CREATE EVENT SESSION [QueryAndRecompile]
ON SERVER
    ADD EVENT sqlserver.rpc_completed
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.rpc_starting
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sp_statement_completed
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sp_statement_starting
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sql_batch_completed
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sql_batch_starting
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sql_statement_completed
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sql_statement_recompile
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks')),
    ADD EVENT sqlserver.sql_statement_starting
    (WHERE ([sqlserver].[database_name] = N'AdventureWorks'))
    ADD TARGET package0.event_file
    (SET filename = N'QueryAndRecompile')
WITH
(
    TRACK_CAUSALITY = ON
);
Listing 8-5

CREATE statement for QueryAndRecompile session

With this Extended Events session up and running, Listing 8-6 illustrates how we’re going to execute dbo.WorkOrderAll.
EXEC dbo.WorkOrderAll;
GO
CREATE INDEX IX_Test ON Production.WorkOrder(StockedQty,ProductID);
GO
EXEC dbo.WorkOrderAll; --After creation of index IX_Test
Listing 8-6

Executing the procedure and creating an index

After executing, we can see that despite the fact that no recompile is going to help this query, a recompile occurred anyway, as shown in Figure 8-3.

A screenshot depicts the recompile cause, batch text, statement, and timestamp of the s q l statement recompile.

Figure 8-3

A useless statement recompile

I’ve highlighted the recompile event so we can see why it happened. The recompile_cause column informs us that the reason was “Schema changed.” That was me creating the nonclustered index before executing the procedure again.

In this case, the recompile served no useful purpose. The execution plan is exactly the same. Because the conditions were met for a recompile, a recompiled occurred. This means extra processing was done to remove the plan from cache, create a new plan, and then add that plan to cache, all before executing the query. This is why excessive or unnecessary recompiles can be a major performance issue. Before we proceed, run Listing 8-3 again to remove the test index.

Identifying the Statement Being Recompiled

Within a large, stored procedure, consisting of a number of individual statements, knowing exactly which statements are being recompiled could be a challenge. This is why I created the Extended Events session in Listing 8-5.

The code there is taking advantage of the various events related to batch and procedure execution, individual statements in both batches and procedures, and the recompile event, and tying it all together through Causality Tracking.

Let’s look again at the output from Figure 8-3. Since I ran my stored procedure call as a batch from SSMS, the first event we see is sql_batch_starting, and you can see the batch_text shows “EXEC dbo.WorkloadAll”. Let’s walk through the rest of the events in order:
  1. 1.

    sql_batch_starting: The start of the batch process.

     
  2. 2.

    sql_statement_starting: The batch in this case consists only of the execution of the procedure, so that’s what we see here in the statement column.

     
  3. 3.

    sp_statement_starting: The procedure consists of just one statement, and now it is starting to execute. However, the plan used by that statement has been marked for recompile due to the change in the schema.

     
  4. 4.

    sql_statement_recompile: The recompile event occurs as described.

     
  5. 5.

    sp_statement_starting: Now our stored procedure statement starts again because it was interrupted by the recompile event.

     
  6. 6.

    sp_statement_completed: The stored procedure statement finishes.

     
  7. 7.

    sql_statement_completed: Now the statement in the batch, started up at #2 in this list, is completed.

     
  8. 8.

    sql_batch_completed: The batch itself can now complete.

     

You don’t have to do all of this in order to capture recompile events. You can simply capture just the event itself. In most cases, you’ll see the individual statement as part of the recompile event. However, this case illustrates an example where that’s not true. So setting up a system where we can observe the start and stop of each step allows us to better understand which individual statement is causing the recompile.

Because my system isn’t under load, the order of the timestamp values is enough to see the sequence I outlined. However, on a busy system, I would take advantage of the Causality Tracking to group and order events. All this lets me identify exactly which statement was recompiled.

Analyzing Causes of Recompilation

While a recompile can help performance by creating a more appropriate execution plan, it’s entirely possible for recompiles to become excessive and seriously negatively impact performance. Every single compile of an execution plan uses up valuable CPU time. In addition, you’re moving plans in and out of memory as a part of the process, thereby using up those resources too. When a query is recompiling, any processes that need that execution plan will be blocked until the compile process completes. This means even if you have prepared plans, a high-volume system with frequent calls to a given query can see poor performance. For all these reasons, you should know the conditions that result in recompilation. Knowing when recompiles occur can help you avoid them when they become excessive. Some of the causes for statement-level recompiles are as follows:

  • Schema changes: If a table, temporary table, or view referred to by a statement changes, including structure, metadata, and indexes, a recompile is necessary.

  • Binding changes: When a binding, like a default, of a column in a table or temporary table changes.

  • Statistics updates: Whether automatic or manual, when statistics used by a query get updated.

  • Deferred object resolution: If an object necessary for a query gets created as part of the execution of a batch, a recompile is necessary. A query can compile without an object, but then, when that object gets created, a recompile for the referencing statement(s) is required.

  • SET options: If the SET options of a given query change.

  • Sp_recompile: An explicit call to the system procedure sp_recompile will result in a recompile.

  • RECOMPILE hint: The use of the RECOMPILE query hint does what’s in the name.

  • Parameter Sensitive Plans: When a multi-plan query has one of the plans recompile or when the dispatcher plan changes.

Listing 8-7 shows how to get the complete list of causes from the Extended Events system tables.
SELECT dxmv.map_value
FROM sys.dm_xe_map_values AS dxmv
WHERE dxmv.name = 'statement_recompile_cause';
Listing 8-7

Querying sys.dm_xe_map_values

The results are shown in Figure 8-4.

A screenshot depicts the reasons for the recompilation. The schema changed reason is highlighted at the top.

Figure 8-4

All the reasons for a plan recompile

This list grows regularly as new functionality is added to SQL Server over time. We simply don’t have room to discuss every possible cause for a recompile event. Most of them are relatively self-explanatory. However, we can walk through a few examples so that you can see how to use the Extended Events to understand recompile causes.

Deferred Object Resolution

It’s very common to have a batch that dynamically creates database objects and then uses them in subsequent statements. When such a batch is executed for the first time, the initial plan won’t contain the information about the objects being created. The processing strategy is deferred until the runtime of the query. When a DML statement referring to the objects being created is executed, the query recompiles in order to generate a new plan.

Both tables and local temporary tables can be created within a batch in order to hold intermediate result sets. The recompilation of the statement because of deferred object resolution behaves differently for a table when compared to a local temporary table.

Recompilation on a Table

To see the deferred compilation in action, we’ll start with Listing 8-8.
CREATE OR ALTER PROC dbo.RecompileTable
AS
CREATE TABLE dbo.ProcTest1
(
    C1 INT
);
SELECT *
FROM dbo.ProcTest1;
DROP TABLE dbo.ProcTest1;
Listing 8-8

Stored procedure that creates a table

When the stored procedure is executed for the first time, an execution plan is generated before the actual execution of the queries. Since the table doesn’t exist in Listing 8-8, the plan that gets created won’t include the processing strategy for the SELECT statement. Therefore, a recompile is necessary, as shown in Figure 8-5.

A screenshot depicts the recompile causes of the s q l statement recompile from two executions of the procedure. It includes deferred compile and schema changed.

Figure 8-5

Multiple recompile events from two executions of the procedure

I’ve highlighted the first recompile event with the recompile_cause listed as Deferred compile. When the procedure is executed the second time, another recompile occurs. When we drop the table at the end of the batch within the stored procedure, the execution plan that was recompiled for that table doesn’t get dropped. It’s still there and can be referenced the next time the query executes. However, because the table gets recreated, SQL Server considers that a change to the schema. All of that is neatly laid out for us by the Extended Events.

Recompilation on a Temporary Table

It’s more common to create temporary tables within your batches. Listing 8-9 shows a simple query to illustrate deferred object resolution in a temporary table.
CREATE OR ALTER PROC dbo.RecompileProc
AS
CREATE TABLE #TempTable (C1 INT);
INSERT INTO #TempTable (C1)
VALUES (42);
Listing 8-9

Stored procedure that creates a temporary table

If I create and then execute this stored procedure, twice, the Extended Events output is shown in Figure 8-6.

A screenshot depicts the statement level recompile by the deferred recompile cause.

Figure 8-6

Statement-level recompile caused by deferred compilation

You can see the deferred compile explanation in the recompile_cause column. The first statement in the procedure creates a temporary table, and then the second inserts data into it. That second statement had to be deferred until the object was actually created. However, unlike the table in the example in the previous section, the temporary table being created isn’t considered a change to the schema. Therefore, no second recompile is necessary, and the execution plan is reused.

Avoiding Recompiles

I can’t emphasize enough that a recompile can be extremely beneficial for a given query. If the data has changed sufficiently, the optimizer can make better choices for a given plan. However, there are coding practices that can cause unnecessary recompiles to occur. You can also see excessive recompiles due to code changes and other events, as outlined previously. So there are practices we can follow that will help us reduce the frequency of recompiles. The following practices can help reduce the number of recompiles:

  • Avoid interleaving DDL and DML statements.

  • Reduce recompilation caused by statistics changes.

  • Use the KEEPFIXED PLAN hint.

  • Disable automatic statistics maintenance on a table.

  • Use table variables.

  • Use temporary tables across multiple scopes.

  • Avoid changing SET options within a batch.

Avoid Interleaving DDL and DML Statements

It’s a common practice to use local temporary tables with a batch or procedure. Frequently, you’ll see people modify the schema or add indexes as part of the practice. Making these changes will affect the validity of the execution plan, therefore causing a recompilation for the statements that reference those temporary tables, mostly from deferred compilation. Listing 8-10 shows a procedure that can lead to recompilations.
CREATE OR ALTER PROC dbo.TempTable
AS
--All statements are compiled initially
CREATE TABLE #MyTempTable
(
    ID INT,
    Dsc NVARCHAR(50)
);
--This statement must be recompiled
INSERT INTO #MyTempTable
(
    ID,
    Dsc
)
SELECT pm.ProductModelID,
       pm.Name
FROM Production.ProductModel AS pm;
--This statement must be recompiled
SELECT mtt.ID,
       mtt.Dsc
FROM #MyTempTable AS mtt;
CREATE CLUSTERED INDEX iTest ON #MyTempTable (ID);
--Creating index causes a recompile
SELECT mtt.ID,
       mtt.Dsc
FROM #MyTempTable AS mtt;
CREATE TABLE #t2
(
    c1 INT
);
--Recompile from a new table
SELECT c1
FROM #t2;
Listing 8-10

Procedure with interleaved DDL and DML

Because of all the interleaved structural changes, more recompiles are needed than would be necessary if we changed the code around a little. Figure 8-7 shows the Extended Events output.

A screenshot depicts the interleaved D D L and D M L changes.

Figure 8-7

Multiple recompiles from interleaved DDL and DML

It’s good to remember every statement in a procedure gets a plan initially. However, because objects are referenced that don’t exist, additional work is necessary at every execution of this procedure.

Reduce Recompilation Caused by Statistics Changes

In most cases, the changes in data over time that result in statistics updates need a new execution plan. This means the cost associated with the recompile can be beneficial overall to the system. However, in some cases, the plans may be identical because the data distribution is the same, even after a statistics update. If this were to happen frequently, the recompiles could be very painful. In the rare instance where this is the case, you have a couple of options to deal with recompiles from statistics updates:

  • Use the KEEPFIXED PLAN hint.

  • Disable the automatic statistics maintenance on a table.

Use the KEEPFIXED PLAN Hint

In the event you’d like to avoid recompiles as much as possible, you can apply a query hint, KEEPFIXED PLAN. We haven’t discussed query hints yet. In a nutshell, they’re slightly misnamed. Instead of hint, this is a commandment to the optimizer, or, in the instance of this particular hint, the SQL engine. Instead of removing this plan from cache to recompile it, the same plan will remain in place. Listing 8-11 contains the necessary code to test this hint.
IF
(
    SELECT OBJECT_ID('dbo.Test1')
) IS NOT NULL
    DROP TABLE dbo.Test1;
GO
CREATE TABLE dbo.Test1
(
    C1 INT,
    C2 CHAR(50)
);
INSERT INTO dbo.Test1
VALUES
(1, '2');
CREATE NONCLUSTERED INDEX IndexOne ON dbo.Test1 (C1);
GO
--Create a stored procedure referencing the previous table
CREATE OR ALTER PROC dbo.TestProc
AS
SELECT t.C1,
       t.C2
FROM dbo.Test1 AS t
WHERE t.C1 = 1
OPTION (KEEPFIXED PLAN);
GO
--First execution of stored procedure with 1 row in the table
EXEC dbo.TestProc; --First execution
--Add many rows to the table to cause statistics change
WITH Nums
AS (SELECT 1 AS n
    UNION ALL
    SELECT Nums.n + 1
    FROM Nums
    WHERE Nums.n < 1000)
INSERT INTO dbo.Test1
(
    C1,
    C2
)
SELECT 1,
       Nums.n
FROM Nums
OPTION (MAXRECURSION 1000);
GO
--Reexecute the stored procedure with a change in statistics
EXEC dbo.TestProc;
Listing 8-11

Script to create a table and procedure for query hint

The results of the Extended Events are visible in Figure 8-8.

A screenshot depicts the results of the extended events.

Figure 8-8

Results of the KEEPFIXED PLAN query hint

You can see that the procedure executed the first time and didn’t have a recompile. Then, the script in Listing 8-11 modified the data sufficiently that a recompile would absolutely be necessary under normal circumstances. However, you can see that we didn’t get a recompile on the second execution of the procedure, despite the change in data and statistics. This is due to the KEEPFIXED PLAN hint.

You do see one recompile, but that was from the work done to load the table with data, not the result of the statistics update.

Caution

Using any query hint should only come after extensive testing has proven that it is the best solution. KEEPFIXED PLAN could result in a poor execution plan being kept on the system when a better one would result in superior performance.

Another query hint that might be useful here is KEEP PLAN. This hint is specific to temporary tables. It will keep the plan in place until the 500-row threshold is met for statistics updates. It can help reduce the amount of recompiles you see when using temporary tables. It does come with the same caveats though.

Disable Automatic Statistics Maintenance on a Table

You have the option of disabling statistics updates, either for the database entirely or for individual tables. Listing 8-12 shows how to turn off a table’s statistics maintenance.
EXEC sys.sp_autostats 'dbo.Test1', 'OFF';
Listing 8-12

Disabling automatic statistics maintenance on one table

Now, regardless of data changes, statistics are not updated on this table. That means that no queries will be marked for recompile because of changes in data.

Once again, we are in a situation where this could prove extremely problematic. Extensive testing to validate that this won’t hurt other queries should be undertaken before implementing this. Also, if you do choose to disable the automatic statistics updates, you should plan to have a manual process to update those statistics and then deal with the recompile in a more controlled manner.

Use Table Variables

Temporary tables and table variables are almost identical in every regard. However, table variables have one singular distinction: they do not have statistics. Because table variables do not have statistics, they don’t suffer from some recompiles that do occur on temporary tables. If we look at Listing 8-13, we can see an example with a temporary table.
CREATE TABLE #TempTable
(
    C1 INT PRIMARY KEY
);
SET @Count = 1;
WHILE @Count < 8
BEGIN
    INSERT INTO #TempTable
    (
        C1
    )
    VALUES
    (@Count );
    SELECT tt.C1
    FROM #TempTable AS tt
       JOIN Production.ProductModel AS pm
       ON pm.ProductModelID = tt.C1
       WHERE tt.C1 < @Count;
    SET @Count += 1;
END;
DROP TABLE #TempTable;
Listing 8-13

Updating a temporary table

Figure 8-9 has part of the output from the execution of Listing 8-13.

A screenshot depicts the recompile caused by the statistics change.

Figure 8-9

A recompile caused by a statistics change

Since the threshold of six rows has been exceeded on the temporary table, a recompile has occurred. We can change this code to what we see in Listing 8-14.
DECLARE @TempTable TABLE
(
    C1 INT PRIMARY KEY
);
DECLARE @Count TINYINT = 1;
WHILE @Count < 8
BEGIN
    INSERT INTO @TempTable
    (
        C1
    )
    VALUES
    (@Count );
    SELECT tt.C1
    FROM @TempTable AS tt
       JOIN Production.ProductModel AS pm
       ON pm.ProductModelID = tt.C1
       WHERE tt.C1 < @Count;
    SET @Count += 1;
END;
Listing 8-14

Updating a table variable

In this case, there is no statistics update. I’m not going to display the Extended Events output here because there is nothing to show. No recompile occurs in this instance.

For very small data sets like this one, table variables are preferable because the lack of statistics avoids recompiles. As the data set grows, however, statistics become helpful to the optimizer, and temporary tables will perform better, despite the recompile.

Use Temporary Tables Across Multiple Scopes

You can declare a temporary table in one procedure and then use that same temporary table in a second procedure, called by the first procedure. Prior to SQL Server 2019, and outside of Azure SQL Database, this resulted in a recompile, every single time the query was called. However, new changes to the engine mean you won’t see those recompiles.

Listing 8-15 shows an example of this in action.
CREATE OR ALTER PROC dbo.OuterProc
AS
CREATE TABLE #Scope
(ID INT PRIMARY KEY,
ScopeName VARCHAR(50));
EXEC dbo.InnerProc
GO
CREATE OR ALTER PROC dbo.InnerProc
AS
INSERT INTO #Scope
(
    ID,
    ScopeName
)
VALUES
(   1,   -- ID - int
    'InnerProc' -- ScopeName - varchar(50)
    );
SELECT s.ScopeName
FROM #Scope AS s;
GO
Listing 8-15

Nested procedures to avoid recompiles

Creating a procedure like dbo.OuterProc, you will receive a warning that it has a dependency on dbo.InnerProc. However, both procedures can be created, and then, when the outer procedure gets executed, you won’t see recompiles from the inner procedure.

Avoid Changing SET Options Within a Batch

Changing the environment settings while executing a procedure will lead directly to recompiles. For ANSI compatibility, it’s generally recommended that you keep the following SET options ON:

  • ARITHABORT

  • CONCAT_NULL_YIELDS_NULL

  • QUOTED_IDENTIFIER

  • ANSI_NULLS

  • ANSI_PADDING

  • ANSI_WARNINGS

  • NUMERIC_ROUNDABORT should be set to OFF

Listing 8-16 shows code that doesn’t follow these guidelines.
CREATE OR ALTER PROC dbo.TestProc
AS
SELECT 'a' + NULL + 'b'; --1st
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'a' + NULL + 'b'; --2nd
SET ANSI_NULLS OFF;
SELECT 'a' + NULL + 'b';--3rd
GO
EXEC dbo.TestProc; --First execution
EXEC dbo.TestProc; --Second execution
Listing 8-16

Changing environment settings within a procedure

Some of the results from the Extended Events are visible in Figure 8-10.

A screenshot depicts the results. It includes name, timestamp, recompile cause, batch text, and statement.

Figure 8-10

Recompiles caused by SET option changes

The first execution of the queries results in recompiles for the statements after the SET option changes. However, you’ll note that the second execution doesn’t show any recompile. That’s because the SET options are now a part of the execution plan, so no further recompiles are necessary. However, for identical queries, there are now three plans in cache.

Worth noting, changing the SET NOCOUNT environment setting doesn’t cause recompiles.

Controlling Recompile Results

In the previous section, we went over several ways you can attempt to reduce the number of recompiles. However, sometimes, the recompile is unavoidable. In that case, you need to have some mechanisms to control the output of the recompile. You have four options:

  • Plan forcing

  • Plan guides

  • Query hints

  • Hint forcing

Plan Forcing

We already talked a little about plan forcing in Chapter 6 when we talked about the Query Store. We’ll cover it some more in Chapter 14 when we talk about dealing with parameter-sensitive execution plans. However, it’s worth a little detail right here in this chapter too.

Plan forcing will not prevent a recompile from occurring. Should any of the listed criteria be met, the plan will recompile. However, plan forcing allows you to control the outcome of the recompile. Instead of an all new plan, you’ll get the plan chosen for forcing. This does assume that the plan was not rendered invalid due to code or structure changes. Otherwise, this is one way to control the results of a recompile.

Query Hints

I introduced the concept of query hints earlier in the chapter when we used KEEPFIXED PLAN to eliminate recompiles and KEEP PLAN to reduce the occurrence of recompiles for temporary tables. Many of the query hints available are directly related to forcing choices on the optimizer. We’ll address a number of different hints throughout the book. However, there’s one in particular I’d like to bring up here: OPTIMIZE FOR.

The OPTIMIZE FOR hint allows you to control the values of parameters used during the compile process. You can use OPTIMIZE FOR with a specified value for a parameter to get a precise plan. You can also use OPTIMIZE FOR UNKNOWN to get a more generic plan. We’ll cover these hints further in Chapter 14, but let’s look at an example right now in Listing 8-17.
CREATE OR ALTER PROCEDURE dbo.CustomerList @CustomerID INT
AS
SELECT soh.SalesOrderNumber,
       soh.OrderDate,
       sod.OrderQty,
       sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID >= @CustomerID
OPTION (OPTIMIZE FOR (@CustomerID = 1));
Listing 8-17

A parameter-sensitive stored procedure with a query hint

Using a query hint like I do in Listing 8-17 does require that the code gets modified. There are possible ways around that, discussed in the next two sections. However, regardless of the recompile process, the query in this procedure will always get one plan, based on the value provided to @CustomerID within the OPTIMIZE FOR hint.

We can see it in action if we run the queries in Listing 8-18.
EXEC dbo.CustomerList @CustomerID = 7920 WITH RECOMPILE;
EXEC dbo.CustomerList @CustomerID = 30118 WITH RECOMPILE;
Listing 8-18

Executing the CustomerList procedure with different values

Executing the procedure while supplying WITH RECOMPILE will ensure that the optimizer is forced to recompile the entire procedure. The execution plans from each of these values are here in Figure 8-11.

A screenshot represents the execution plans for query 1 and query 2 at the top and bottom.

Figure 8-11

Two identical execution plans

You can see that both execution plans are the same. However, since I have runtime metrics, you can see substantial differences that would have, under other circumstances, resulted in different execution plans. The first query shows that it’s returning 121,317 rows out of 121,317 rows expected. In short, that plan is correct for the parameter passed in. However, the second plan is returning only 289 rows. That suggests pretty strongly a different execution plan could have been used for the second parameter value.

We can see evidence of the work done by the query hint within the execution plan. If you look at the properties of the first operator, in the second execution plan, you’ll see the Parameter Compiled Value in Figure 8-12.

A screenshot of the parameter list and the customer I D.

Figure 8-12

Parameter list from the first operator

So while the query was executed with the value of 30118, the compile value was 1. This, despite the fact that the query was recompiled as it was executed.

Plan Guides

In the previous section, we used a stored procedure with a query hint added to it. What if you wanted to try to use a query hint, but you really didn’t want to modify the code? Plan guides can sometimes do that job for us. Listing 8-19 shows the procedure and query without the query hint.
CREATE OR ALTER PROCEDURE dbo.CustomerList @CustomerID INT
AS
SELECT soh.SalesOrderNumber,
       soh.OrderDate,
       sod.OrderQty,
       sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID >= @CustomerID;
Listing 8-19

The CustomerList procedure without the query hint

If I decided that I need to add a query hint, but for whatever reason, I can’t modify the code, I’m going to create a plan guide as you see in Listing 8-20.
sp_create_plan_guide @name = N'MyGuide',
                     @stmt = N'SELECT soh.SalesOrderNumber,
       soh.OrderDate,
       sod.OrderQty,
       sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID >= @CustomerID;',
                     @type = N'OBJECT',
                     @module_or_batch = N'dbo.CustomerList',
                     @params = NULL,
                     @hints = N'OPTION (OPTIMIZE FOR (@CustomerID = 1))';
Listing 8-20

Creating a plan guide

You’ll notice that I copied the formatting, as well as the text, of the query. That’s because in order to get plan guides to work, you have to match the white space exactly. However, with that plan guide in place, if I were to execute the procedure again, as I did in Listing 8-18, the execution plans will be identical again. We can see whether or not the guide was used a couple of ways. There is an event in Extended Events. You can also look directly at the execution plan properties in Figure 8-13.

A screenshot of the parameter list and the customer I D.

Figure 8-13

The PlanGuideName property in an execution plan

The property PlanGuideName is added to an execution plan, in the properties of the first operator, only when a plan guide is used. You can see this one right there, by name, MyGuide.

Listing 8-20 represents an object plan guide. This means the plan guide itself is associated only with the defined module, CustomerList. You can also create a plan guide just for queries, like ad hoc batches. This is known as a SQL plan guide. Just like with the object plan guide, you have to get the white space and everything just right.

Listing 8-21 shows an ad hoc query version of the same code we’ve been using.
SELECT soh.SalesOrderNumber,
       soh.OrderDate,
       sod.OrderQty,
       sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID >= 1;
Listing 8-21

An ad hoc query in need of a query hint

If we wanted to take control of the recompile to force a particular behavior, we could create a SQL plan guide like Listing 8-22.
EXECUTE sp_create_plan_guide @name = N'MyGoodSQLGuide',
                             @stmt = N'SELECT soh.SalesOrderNumber,
       soh.OrderDate,
       sod.OrderQty,
       sod.LineTotal
FROM Sales.SalesOrderHeader AS soh
    JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.CustomerID >= 1;',
                             @type = N'SQL',
                             @module_or_batch = NULL,
                             @params = NULL,
                             @hints = N'OPTION  (TABLE HINT(soh,  FORCESEEK))';
Listing 8-22

Forcing an index seek through a plan guide

Executing the query from Listing 8-21 results in the execution plan shown in Figure 8-14 (broken in two, top to bottom, for visibility).

A screenshot depicts the execution plan with a query hint through the plan guide.

Figure 8-14

The execution plan after applying a hint through a plan guide

That’s a radically different execution plan, despite compiling from a value that should result in the plan in Figure 8-11.

One other method for controlling the results of a recompile is possible. To see this in action, I’m first going to clear the cache to ensure there are no plans at all. Second, I’m going to remove the plan guides we had created using Listing 8-23.
EXECUTE sp_control_plan_guide @operation = 'Drop', @name = N'MySQLGuide';
EXECUTE sp_control_plan_guide @operation = 'Drop', @name = N'MyGuide';
Listing 8-23

Removing plan guides

With that done, I’m going to go back and execute the query in Listing 8-21. That will mean that the original plan is now in cache. I can now take advantage of one more plan guide mechanism, creating a plan guide from an execution plan. Listing 8-24 shows how to get this done.
DECLARE @plan_handle VARBINARY(64),
        @start_offset INT;
SELECT @plan_handle = deqs.plan_handle,
       @start_offset = deqs.statement_start_offset
FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(sql_handle)
    CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle, deqs.statement_start_offset, deqs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT soh.SalesOrderNumber%';
EXECUTE sp_create_plan_guide_from_handle
@name = N'ForcedPlanGuide',
      @plan_handle = @plan_handle,
      @statement_start_offset = @start_offset;
Listing 8-24

Using sp_create_plan_guide_from _handle

This is very similar to plan forcing but clearly involves quite a few more steps. However, it is a way to control the output of a recompile.

I want to emphasize that using plan guides can be a dangerous approach to fixing problems. As data changes over time, the plans that were working well for you may no longer be. If you do choose to implement plan guides, ensure you have a regularly scheduled reassessment of whether or not they are appropriate.

Hint Forcing

In Azure SQL Database and SQL Server 2022, you have one final option for controlling the output of a recompile: hint forcing. We covered how this works in Chapter 6.

Summary

Most of the time, recompiles are helping us. Data, structures, or code has changed, and a fresh execution plan will help enhance performance. However, as we discussed throughout the chapter, this isn’t always the case. Extended Events are a great way to understand why a given statement is being recompiled. You now have several methods to reduce the volume of recompiles you experience on your system. Finally, we walked through several ways to control the results of the recompile. With your ability to understand why recompiles are occurring, it will make it possible to avoid unnecessary recompiles.

The last several chapters were all about the internals of how SQL Server generates and manages execution plans. The next several chapters are going to talk about specific database structures that can hurt, or help, your performance. We’re going to start with the most important: indexes.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.138.120.116