Will do, thanks

Please help me understand something

I've executed those select scripts seperately. Then I ran the below script to check the cache:

SELECT TOP ( 100 ) [text]

, cp.size_in_bytes

, cp.usecounts

FROM sys.dm_exec_cached_plans AS cp

CROSS APPLY sys.dm_exec_sql_text(plan_handle) st

WHERE cp.cacheobjtype = 'Compiled Plan'

AND cp.objtype = 'Adhoc'

ORDER BY cp.size_in_bytes DESC

Results attached

Then I ran this script to check Query Stats:

SELECT

SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1,

( ( CASE statement_end_offset

WHEN -1 THEN DATALENGTH(t.text)

ELSE s.statement_end_offset

END - s.statement_start_offset ) / 2 ) + 1)

AS statement_text,

last_execution_time,

execution_count ,

statement_start_offset AS stmt_start_offset ,

total_logical_reads / execution_count AS avg_logical_reads ,

total_logical_writes / execution_count AS avg_logical_writes ,

total_physical_reads / execution_count AS avg_physical_reads ,

t.text,

qp.query_plan

FROM sys.dm_exec_query_stats AS s

CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t

CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) qp

where SUBSTRING(t.text, ( s.statement_start_offset / 2 ) + 1,

( ( CASE statement_end_offset

WHEN -1 THEN DATALENGTH(t.text)

ELSE s.statement_end_offset

END - s.statement_start_offset ) / 2 ) + 1) like '%Col1%'

ORDER BY avg_physical_reads DESC

Results attached

The cached_plan script shows 2 plans were generated, one plan shows a usecount of 2 and the other plan shows a usecount of 1

The Query_stats shows one plan with an execution count of 3

Which one is correct here or am I missing something?

Thanks