Thursday, July 26, 2007

What Query Plans are in SQL Server's Memory?

"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,
[entries_count]
FROM sys.dm_os_memory_cache_counters
Order By single_pages_kb + multi_pages_kb DESC

No comments:

Post a Comment

Please leave your thoughts, I love hearing what you got out of the post. Spam comments will be removed.