plan_handle is always different for each query in SQL Server Cache

Question

I'm evaluating some Plan Cache behaviors and this is my scenario.

I'm running the following two queries separately:

SELECT TOP 10 *
FROM dbo.Countries CT
LEFT JOIN dbo.Continents CN ON CT.ContinentId=CN.ContinentId
WHERE CountryId='AR'

SELECT TOP 10 *
FROM dbo.Countries CT
LEFT JOIN dbo.Continents CN ON CT.ContinentId=CN.ContinentId
WHERE CountryId='BR'

After running both queries, I'm getting this plan cache view:

My understanding is:

• Different sql_handle: expected
• Different plan_handle: unexpected
• Same query_hash: expected
• Same query_plan_hash: expected

Question: I really don't get why I'm getting a different plan_handle for each execution, even when the query is basically the same, and the query_hash and query_plan_hash do match. What could be the reason for this?

This is a comparison of both cached plans: Comparison of cached plans

I get the difference in the statement but I don't think that should count. Otherwise, we would always have one plan_handle per sql_handle since it would always change.

Some additional settings already checked:

• Optimization level: FULL
• No plan warnings: (both are Good Enough Plans found)
• SET options match, both queries are executed in the same SSMS Window
• Compat Level: 140
• Optimize for Ad Hoc: false
• Query Optimizer Fixes: Off
• Legacy CE: Off
• No Database-scoped configuration
• Parameterization: Simple
• Resource Governor: Disabled

I checked all potential properties affecting this behaviors with no luck. I would expect both queries reusing the same plan, hence, pointing to the same plan_handle. Is my expectation incorrect?

• Plan A: https://www.brentozar.com/pastetheplan/?id=ByP5gwIkA
• Plan B: https://www.brentozar.com/pastetheplan/?id=Bk2ybw81C

Thanks

Answer 1

I would expect both queries reusing the same plan, hence, pointing to the same plan_handle. Is my expectation incorrect?

Your expectation is incorrect. When you hard-code where-clause values, the literal values are available at the time the plan is optimized, and so the different queries may get different plans.

If you don't explicitly build parameters into the design of your applications, you can also rely on the SQL Server Query Optimizer to automatically parameterize certain queries by using the default behavior of simple parameterization. Alternatively, you can force the Query Optimizer to consider parameterizing all queries in the database by setting the PARAMETERIZATION option of the ALTER DATABASE statement to FORCED.

Query processing architecture guide

SIMPLE parameterization will only parameterize queries where it has high confidence that the query plan doesn't depend on the parameter value, and it's safe to reuse a single plan for all future values.

See:

Parameterization is considered safe if the query processor would generate the same plan for all possible future parameter values.

Paul White - Simple Parameterization and Trivial Plans — Part 6

In general, SQL Server auto-parameterizes those queries whose parameterized form of the query would result in a trivial plan. A trivial plan exists when the query optimizer determines that only one plan is possible

Plan Caching and Recompilation in SQL Server 2012