We have a SQL Server Standard Edition 2017 with 1.7 TB data that never seems to have enough memory. Recently upgrade memory from 100 GB to 128 GB, still get RESOURCE_MEMPHYSICAL_LOW in sys.dm_os_ring_buffers, please need some advice on how to check further on needed memory cause sys.dm_os_memory_brokers also still give GROW notifications.
A few things to consider:
SQL Server 2017 Standard edition has limits to the amount of memory it can use. See https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15#Cross-BoxScaleLimits
RESOURCE_MEMPHYSICAL_LOW is an indicator of physical memory being low - specifically, the memory that hasn't been allocated to SQL Server. What have you set within SQL as the maximum server memory it's allowed to use? See https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options?view=sql-server-ver15
What else is running on the server? Or is it just SQL Server? That should inform your configuration setting. For a basic calculation, you could do worse than follow Jonathan Kehayias's advice at https://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/
Thanks for the reply @ThomasRushton, Yes we used the limit now because we would like to see if it would change on SQL Server, we set the max server memory to 128000 MB and the VM OS RAM is set to 150 GB. Only the SQL Server is running on the server.
Although the MEMORYCLERK_SQLBUFFERPOOL in sys.dm_os_memory_clerks only uses 144366 MB, still there is memory pressure shown on the IndicatorsPorcess in sys.dm_os_ring_buffers.
What else can i check?
Sorry typo: Although the MEMORYCLERK_SQLBUFFERPOOL in sys.dm_os_memory_clerks only uses 114366 MB, still there is memory pressure shown on the IndicatorsPorcess in sys.dm_os_ring_buffers.
Is there somewhere else i can look for memory pressure stats?
The ring buffer contains messages relating to events and 'wraps around' - are you sure you are seeing new messages or are you looking at previous RESOURCE_MEMPHYSICAL_LOW messages?
This is what i see when i check the ring buffer for memory events (from this morning 7.47AM), but i don't understand every part yet.
16 People are following this question.
