0

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

2
  • Is the Lock Pages in memory privilege is in effect for this SQL Server instance? You can check the sql_memory_model column in sys.dm_os_sys_info to see if that column value is 2 or 3
    – AMtwo
    Apr 19, 2021 at 11:17
  • This is not in effect. It is something I have read about and thought of implementing but I was unsure it would have any benefit.
    – Garry
    Apr 19, 2021 at 12:32

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.