Understanding sys.dm_exec_query_stats execution_count vs creation_time

Question

I've just downloaded the SQL Server 2012 'Performance Dashboard Reports' and am having some issues interpreting the data shown by one of the results and linking it back to the DMV sys.dm_exec_query_stats.

According to the CPU report, my worst performing query is an insert statement that has run ~27K times.

When I take the query_hash for the query and put it into the query below, I get 62 results, each with the same query_hash and query_plan_hash but with different plan_handle values.

SELECT * FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest
WHERE query_hash = 0x1561B563078359C1

According to BOL, the execution_count shows:

Number of times that the plan has been executed since it was last compiled.

and creation_time shows

Time at which the plan was compiled.

I am 99.9% sure the query runs once every 5 minutes, yet each query in the result set has a plan creation_time of this morning and yet execution_count shows ~400 for each one.

How does SQL Server determine this figure? Are the figures somehow linked by plan_hash or are they unique for each plan_handle?

Answer 1

sys.dm_exec_query_stats returns aggregated stats for plans, but with a row per query.

So if a procedure has 2 queries, then sys.dm_exec_query_stats would return 2 rows, but the plan-related columns (such as compile time, execution count, etc) are plan level stats, and cannot be further aggregated.