-1

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?

Tala
  • 41
  • 1
  • 3
  • If you don't want SQL Server to consume that much RAM, then limit the amount it can use. Otherwise, as Aaron said, SQL Server will simply use as much as it needs and it doesn't release that RAM after its "claimed" it. – Thom A Apr 16 '22 at 12:43

1 Answers1

5

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.

J.D.
  • 37,483
  • 8
  • 54
  • 121
  • thank you so much for your answer, what I understood it is normal and nothing to worry about. what about the Sp_server_diagnostic why it is consuming wait time? how can I help with this case? – Tala Apr 17 '22 at 07:53
  • @Tala No problem, correct, there's nothing to be concerned with the high server Memory usage, especially now that you set the Max Memory to 25 GB. sp_server_diagnostic is a system stored procedure that returns health information about the server repeatedly until you stop it. This would be unrelated to your high Memory usage, and is not a problem itself either. Someone or something probably ran it on your server but all it does is give information. – J.D. Apr 17 '22 at 12:12
  • if I may ask why it is happing gradually? the memory utilization – Tala Apr 18 '22 at 07:53
  • @Tala Please read the docs I linked for detailed information, but in general, it happens at the rate at which you query your data. If you always query the same Table, and there's enough Memory to fit all the data you query from that Table, then the Memory utilization won't grow much more than that amount. But as you start querying other Table's for more data, then the Memory utilization will increase to cache that data as well. This is one of the main contributing factors that increase Memory utilization. – J.D. Apr 18 '22 at 11:33
  • there is something I did not understand, when I stopped the APP services, the memory did not change and the active sessions were still the same on the DB !! 1800 active sessions.! is there any explinations ? – Tala Apr 22 '22 at 14:19
  • @Tala which services are you referring to?...services for an application you wrote or services specific to SQL Server? If SQL Server, which services did you stop exactly? – J.D. Apr 22 '22 at 17:44
  • the application service that is using SQL Server DB,Not SQL server service. – Tala Apr 24 '22 at 07:38
  • @Tala right, that's how it should function. Just because you stop your application doesn't mean the server stops catching the data in Memory. The whole point of it caching it is so that it's ready to be re-used, for example if you started the application again or had other apps or reports that relied on that same data. It's a good thing for the Memory to be in use, otherwise it's a wasted resource. – J.D. Apr 24 '22 at 13:25
  • thank you, but what about the sessions why does SQL keep it open even if the APP service stopped.? – Tala Apr 25 '22 at 10:35
  • @Tala At this point you should open a second question with your concerns about the sessions. Comments aren't supposed to be used for extended discussion. I wouldn't be able to say much without seeing what you're looking at, other than it's also normal for there to be a bunch of inactive sessions / processes. You can use sp_WhoIsActive to check what sessions / processes are actually currently active. – J.D. Apr 25 '22 at 12:11