plan_cache_analysis.txt

--shows cached plans stats

SELECT objtype AS [CacheType]
        , count_big(*) AS [Total Plans]
        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]
        , avg(usecounts) AS [Avg Use Count]
        , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]
        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY [Total MBs - USE Count 1] DESC
go

-- shows cache stats based on type

select * from sys.dm_os_memory_cache_counters order by pages_kb desc

--sql_handle 

SELECT st.*, qs. sql_handle, qs.plan_handle
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st; 

-- get sql test from plan_handle or sql_handle:

SELECT * from sys.dm_exec_sql_text(plan_handle|sql_handle)

-- shows most used adhoc plans; change (or omit) the objtype to get what you need

SELECT st.text, cp.plan_handle, cp.usecounts, cp.size_in_bytes, 
 	cp.cacheobjtype, cp.objtype
FROM sys.dm_exec_cached_plans cp
 	CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st 
	where objtype = 'adhoc'
ORDER BY cp.usecounts DESC