Have you ever been digging in your plan cache with either sys.dm_exec_query_stats or sys.dm_exec_procedure_stats and seen two identical queries with different execution plans? When I say identical I’m referring to having the same sql_handle. A sql_handle is generated based on the query text submitted to sql. If one character is different (even white space) it will generate a different sql_handle. On occasion more than one plan_handle for a given sql_handle will appear in the plan cache at a given point in time. Running the query below will show this scenario:
select sql_handle, statement_start_offset, count(1) as plan_handle_count
group by sql_handle, statement_start_offset
having count(1) > 1
order by 3 desc
In the example above we can see that there are multiple statements (as identified by the sql_handle/statement_start_offset) with multiple plans associated with them.
When an execution plan is generated and cached certain environmental factors are taken into account when building the cache key (i.e. the plan_handle). You can use the plan_handle to see what these environmental factors are. You can find these by querying sys.dm_exec_plan_attributes.
What is sys.dm_exec_plan_attributes?
You can query sys.dm_exec_plan_attributes to return attributes of an execution plan. This is a table valued function and it returns the results in a attribute/value form. Some of the attributes returned by this are factored into the cache key. These attributes can be identified by when their is_cache_key column is set to 1 (true).
We’ll take one of the sql_handle/statement_start_offsets from the query we originally ran and see why different plan_handle’s appear by running the following query:
select eqs.plan_handle, epa.* from sys.dm_exec_query_stats eqs
cross apply sys.dm_exec_plan_attributes(eqs.plan_handle) epa
where sql_handle = 0x02000000305A1B212609B6BA2F2B5DDEA84AFC7B2C820CD70000000000000000000000000000000000000000
and statement_start_offset = 4
and is_cache_key = 1
The output of running this is below:
You can see there are two different plan_handles displayed. Every one of the attributes is the same with the exception of one – the dbid. This is the database the query was executed in. The exact same query running in two different databases will generate a different plan_handle.
Even if a query has two different plan_handles it is possible the plans are essentially the same . One way to quickly identify this without opening the plan and comparing it is to compare the query_plan_hash column in sys.dm_exec_query_stats. I have another post discussing this here.