1

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.

See below

So i changed Max Server memory to 102 GB and still after changing also i see RESOURCE_MEMPHYSICAL_LOW

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

enter image description here

  • Have you checked the queries for any missing indexes? Officially I think lock pages in memory is a no no now, but on physical servers with HP drivers I've seen it work miracles so that drivers and other vendor software doesn't clear the memory. Check for memory messages in your SQL log – Alen Jul 24 '18 at 20:21
  • Based on your last statement, I'd dig into the sp_executesql statements. Check out this question and answer specifically about the variables your statements may be using. Perhaps it's simply the underlying queries themselves. – scsimon Jul 24 '18 at 20:42
  • @Alen : i checked for messages in SQL error log,but there is none regarding cache clearing.There are no missing indexes ,i checked those as well. – user9516827 Jul 24 '18 at 20:49
  • what about memory being pooled out? and check for missing indexes too – Alen Jul 24 '18 at 20:50
  • how do i find memory pooled out?I checked the sys.dm_os_memory_clerks and the results are updated in the post.Can you address whether Buffer Pool size = Max server memory? and whether plan cache comes in this buffer pool. – user9516827 Jul 24 '18 at 20:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.