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
Lock Pages in memory
privilege is in effect for this SQL Server instance? You can check thesql_memory_model
column in sys.dm_os_sys_info to see if that column value is 2 or 3