If you're in the Kansas City area tomorrow night I'm going to be doing a presentation on the procedure cache, parameterization and parameter sniffing. Details are on the Kansas City SQL Server User Group web site. I'm a last minute fill in but this is a presentation I've been working on for a while. Just to get you started I'll post two queries that I use in the introduction:
SELECT sum(single_pages_kb) AS Single_Pages_KB
, sum(single_pages_kb) / 1024 AS Single_Pages_MB
FROM sys.dm_os_memory_cache_counters
SELECT
LEFT([name], 20) AS [Name],
LEFT([type], 20) as [Type],
single_pages_kb,
single_pages_kb / 1024 AS Single_Pages_MB,
entries_count
FROM
sys.dm_os_memory_cache_counters
WHERE
[type] in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP', 'CACHESTORE_PHDR', 'CACHESTORE_XPROC')
ORDER BY
single_pages_kb DESC
For the server that SQLTeam.com runs on these return the following:
Single_Pages_KB Single_Pages_MB
-------------------- --------------------
671848 656
(1 row(s) affected)
Name Type single_pages_kb Single_Pages_MB entries_count
-------------------- -------------------- --------------- --------------- -------------
SQL Plans CACHESTORE_SQLCP 657144 641 9520
Object Plans CACHESTORE_OBJCP 10128 9 104
Bound Trees CACHESTORE_PHDR 776 0 15
Extended Stored Proc CACHESTORE_XPROC 24 0 1
(4 row(s) affected)
Wow! That's a lot of memory in the procedure cache! Come by tomorrow night to find out why.