Cache and Performance - AdHoc Queries and Proc Cache

—————————————
–    STORED PROCEDURE CACHE ALLOCATION
—————————————
SELECT CAST(name AS VARCHAR(28)) AS ‘Object’,
single_pages_kb AS ‘Cache in Kb’,
entries_count AS ‘Num of entries’
FROM sys.dm_os_memory_cache_counters
WHERE type in (’CACHESTORE_SQLCP’, ‘CACHESTORE_OBJCP’, ‘CACHESTORE_PHDR’, ‘CACHESTORE_XPROC’)

—————————————
–    DATABASE BUFFER CACHE ALLOCATION
—————————————
SELECT count(*) AS cached_pages_count
,CASE database_id
WHEN 32767 THEN ‘ResourceDb’
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;

—————————————
–    CACHED PAGES FOR CURRENT DATABASE
—————————————
SELECT count(*)AS cached_pages_count
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC;

—————————————
–    ADHOC PLANS USED ONLY ONCE
—————————————
select top 1000 * from sys.dm_Exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = ‘Compiled Plan’
and objtype = ‘Adhoc’ and usecounts = 1

—————————————
–    ADHOC PLANS USED MORE THAN ONCE
—————————————
select top 1000 * from sys.dm_Exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = ‘Compiled Plan’
and objtype = ‘Adhoc’ and usecounts > 1

———————————————–
–    TOTAL CACHE USED BY DATABASE AND OBJTYPE
———————————————–
select
dbid, objtype, sum(size_in_bytes) as Total_Size_Bytes
from sys.dm_Exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where cacheobjtype = ‘Compiled Plan’
group by dbid, objtype
order by Total_Size_Bytes desc

select * from master..sysdatabases

—————————–
–    REUSED ADHOC PLANS
—————————–
select
*
from sys.dm_Exec_cached_plans
cross apply sys.dm_exec_sql_text(plan_handle)
where objtype = ‘AdHoc’
and usecounts > 1

select * from sys.dm_Exec_cached_plans where objtype = ‘AdHoc’

select 771276800/1024.00/1024.00

————————————
–    CHECK THE SIZE OF ADHOC CACHE
————————————
select
objtype,
count(*)as number_of_plans,
sum(cast(size_in_bytes as bigint))/1024/1024 as size_in_MBs,
avg(usecounts)as avg_use_count
from sys.dm_exec_cached_plans
group by objtype

————————————
–    CLEAN UP ONLY ADHOC SQL CACHE
———————————–
dbcc freesystemcache (’SQL Plans’)