sql-serverquery-optimizationsql-execution-plan

plan_handle is always different for each query in SQL Server Cache

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:

Plan Cache View

My understanding is:

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:

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?

Adding one simpler scenario without parameterization involved.

These two queries produce different plan_handle even without parameterization involved:

SELECT * FROM dbo.Countries

SELECT * FROM dbo.countries

The only difference between them, is the case in one letter ("countries" VS "Countries").

This image shows two different plan_handles: enter image description here

Here are the actual plans:

When comparing the plans, these are the differences: enter image description here

I still don't know why I am getting a different plan for both queries, when it should be reused in my opinion.

This is the T-SQL that I'm using the retrieve the plan cache contents. I'm including this because I'm starting to think that the problem could be related to how I'm analyzing the cache contents.

    SELECT
        QS.last_execution_time,
        QS.execution_count,
        QS.total_elapsed_time,
        QS.total_elapsed_time/QS.execution_count AS AvgDuration,
        QS.total_logical_reads,
        QS.total_logical_reads/QS.execution_count AS AvgRead,
        T.text,
        QS.query_hash,
        QS.plan_handle,
        P.query_plan
    FROM
        sys.dm_exec_query_stats AS QS
        CROSS APPLY sys.dm_exec_sql_text (QS.sql_handle) AS T
        CROSS APPLY sys.dm_exec_query_plan (QS.plan_handle) AS P

Thanks

Solution

  • 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