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
from sys.dm_exec_query_stats
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.

Final thoughts

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.