© 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_13

13. Parameter-Sensitive Queries: Causes and Solutions

Grant Fritchey1  
(1)
Grafton, MA, USA
 

Stored procedures and prepared statements can use parameters as placeholders for values. The optimizer can use the values in the parameter when the plans compile to get specific statistics, commonly known as parameter sniffing. Most of the time, this enhances performance. However, some queries suffer poor performance because they are very sensitive to exactly which values are used to create the plan. These are known as parameter-sensitive queries. Dealing with these queries can be a challenge.

In this chapter, I’ll cover the following topics:

  • How does parameter sniffing work?

  • Identifying queries that are sensitive to parameter values

  • Mechanisms for addressing parameter-sensitive queries

How Does Parameter Sniffing Work?

Parameter sniffing is a very simple concept. When you have a stored procedure, or other prepared statement with a parameter, the literal values passed to that parameter are available to the query optimizer when it compiles the query. That’s it. The query optimizer can access those values and then use the exact value passed to the parameter to look up row estimates in the statistics of the table being referenced. Because of this, in many cases, you get execution plans that more accurately reflect the data because an actual value is used. If no actual value was used, an average would be used instead.

The first thing to note is that while parameters and variables look similar within a given T-SQL statement where they are consumed, they are not the same. The definition of a parameter is part of the definition of the prepared statement. The definition of a variable is instead within the T-SQL of a batch or procedure. That difference matters. For example, the following code snippet shows the definition of a pair of parameters:
CREATE PROCEDURE dbo.AddressList
(
    @City VARCHAR(30),
    @PostalCode VARCHAR(15)
)
AS...
while the following snippet is the definition of a variable:
DECLARE @City VARCHAR(30);

Because a variable is defined within the code, it’s not possible for the optimizer to know the value in the variable, so it’s not sniffed automatically the way parameters are. However, in the event of a statement-level recompile, things change. During the recompile, any variables used in the statement being recompiled have known values, and the optimizer will take advantage of that in order to get more accurate row estimates.

To see parameter sniffing in action, we’re going to use the stored procedure in Listing 13-1.
CREATE OR ALTER PROC dbo.ProductTransactionHistoryByReference
(@ReferenceOrderID INT)
AS
BEGIN
    SELECT p.NAME,
           p.ProductNumber,
           th.ReferenceOrderID
    FROM Production.Product AS p
        JOIN Production.TransactionHistory AS th
            ON th.ProductID = p.ProductID
    WHERE th.ReferenceOrderID = @ReferenceOrderID;
END;
Listing 13-1

A stored procedure with a parameter

There’s nothing special or unusual about the query inside the stored procedure in Listing 13-1. What’s actually special is the distribution of data for the ReferenceOrderID column in the Production.TransactionHistory table. Some values return a large number of rows and work best with one plan, while other values return very few rows and work better with a different plan.

To see how parameter sniffing works, I’ll run the query in Listing 13-2.
EXEC dbo.ProductTransactionHistoryByReference @ReferenceOrderID = 53465;
Listing 13-2

Executing the ProductTransactionHistoryByReference query

The query has the following execution plan and runtime metrics:
1.2ms
245 reads

A diagram of the execution plan illustrates the connection of index seek and key lookup with nested loops, then sort. Sort and clustered index seek flows to merge join, then select with a cost of 0.

Figure 13-1

Execution plan for a value returning more rows

There’s nothing remarkable about the execution plan. Based on the value passed to the parameter, the optimizer chose to scan the Product tables clustered index, PK_Product_ProductID. Also, it decided that the most efficient join would be the Merge. While the Clustered Index Scan was ordered (which you can verify by looking at the properties of that operator), a Sort operator was necessary to order the output from the Nested Loops join. All this took just over a millisecond and had 245 reads.

I’ll remove the plan from cache using the query in Listing 13-3.
DECLARE @planhandle VARBINARY(64);
SELECT @planhandle = deps.plan_handle FROM sys.dm_exec_procedure_stats AS deps
WHERE deps.object_id = OBJECT_ID('dbo.ProductTransactionHistoryByReference')
IF @planhandle IS NOT NULL
DBCC FREEPROCCACHE(@planhandle);
Listing 13-3

Removing a single plan from cache

With that removed, I can execute the procedure with a different value, 816. This results in the plan in Figure 13-2.

A diagram of the execution plan connects index seek and key lookup with nested loops. Then nested loops and clustered index seek flows to nested loops, then select with a cost of 0.

Figure 13-2

A different execution plan for a different value

The plan has changed from the one shown in Figure 13-1. Because fewer rows are returned, a scan is not needed. The optimizer chose to use a Nested Loops join instead of a Merge, so no sort operations are needed either. This change in execution plans is the result of parameter sniffing. Specific values are used to look at the statistics, and then plans are chosen based on those values.

If I run the query outside the stored procedure, using a local variable, the code will look like Listing 13-4.
DECLARE @ReferenceOrderID INT = 53465;
SELECT p.Name,
       p.ProductNumber,
       th.ReferenceOrderID
FROM Production.Product AS p
    JOIN Production.TransactionHistory AS th
        ON th.ProductID = p.ProductID
WHERE th.ReferenceOrderID = @ReferenceOrderID;
Listing 13-4

Executing the same query with a variable

This results in the following execution plan in Figure 13-3 and these runtime metrics:
975mcs
362 reads

A diagram of the execution plan connects index seek and key lookup with nested loops. Then nested loops and clustered index seek flows to nested loops, then select with a cost of 0.

Figure 13-3

An average execution plan

The first thing we should address is that the query ran a little faster on average, by about 30 microseconds on my machine. However, the reads went up from 245 to 362. That could be OK or a serious problem depending on the system and where any bottlenecks may be.

Next, note the actual rows, 72 for all the operators, vs. the estimated rows, 3. It’s that difference in the estimated values that results in differences in the execution plans. For larger data sets, the plan shown in Figure 13-1 performs nominally better (at least for the reads). For the average number of rows across the entire data set, it matches the plan shown in Figure 13-2, which performs better for smaller data sets.

To see the estimations go the other way. I’m going to perform the following steps:
  1. 1.

    Run Listing 13-3 to remove the plan from cache.

     
  2. 2.

    Run Listing 13-2 with the larger data set in order to generate that plan.

     
  3. 3.

    Execute the procedure again with the value 816 and measure performance.

     
  4. 4.

    Run Listing 13-3 again to remove the plan from cache.

     
  5. 5.

    Execute the procedure a final time with the value 816 and measure performance.

     
The results are shown in Table 13-1.
Table 13-1

Query performance metrics for two plans

Parameter Values

Execution Time

Reads

53465 (72 rows)

483mcs

11

816 (2 rows)

378mcs

7

As you can see, both the reads and the execution time degrade when the smaller data set uses the plan created for the larger data set. This is the problem with bad parameter sniffing.

You have to decide which plan is better overall for your systems, and it’s not always going to be obvious. Let’s discuss how to identify the problematic queries.

Identifying Queries That Are Sensitive to Parameter Values

Encountering bad parameter sniffing can be a very frustrating experience. The problem is almost always an intermittent one. Performance will be fine for a long period of time, and then suddenly performance will degrade badly. Then, without any action from you, it may suddenly go back to performing just fine. This is occurring as plans age out of cache or are updated through other processes (discussed in Chapters 7 and 8) and new plans, possibly not the best for the majority of your queries, take their place.

Presumably you’re going to be able to identify which query is causing this problem. Then, it’s a question of getting the execution plans for the query. We need to see the execution plans in order to determine if the intermittent slow performance is caused by changes in the plan, or has other causes. You have three choices in capturing plans.

One way to do this would be to capture the plan when behavior is good and again when behavior is bad, using the plan cache and sys.dm_exec_query_plan like in Listing 13-5.
SELECT deps.EXECUTION_COUNT,
       deps.total_elapsed_time,
       deps.total_logical_reads,
       deps.total_logical_writes,
       deqp.query_plan
FROM sys.dm_exec_procedure_stats AS deps
    CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
WHERE deps.OBJECT_ID = OBJECT_ID('dbo.ProductTransactionHistoryByReference');
Listing 13-5

Using sys.dm_exec_query_plan to retrieve execution plans

Just remember that this method is cache dependent, so you have to capture the plans while they are still in cache.

Another approach would be to use the Query Store, if that’s enabled on the database in question. The nice thing about the Query Store is that it will automatically have all the plans associated with a given query. Listing 13-6 shows how I can aggregate the aggregations from Query Store to get the execution plans.
SELECT SUM(qsrs.count_executions) AS ExecutionCount,
       AVG(qsrs.avg_duration) AS AvgDuration,
       AVG(qsrs.avg_logical_io_reads) AS AvgReads,
       AVG(qsrs.avg_logical_io_writes) AS AvgWrites,
       CAST(qsp.query_plan AS XML) AS QueryPlan,
       qsp.query_id,
       qsp.plan_id
FROM sys.query_store_query AS qsq
    JOIN sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
    JOIN sys.query_store_runtime_stats AS qsrs
        ON qsrs.plan_id = qsp.plan_id
WHERE qsq.OBJECT_ID = OBJECT_ID('dbo.ProductTransactionHistoryByReference')
GROUP BY qsp.query_plan,
         qsp.query_id,
         qsp.plan_id;
Listing 13-6

Using Query Store to retrieve execution plans

I’ve used the CAST to change the query_plan value to XML from the text that it’s stored as. For very large execution plans, this might not work because of the nesting level of XML. However, I do this because it makes it possible to click on the column in SSMS and open the graphical plan (sadly, in Azure Data Studio, it will only open the XML as of this writing).

Finally, you can use Extended Events to capture the execution plans. I’ll show you how to do this, but I would suggest caution because this is an expensive Extended Events session and could put an undo load on your system. I’d suggest only running this for very short periods of time. Listing 13-7 shows the necessary session.
CREATE EVENT SESSION [ExecutionPlans]
ON SERVER
    ADD EVENT sqlserver.query_post_execution_showplan
    (WHERE (
               [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'AdventureWorks')
               AND [object_name] = N'ProductTransactionHistoryByReference'
           )
    ),
    ADD EVENT sqlserver.rpc_completed
    (WHERE (
               [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'AdventureWorks')
               AND [object_name] = N'ProductTransactionHistoryByReference'
           )
    )
    ADD TARGET package0.event_file
    (SET FILENAME = N'ExecutionPlans')
WITH
(
    TRACK_CAUSALITY = ON
);
Listing 13-7

An Extended Events session to capture execution plans

Even with good filtering in place, this Event can be extremely expensive. If your system is already under stress, you may not want to use this approach. However, it really does make it easy to capture both the runtime metrics and the execution plan.

The goal, once again, is to have the two (or more) execution plans generated for the query. Since this is an intermittent problem, you may want to save one of the plans to disk until you get the second. Right-click inside any graphical plan and select “Save execution plan as…” from the context menu. As I said before, it’s likely that Query Store will just have both plans available.

The target in the plan is the compile time and runtime values for the parameter, which is stored in the first operator of an execution plan. Right-click the operator and select Properties from the context menu. I usually just leave the properties window open in SSMS when I’m tuning queries. In ADS, you’ll have to reopen the properties window each time you open a plan. Figure 13-4 shows the Parameter List properties for the plan in question.

A table with 2 columns and 4 rows. Column headers are the parameter list, and reference order I D. The row entries are column with reference order I D, parameter compiled value as 816, parameter data type as int, and parameter runtime value as 53465.

Figure 13-4

Parameter List values for the query

Now I have actual data that I can use to understand what’s happening with our query. The key to understanding why the optimizer is making these choices is in the statistics. You could run DBCC SHOW_STATISTICS and find the values using the RANGE_HI_KEY column and then determine if our value matches, where we’d use RANGE_ROWS, or was simply within a range, where we’d use AVG_RANGE_ROWS. Instead, I prefer to just query that information directly from sys.dm_db_stats_histogram as shown in Listing 13-8.
DECLARE @KeyValue INT = 53465;
WITH histolow
AS (SELECT ddsh.step_number,
           ddsh.range_high_key,
           ddsh.range_rows,
           ddsh.equal_rows,
           ddsh.average_range_rows
    FROM sys.dm_db_stats_histogram(OBJECT_ID('Production.TransactionHistory'), 3) AS ddsh ),
     histojoin
AS (SELECT h1.step_number,
           h1.range_high_key,
           h2.range_high_key AS range_high_key_step1,
           h1.range_rows,
           h1.equal_rows,
           h1.average_range_rows
    FROM histolow AS h1
        LEFT JOIN histolow AS h2
            ON h1.step_number = h2.step_number + 1)
SELECT hj.range_high_key,
       hj.equal_rows,
       hj.average_range_rows
FROM histojoin AS hj
WHERE hj.range_high_key >= @KeyValue
      AND
      (
          hj.range_high_key_step1 < @KeyValue
          OR hj.range_high_key_step1 IS NULL
      );
Listing 13-8

Identifying statistics information through a query

This will show us the equivalent rows and the average number of rows within the histogram inside the statistics. You have to supply the appropriate OBJECT_ID and the correct statistics ID, both available from the sys.stats system table. Then set each of the two values, compiled and runtime, to the @KeyValue variable. The results for both queries are shown in Figure 13-5.

Two sets of histogram results for two values. A table with a row and 3 columns. The values are as follows. Range high key, 1255 and 53465. Equal rows, 24 and 72. Average range rows, 2.266428 and 30.

Figure 13-5

Histogram results for two different values

The information in Figure 13-5 tells us what we need to know. For our compiled value of 816, it’s not equal to the range_high_key, so you have to look to the average_range_rows, which shows 2.266 rows average across that step in the histogram, whereas the runtime value, 53465, is equal to the range_high_key. That means it will return exactly 72 rows. The difference between 72 rows and 2.266 rows results in two different execution plans.

We now know where the different plans came from. We can also see how the plans affect performance for the different queries. The last step is deciding how to best address the, in this case, bad parameter sniffing.

Mechanisms for Addressing Plan-Sensitive Queries

There are a number of different ways you can try to address bad parameter sniffing. Each of the methods requires you to make a determination as to what kind of solution you’re going to apply. With the exception of the new Parameter Sensitive Plan Optimization, the last mechanism we’ll examine, each mechanism for dealing with bad parameter sniffing requires a trade-off. I’ll discuss the trade-offs as I detail each mechanism. The options for dealing with bad parameter sniffing are as follows:

  • Disable parameter sniffing entirely.

  • Use a local variable in place of a parameter.

  • Recompile the query every time it executes.

  • Use the query hint OPTIMIZE FOR.

  • Use the force plan functionality of Query Store.

  • If your query meets the thresholds, automatically get Parameter Sensitive Plan Optimization.

With each of these approaches, you will need to return to the queries over time. As your data changes, what was a good solution could turn into a problem, so keep that in mind as you apply any of these fixes. Let’s discuss these mechanisms and their trade-offs.

Disable Parameter Sniffing

While most of the time, parameter sniffing is either benign or beneficial, it really can be problematic. There are two mechanisms that would allow you to disable parameter sniffing entirely. The first is a server-level setting using a trace flag. Listing 13-9 shows how to do this.
DBCC TRACEON (4136,-1);
Listing 13-9

Enabling trace flag

The problem with this approach is that it disables parameter sniffing for the entire server. While you may have a database that is suffering from bad parameter sniffing, seldom will all databases be in the same situation.

Alternatively, you can take advantage of Database Scoped Configuration (2016 and greater as well as Azure SQL Database) to disable parameter sniffing for a single database as shown in Listing 13-10.
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
Listing 13-10

Using Database Scoped Configuration

Using this mechanism allows you a more targeted approach to disable parameter sniffing, but you do lose any advantages that parameter sniffing confers on your database.

One more option is to take advantage of the USE HINT query hint to disable parameter sniffing on a single query. Listing 13-11 shows how this works.
CREATE OR ALTER PROC dbo.ProductTransactionHistoryByReference
(@ReferenceOrderID INT)
AS
BEGIN
    SELECT p.NAME,
           p.ProductNumber,
           th.ReferenceOrderID
    FROM Production.Product AS p
        JOIN Production.TransactionHistory AS th
            ON th.ProductID = p.ProductID
    WHERE th.ReferenceOrderID = @ReferenceOrderID
    OPTION (USE HINT ('DISABLE_PARAMETER_SNIFFING'));
END;
Listing 13-11

Disabling parameter sniffing for one query

This gives you the ability to control parameter sniffing at an even more granular level than the entire server or the entire database (SQL Server 2016 and greater). You can combine this with Query Store hints (covered in Chapter 6) or plan guides (covered in Chapter 8), so you don’t have to modify the code. However, I usually find one of the other methods serves me better most of the time.

Local Variables

Way back in SQL Server 2000 and before, there were almost no ways to deal with bad parameter sniffing. Because of this, in some situations, you could use a local variable in place of a parameter. This means that you will get an average value from the histogram, instead of a specific value. Listing 13-12 shows how this would work inside a stored procedure.
CREATE OR ALTER PROC dbo.AddressByCity
(@City VARCHAR(30))
AS
BEGIN
    DECLARE @LocalCity VARCHAR(30) = @City;
    SELECT A.AddressID,
           A.PostalCode,
           sp.NAME,
           A.City
    FROM Person.ADDRESS AS A
        JOIN Person.StateProvince AS sp
            ON sp.StateProvinceID = A.StateProvinceID
    WHERE A.City = @LocalCity;
END;
Listing 13-12

Using a local variable to address parameter sniffing

As you are no doubt thinking, that looks odd. However, you may come across this in older code. Between more methods for dealing with bad parameter sniffing and the fact that this will be sampled in the same way during a recompile, it’s unlikely you’ll see this much. I don’t recommend this approach.

Recompile

Since parameter sniffing occurs when you compile a plan, if you want to get a specific plan for each possible value, you can use a query hint to recompile the statement within your procedure. Listing 13-13 shows an example of how this would work.
CREATE OR ALTER PROC dbo.ProductTransactionHistoryByReference
(@ReferenceOrderID INT)
AS
BEGIN
    SELECT p.NAME,
           p.ProductNumber,
           th.ReferenceOrderID
    FROM Production.Product AS p
        JOIN Production.TransactionHistory AS th
            ON th.ProductID = p.ProductID
    WHERE th.ReferenceOrderID = @ReferenceOrderID
        OPTION(RECOMPILE);
END;
Listing 13-13

Recompiling a statement at each execution

Now every time this stored procedure gets executed, the statement within will be recompiled. This means that when the parameter values are sampled, the plan generated each time will be based on that sampled value. This does address the issues caused by bad parameter sniffing. However, you now have to pay the added overhead of compiling the plan on each execution. Further, any query with a RECOMPILE hint will not have its plan stored in cache. While this approach does solve the bad parameter sniffing issue, you are going to have to pay the overhead associated with recompiles and no plan reuse.

You can also use the WITH RECOMPILE hint when you define the stored procedure or even when you execute it.

OPTIMIZE FOR Query Hint

Another approach to dealing with bad parameter sniffing is to use the OPTIMIZE FOR query hint. This hint comes with two approaches. You can have the plan generated for specific values, or you can tell it to only, ever, use an average from the statistics.

As I outlined in the previous section, you’ll likely identify a plan that works better for most of your data. When you have that specific data and you want to see just that plan, you modify your query as shown in Listing 13-14.
CREATE OR ALTER PROC dbo.ProductTransactionHistoryByReference
(@ReferenceOrderID INT)
AS
BEGIN
    SELECT p.NAME,
           p.ProductNumber,
           th.ReferenceOrderID
    FROM Production.Product AS p
        JOIN Production.TransactionHistory AS th
            ON th.ProductID = p.ProductID
    WHERE th.ReferenceOrderID = @ReferenceOrderID
    OPTION (OPTIMIZE FOR (@ReferenceOrderID = 53465));
END;
Listing 13-14

Applying the OPTIMIZE FOR query hint

If I alter my procedure in this manner, when I execute to procedure, even if I pass a value that I know will generate another plan, I’ll still get the plan for the value of 53465. Listing 13-15 shows the execution.
EXEC dbo.ProductTransactionHistoryByReference @ReferenceOrderID = 816;
Listing 13-15

Executing the query to generate a plan

The resulting execution plan with runtime metrics is shown in Figure 13-6.

A diagram of the execution plan connects index seek and key lookup with nested loops, then sort. Sort and clustered index with a cost of 5 percent flow to merge join, then select with a cost of 0.

Figure 13-6

Execution plan using a query hint

Nothing in the plan itself will tell you that a query hint is in use. You have to look to the code. However, even though this query was recompiled because I altered the code, the plan generated used the value I gave it in the OPTIMIZE FOR hint. We can validate this by looking at the Parameter List in the properties of the first operator, visible in Figure 13-7.

A table has 2 columns and 4 rows. Column headers are the parameter list and reference order I D. The row entries are column with reference order I D, parameter compiled value as 53465, parameter data type as int, and parameter runtime value as 816.

Figure 13-7

Parameter runtime and compiled values

While we executed the query with the value of 816, the value specified in the query hint was used. If you have more than one parameter value you want to reference in OPTIMIZE FOR, you can just comma separate them.

Another way to use OPTIMIZE FOR is not to specify an exact value, but instead, tell it that no value is known. Listing 13-16 shows how to modify the query.
CREATE OR ALTER PROC dbo.ProductTransactionHistoryByReference
(@ReferenceOrderID INT)
AS
BEGIN
    SELECT p.NAME,
           p.ProductNumber,
           th.ReferenceOrderID
    FROM Production.Product AS p
        JOIN Production.TransactionHistory AS th
            ON th.ProductID = p.ProductID
    WHERE th.ReferenceOrderID = @ReferenceOrderID
    OPTION (OPTIMIZE FOR (@ReferenceOrderID UNKNOWN));
END;
Listing 13-16

Using the UNKNOWN option in OPTIMIZE FOR

With no known value, the optimizer will use the average from the statistics as a row estimate. You also won’t see compile values in the properties as you can see in Figure 13-8.

A table has 2 columns and 3 rows. Column headers are the parameter list and reference order I D. The row entries are column with reference order I D, parameter data type as int, and parameter runtime value as 816.

Figure 13-8

No parameter compile time value

The OPTIMIZE FOR hint gives you the ability to generate a plan based on a specific value, or a plan based on no value at all, as if it wasn’t parameter sniffing. As with the other hints, you can modify code as in the examples, or use plan guides or Query Store hints.

Force Plan

In Chapter 6, I covered how Query Store can be used to force a plan. We’re going to use our analysis of the query and plans to pick the one that works best across the most important queries. That may be the plan that is used the most. It may be the plan that runs the fastest for a given value. You’ll have to make that call on a case-by-case basis. However, once decided, you simply force the plan as shown in Chapter 6. As with the other options, it’s a good idea to revisit these plans as your data and structures change over time.

Parameter Sensitive Plan Optimization

Introduced with SQL Server 2022 and available in Azure SQL Database, a new mechanism is available to deal with some instances of bad parameter sniffing. This new mechanism is called Parameter Sensitive Plan (PSP) Optimization. The nice thing about PSP is that it’s entirely automatic. No code changes, query hints, or plan forcing required. Your database has to be set to compatibility mode 160, SQL Server 2022, and you can’t have disabled parameter sniffing at the server, database, or query level (discussed earlier in the “Disable Parameter Sniffing” section). From there, as SQL Server internals identify a wide variance in execution plans and performance, you can get more than one plan cached in memory. The exact number of extra plans possible is not currently documented.

Microsoft has not published specific information around the thresholds required for PSP to function. However, with some testing, as of this writing, I can say that approximately 100,000 rows and approximately 1 million rows are the dataset thresholds that a given query would have to meet in order to see PSP in action.

In order to test PSP, I modified data in the table dbo.bigTransactionHistory from the MakeBigAdventure script in order to have a value with over 100,000 rows. I also added a single row. I created a new index and a stored procedure as well as executed the procedure, all in Listing 13-17.
--Modify data to get 100K rows
UPDATE dbo.bigTransactionHistory
SET ProductID = 1319
WHERE ProductID IN ( 28417, 28729, 11953, 35521, 11993, 29719, 20431, 29531, 29749, 7913, 29947, 10739, 26921, 20941,
27767, 27941, 47431, 31847, 32411, 39383, 39511, 35531, 28829, 35759, 29713, 29819, 16001, 29951, 10453, 34967, 16363, 41347, 39719, 39443, 39829, 38917, 41759, 16453, 16963, 17453, 16417, 17473, 17713, 10729, 21319, 21433, 21473, 29927, 21859, 16477
);
GO
--Add a single row to both tables
INSERT INTO dbo.bigProduct
(
    ProductID,
    Name,
    ProductNumber,
    SafetyStockLevel,
    ReorderPoint,
    DaysToManufacture,
    SellStartDate
)
VALUES
(43, 'FarbleDing', 'CA-2222-1000', 0, 0, 0, GETDATE());
INSERT INTO dbo.bigTransactionHistory
(
    TransactionID,
    ProductID,
    TransactionDate,
    Quantity,
    ActualCost
)
VALUES
(31263602, 42, GETDATE(), 42, 42);
GO
--Create an index for testing
CREATE INDEX ProductIDTransactionDate
ON dbo.bigTransactionHistory (
                                 ProductID,
                                 TransactionDate
                             );
GO
--Create a procedure
CREATE PROC dbo.TransactionInfo
(@ProductID INT)
AS
BEGIN
    SELECT bp.Name,
           bp.ProductNumber,
           bth.TransactionDate
    FROM dbo.bigTransactionHistory AS bth
        JOIN dbo.bigProduct AS bp
            ON bp.ProductID = bth.ProductID
    WHERE bth.ProductID = @ProductID;
END;
--Execute the Queries
EXEC dbo.TransactionInfo @ProductID = 1319;
EXEC dbo.TransactionInfo @ProductID = 42;
Listing 13-17

Scripts to test Parameter Sensitive Plan Optimization

You may have to adjust the first script depending on how your version of MakeBigAdventure runs. I capture the execution plans for the two procedure calls, and you can see the results in Figure 13-9.

Two query illustrations of 99 and 1 percent. Both has the connection of clustered index seek and index seek with nested loops. Then flows to select with a cost of 0 percent.

Figure 13-9

Two different execution plans for the same query

While the plan shape for these two plans is the same, you can see that the top plan estimates 104,448 rows and returns that many. The bottom plan estimates and returns one row. These are two distinct execution plans. However, I didn’t recompile the queries or procedures between executions. Instead, the optimization process is implementing PSP optimization, so I have two different execution plans.

There are a number of other changes to the behavior of execution plans. In execution plans that also capture runtime metrics (a.k.a. Actual Plans), you get additional data in the properties as shown in Figure 13-10.

A list of new properties execution plans. It highlights the dispatcher with high and low boundaries as 1000 and 100, predicate, and query variant I D as 3.

Figure 13-10

New properties in the execution plan

These new properties show information about how the optimizer is deciding which plan to run. At the top are Dispatcher settings, which show a boundary and the predicate in question, @ProductID. At the bottom is the marker for which variant of the query is in use. This is necessary because as a part of this process, changes are made to the query text. I used the query in Listing 13-18 to retrieve information from cache on our two plans.
SELECT deqs.query_hash,
       deqs.query_plan_hash,
       dest.text,
       deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.text LIKE '%SELECT bp.Name,
           bp.ProductNumber,
           bth.TransactionDate
    FROM dbo.bigTransactionHistory AS bth%';
Listing 13-18

Querying sys.dm_exec_query_stats for PSP information

The results of this query are shown in Figure 13-11.

A table with 5 columns and 2 rows. The column headers are query hash, query plan hash, plan handle, text, and query plan.

Figure 13-11

Querying the plan cache for PSP information

Notice that the query_hash values are the same, but the plan_handle values are different, one for each execution plan. The text for the query is shown here:
(@ProductID int)SELECT bp.Name,             bp.ProductNumber,             bth.TransactionDate      FROM dbo.bigTransactionHistory AS bth          JOIN dbo.bigProduct AS bp              ON bp.ProductID = bth.ProductID      WHERE bth.ProductID = @ProductID option (PLAN PER VALUE(QueryVariantID = 3, predicate_range([AdventureWorks].[dbo].[bigTransactionHistory].[ProductID] = @ProductID, 100.0, 1000.0)))

As you can see, the optimizer made a number of modifications to the query in order to take advantage of PSP.

You can disable PSP using DATABASE SCOPED CONFIGURATION as shown in Listing 13-19.
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;
Listing 13-19

Disabling Parameter Sensitive Plan Optimization

Query Store captures information about PSP plans through a new view, sys.query_store_query_variant. You can see the output in Figure 13-12.

An output captured by Query Store information about query variant query I D as 3659, parent query I D as 3658, and dispatcher plan I D as 158.

Figure 13-12

Query Store information on PSP queries

You can use the query_variant_query_id to find variations on the execution plan in Query Store. The parent_query_id of course goes back to the original query within Query Store. This makes it possible, when using Query Store, to avoid queries like the one in Listing 13-18 where I used LIKE to track down the plans in cache. The dispatcher_plan_id refers to the plan that determines, based on cardinality, which of the different execution plans will be used.

Plans generated through PSP are subject to the same rules around aging out of cache, recompilation, and pretty much all other behaviors described throughout the book.

Summary

While parameter sniffing can aid performance, sometimes, it can also hurt performance. However, the key is identifying which plans are going to work the best in your environment and then using one of the many mitigation methods to address the issue. If you’re working with SQL Server 2022 or in Azure SQL Database, you automatically get Parameter Sensitive Plan optimization, which, if you meet the thresholds, can address bad parameter sniffing with no action on your part.

In the next chapter, we’ll cover common code smells and resolutions as we discuss query design analysis.

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

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