Low Procedure cache

Question

On a SQL Server instance I have, at times the Procedure cache drops quite considerably. Down from 5GB to 3GB and on the rare occasion there in something in the workload which causes it to drop down to the MBs and stay there and not grow, causing memory and CPU pressure issues.

When there has not been a intensive noticable query running to cause this I am unsure how or what to look for in terms of queries to find what is causing this.

I have tried to run perform collecting some memory statistics and have noted that at the times of these drops, there is a noticable decrease in Plan Cache(Bound Trees)\Cache Pages and Plan Cache(Object Plans)\Cache Pages.

I am most interested in the Bound Trees one, what type of query could be causing this?

Thanks

Answer 1

There's a multitude of reasons why a query plan can be removed from the plan cache. Some of those reasons are low use plans, old plans, or when your SQL Server encounters Memory pressure and needs to free up the Memory consumed by the plan cache to relieve some of that pressure. So what you are observing could actually be that your Memory pressure is the cause for clearing your plan cache, not the effect of it.

There's many other reasons the plan cache can get cleared out as well, as noted in Introducing the SQL Server Plan Cache (and a Better sp_Blitz®):

Service restarts

Database restores

Statistics changing on an object

Server comes under memory pressure

People running DBCC FREEPROCCACHE

This article also lists some additional ways that your cache can get cleared such as similar but alternative DBCC commands to FREEPROCCACHE, using the sp_recompile procedure, or when you run an ALTER DATABASE statement.