high memory 94% SQL server



  • 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 https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-memory-usage?view=sql-server-ver15#configuring--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 https://docs.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-ver15#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 https://www.brentozar.com/blitz/max-memory/ 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 https://dba.stackexchange.com/q/287475/150011 .


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2