"SQL Server memory is primarily used to store data (buffer) and query plans (procedure cache). In this article I'll show how much memory is allocated to the procedure cache (RAM). I'll explain how to determine what plans are in the cache and how often they're used.
SQL Server stores the procedure cache in 8KB data pages. You can use the dynamic management view sys.dm_os_memory_cache_counters to provide a summary of how the cache is allocated using this query"
SELECT TOP 6
LEFT([name], 20) as [name],
LEFT([type], 20) as [type],
[single_pages_kb] + [multi_pages_kb] AS cache_kb,
Order By single_pages_kb + multi_pages_kb DESC