question

Davtjen avatar image
Davtjen asked

SQL Server 2017 memory usage high with 1.7 TB data

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.

sql server 2017memory-utilization
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered

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/

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Davtjen avatar image
Davtjen answered

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?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Davtjen avatar image
Davtjen answered

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?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered

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?

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Davtjen avatar image
Davtjen answered

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.


10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.