SELECT objtype 
, usecounts 
, COUNT(*) AS [no_of_plans]
FROM sys.dm_exec_cached_plans
WHERE cacheobjtype = 'Compiled Plan'
AND usecounts = '1'
GROUP BY objtype , usecounts
ORDER BY objtype , usecounts

select * from sys.dm_os_performance_counters
where counter_name like '%compilations%'

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

select Col1 from Table1 where Col1 = '10';
select Col1 from Table1 where Col1 = '10';
select Col1 from Table1 where Col1 = '12';

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

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