Can the plan cache be used to diagnose a lack of RAM?
1
vote
1
answer
90
views
I have learned a few good ways to diagnose a lack of RAM on a SQL Server and I am wondering if I should add the plan cache to this toolbox.
A sign of lacking RAM is that the plan cache doesn't live very long. You can easily find the oldest cached plans in
sys.dm_exec_query_stats
and you can find how much RAM the plan cache is using from SELECT * FROM sys.dm_os_memory_clerks WHERE TYPE = 'CACHESTORE_SQLCP'
. **Can either of these be turned in to actionable information about your system lacking RAM?**
My suspicion is that neither view provides useful metrics. If 2% or 20% of my server's memory is taken up by the plan cache, then so what? Similarly, if my oldest cached plan is a month old, then so what? For all I know, it could just be a very frequently used plan.
Asked by J. Mini
(1237 rep)
Feb 22, 2024, 12:08 AM
Last activity: Feb 24, 2024, 07:21 AM
Last activity: Feb 24, 2024, 07:21 AM