I am facing a problem with the production SQL server running High Availability Group, 2019.
The issue is, the VM memory reached 94% not suddenly but gradually by 8 to 10% daily and when it reached 94% does not reduce until I restart the SQL service.
SQL Server Max memory has been modified to be 25 GB, VM RAM 32GB.
I have noticed sp_server_dignostic was consuming wait time up to 4 hours.
any recommendations?
As pointed out in the comments, SQL Server is meant to consume Memory from the machine and hold it, by design, because it uses it to reduce I/O contention by caching a multitude of things including frequently used data pages of the Tables being queried.
Please see Monitor memory usage - Configuring SQL Server max memory by Microsoft:
By default, a SQL Server instance may over time consume most of the available Windows operating system memory in the server. Once the memory is acquired, it will not be released unless memory pressure is detected. This is by design and does not indicate a memory leak in the SQL Server process.
And also Microsoft's docs on Memory Management Architecture Guide - SQL Server Memory Architecture:
One of the primary design goals of all database software is to minimize disk I/O because disk reads and writes are among the most resource-intensive operations. SQL Server builds a buffer pool in memory to hold pages read from the database. Much of the code in SQL Server is dedicated to minimizing the number of physical reads and writes between the disk and the buffer pool.
Please also see Brent Ozar's recommendations in Memory Dangerously Low or Max Memory Too High which uses a rule of thumb of leaving at least 4 GB or 10% of the total Memory installed (whichever is larger) free to the OS.
Additionally, you may find further detailed information in this similar DBA.StackExchange question.
