Why is my SQL Server prefering to pick one plan over the other when there are 2 executions plans for the same query in the cache?

Question

I have a 2019 SQL Server with a database with 'FORCE PARAMETERIZTION' set to true and a nightly job which does sp_updatestats on it.

One of the application has a feature which has two modes, a "operational" mode and a "test" mode. In the "test" mode, the data distribution is really skewed as compared to the "operational mode" where the datadistribution is uniform.

If the application is used early morning in the "operational mode" first by a user, a new plan is created by the SQL server due to the previous nights update stats. This plan is good for the "operational mode" & acceptable for the "test" mode but since the "test" mode is rarely used, there are no complaints.

But If the appliation is used in the "test" mode first thing in the morning, a plan is created which is good for the skewed data but terrible for the case with the "operational mode". As a result, the site experiences poor performance throught the day.

The application is a black box and cannot be updated. My thoughts are to introduce a hack with the update stats job wherein a SQL in the "operational mode" would be executed. This way, irrespective of whether the user uses either mode, performance will be good the next day.

But the DMV shows that there are 2 plans in the cache, one created by the nightly job and the other from the application.

The one created by the application has huge number of execution counts against it while the other has only one. The query_hash, plan_hash, even the sql_handle is the same on both entries. Only the plan_handle is different. When I examine the SET options in the plan XML, they are exactly the same. The only differences are the parameters used to create the plan and MaxCompileMemory values.

I would like to know why the SQL Server chose to create new plan for the query from the application even though it could have used the one from the nightly job. And it preferred to use the one created by the application repeatedly.

I know the relation between the sql_handle and plan_handle is 1:N but in this particular case, everything is the same (apparently). The SET options are the same, the table statistics going as input to the plans are the same, the query in the job is executed when there is no one around using the application.

Any inputs appreciated.

The eventual goal is to introduce a plan guide but I feel I need to understand the reasons for this behavior before trying trying to introduce a plan guide

Answer 1

Multiple plans

Multiple plans (each with a unique plan_handle) for the same batch text (sql_handle) means one or more cache keys must be different.

A difference in SET options is one possibility because set_options is a cache key.

Other cache keys include:

acceptable_cursor_options
compat_level
date_first
date_format
dbid
dbid_execute
is_replication_specific
language_id
merge_action_type
objectid
optional_clr_trigger_dbid
optional_clr_trigger_objid
optional_spid
parent_plan_handle
required_cursor_options
set_options
status
user_id

Use sys.dm_exec_plan_attributes with plan_handle as a parameter to list the attributes for the two plans. There will be a difference in one or more cache key values.

set_options

Be sure to check set_options first because not all SET options are listed in showplan XML, and the information there is not always accurate.

You can decode the values with:

DECLARE 
    @set_options integer = 266491;

SELECT
    Setting = IIF(GROUPING(V.Setting) = 1, 'Total', V.Setting), 
    IntVal = SUM(V.IntVal)
FROM
(
    VALUES
        ('ANSI_PADDING', 1),
        ('ParallelPlan', 2),
        ('FORCEPLAN', 4),
        ('CONCAT_NULL_YIELDS_NULL', 8),
        ('ANSI_WARNINGS', 16),
        ('ANSI_NULLS', 32),
        ('QUOTED_IDENTIFIER', 64),
        ('ANSI_NULL_DFLT_ON', 128),
        ('ANSI_NULL_DFLT_OFF', 256),
        ('NoBrowseTable', 512),
        ('TriggerOneRow', 1024),
        ('ResyncQuery', 2048),
        ('ARITH_ABORT', 4096),
        ('NUMERIC_ROUNDABORT', 8192),
        ('DATEFIRST', 16384),
        ('DATEFORMAT', 32768),
        ('LanguageID', 65536),
        ('UPON', 131072),
        ('ROWCOUNT', 262144)
) AS V (Setting, IntVal)
WHERE
    @set_options & V.IntVal = V.IntVal
GROUP BY
    V.Setting WITH ROLLUP
ORDER BY
    SUM(V.IntVal);

Example output:

Setting	IntVal
ANSI_PADDING	1
ParallelPlan	2
CONCAT_NULL_YIELDS_NULL	8
ANSI_WARNINGS	16
ANSI_NULLS	32
QUOTED_IDENTIFIER	64
ANSI_NULL_DFLT_ON	128
ARITH_ABORT	4096
ROWCOUNT	262144
Total	266491

user_id

A second popular difference is in user_id, indicating unqualified object names. When run under different accounts, such objects might resolve in different schemas depending on the user executing the batch.

optional_spid can be a factor when the batch uses a temporary table it did not create, for example.

Cached plan reuse

I would like to know why the SQL Server chose to create new plan for the query from the application even though it could have used the one from the nightly job. And it preferred to use the one created by the application repeatedly.

SQL Server can only reuse a cached plan safely if all the cache keys match. Most likely, you will find your overnight job had QUOTED_IDENTIFIER set OFF—as commonly happens with SQL Server Agent jobs—while the application has it set ON.

Your set_options

From a comment:

The value of set_options is 397563 from the application and 266491 from the job

Decoding these values shows one was compiled in a context where FORCED_PARAMETERIZATION was off, while the other had it on. Presumably, the one with it off was running in a different database context (USE <db>), likely master or tempdb.

Your user_id

You also indicated that values for user_id are different. This can be fixed by schema-qualifying object names in scripts. For example, instead of writing:

SELECT * FROM MyTable;

You should write:

-- Schema prefix used
SELECT * FROM dbo.MyTable;

As the documentation for the user_id cache key attribute says:

Value of -2 indicates that the batch submitted does not depend on implicit name resolution and can be shared among different users. This is the preferred method. Any other value represents the user ID of the user submitting the query in the database.

Otherwise, users with different default schemas could see different instances of MyTable, such as Sales.MyTable or Reports.MyTable.

Finally, note that not only tables should be schema qualified:

• Views
• User-defined functions
• Stored procedures
• Synonyms
• Service Broker queues
• Sequences
• User-defined types
• XML schema collections.