How to clear cache for specific database?
select * from master..sysdatabases
DBCC FLUSHPROCINDB (@intDBID)
DBCC FLUSHPROCINDB (10)
CLEAN UP ONLY ADHOC SQL CACHE
————————————
– CLEAN UP ONLY ADHOC SQL CACHE
———————————–
declare @size_in_MBs int
select @size_in_MBs = (select size_in_MBs from
(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) as AXA1
where objtype = ‘Adhoc’)
select @size_in_MBs
if @size_in_MBs > 200
dbcc freesystemcache (’SQL Plans’)
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 [...]
Number of cached pages used by each database in SQL Server 2005
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;
See:
http://msdn.microsoft.com/en-us/library/ms173442.aspx
PROCEDURE CACHE AND BUFFER CACHE INFORMATION
–plays information in a table format about the procedure cache.
DBCC PROCCACHE
–Removes all elements from the procedure cache.
DBCC FREEPROCCACHE
–Removes all clean buffers from the buffer pool.
DBCC DROPCLEANBUFFERS
