Sample Header Ad - 728x90

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