Does SQL Buffer Pool size = Max server memory.? I have been trying to find out the reason why plan cache in my SQL server 2016 gets cleared every few hours.
- I have 128 GB Physical Memory.
- Earlier i kept 126 GB as Max Server memory.
- Only one SQL server instance and a main DB of size 1.5 TB
- Plan cache was getting cleared and i know that nobody is running any script to clear plan cache.
I always thought and still think that having 128 GB Physical memory won't cause memory pressure as i think it is more than enough for the application.
Clearing plan cache is causing my slow down in my application.I believe as plan reuse i not happening and bad plans are getting into the plan cache for some queries.
I checked ring buffer for memory pressure and there was indication of low physical memory.
So i changed Max Server memory to 102 GB and still after changing also i see
I am confused where Plan cache is allocated.
I learned from plan cache internals that there are four types of caches stores and most of the stored procs,Adhoc queries etc are handles separately and sp_executesql queries are handles separately. Is there any clues there because most of my queries(90%) are sp_executesql type Any insights?
Result of querying
select * from sys.dm_os_memory_clerks order by pages_kb desc