Performance statistics for cached query plans

SELECT

DB_NAME(st.[dbid]) AS DBName,

OBJECT_NAME(st.objectid, st.[dbid]) as ObjectName,

qs.plan_generation_num,

qs.execution_count,

( SUBSTRING(st.[text], (qs.statement_start_offset/2) + 1,

 ((CASE qs.statement_end_offset 

 WHEN -1 THEN DATALENGTH(st.[text])

 ELSE qs.statement_end_offset END 

 - qs.statement_start_offset)/2) + 1)

) AS statement_text

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

WHERE DB_NAME(st.[dbid]) = 'DataBaseName' 

AND OBJECT_NAME(st.objectid, st.[dbid]) = 'ObjectName'

plan_generation_num - number of plan generation for this Object

execution_count - number of executions of the current plan

if plan_generation_num is more than 1 and execution_count 1:

new plan is generated for each execution --> probably not a good situation.

if plan_generation_num is 1 and execution_count is big:

probably good situation.