SQL Server Instance How to recover memory used [duplicate]

Question

I know that may be this question has been answered a lot of times, but I need a better way to solve it or to demonstrate to my boss that this is a normal behaviour or there is something that I'm overlooking.

I'm not a DBA, I'm a developer, and like my previous job, I've to take the DBA Mantle because we don't have a formal DBA on site or no one want's to take this thing seriously.

I have three instances of SQL Server, one with SQL 2008 R2 and two with SQL 2016. All are from production environment.

The SQL 2008 R2 is from our main System, it is hosted in a Virtual Server in Hyper-V and is a Dedicated Database Server. No other apps are allocated on that server, it is a pure database server.

The issue is that the server is configured with 30 GB of RAM, and SQL Server is configured to use from 4 GB minimum to 22 GB maximum.

If I do a restart, obviously the server memory usage goes to a flat line at server performance manager, releasing memory from memory used to the minimum configured.

I need help understanding this situation, since all documentation point to an irrefutable fact that this is normal in SQL Server, that I've already documented myself about it, but my boss and colleagues state that this is not normal behaviour for the database. If I do numbers I think the server is requesting more memory. They say this is not a normal behaviour of SQL Server and that there is something we are overlooking (Jobs, and other stuff). To monitor or try to determine the Server Behaviour, I started sending Database Health Report every two hours, to try to check what's going on. For them, the expected behaviour is that SQL Server RAM should rise up and down in the performance graph and not stay at the top all the time, and as I understand this is the expected behaviour or SQL Server by design.

But in different points in time, jobs rise to execute, and they finish normally, some time long running transactions are running, but when I run queries for them after the report is generated, they are gone.

Is there something I'm overlooking? Is this Normal? Is there a way to release memory without rebooting the instance? I've already tried a bunch of solutions

Answer 1

Is this Normal?

Yes.

my boss and colleagues state that this is not normal behaviour for the database

SQL Server will grow its memory usage up to your configured max server memory and only reduce its memory usage if the system runs low on physical memory.

If the system has plenty of available RAM, SQL Server will use it to cache data and execution plans, and to have memory on-hand to run queries.

If Windows sends out a global "low memory" signal SQL Server responds by triming its caches and de-allocating memory.

This is explained in detail in the docs:

The default memory management behavior of the SQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. The SQL Server Database Engine does this by using the Memory Notification APIs in Microsoft Windows.

When SQL Server is using memory dynamically, it queries the system periodically to determine the amount of free memory. Maintaining this free memory prevents the operating system (OS) from paging. If less memory is free, SQL Server releases memory to the OS. If more memory is free, SQL Server may allocate more memory. SQL Server adds memory only when its workload requires more memory; a server at rest does not increase the size of its virtual address space.

Max server memory controls the SQL Server memory allocation, compile memory, all caches (including the buffer pool), query execution memory grants, lock manager memory, and CLR1 memory (essentially any memory clerk found in sys.dm_os_memory_clerks).

Memory Management Architecture Guide - Dynamic Memory Management

Is there a way to release memory without rebooting the instance?

You can reduce the max server memory and SQL Server will trim its caches and reduce its memory usage online. After it drops, you can increase max server memory again and SQL Server will re-grow the caches over time.

But this procedure is operationally pointless. The memory isn't needed for anything else, and if it were, SQL Server would respond to the system low memory signal by reducing its memory utilization.

Answer 2

You have the most authoritative answer you'll be able to find, with David Browne's answer. You can now go back to your colleagues and say "hey I spoke to someone at Microsoft and the answer is this is completely normal behavior for SQL Server, by design. I don't think any of us knows better than the guys who created / maintain the product themselves." (Maybe leave that last part off if you're talking to your boss. :) And if the word of mouth of a Microsoft employee isn't good enough, you can show them that it's right in the product documentation in Monitor memory usage - Configuring SQL Server max memory:

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.

But a couple important things I wanted to add: The reason your colleagues think that way is because they would be right in a normal server environment meant to handle multiple applications where sharing is caring and resource spikes that are held for too long could be a problem or a sign of an application with a severe memory-leak issue, for example.

In a server environment where that server is meant to perform as optimally as possible for only one application, SQL Server in this case, then it only makes sense to allow that application as much resources as available (within reason, the OS needs a little bit of resources itself) so that application (SQL Server) can operate as efficiently as possible.

As David mentions in his answer, in particularly with Memory, SQL Server caches a multitude of things. But one of the most important things it caches are the data pages of the data for your Tables such that when a repeat query is ran that needs access to any data within those pages, it can get it straight from Memory instead of the less performant disk. Imagine if every time a query ran it always needed to get all the data from disk, especially when talking about millions of rows / gigabytes of data. That would not be a fun time for anyone.

SQL Server exhibits this behavior in other ways too, for example how when the database files grow (Transaction Log and Data files), they reserve a certain amount of additional disk space (based on the database options that were configured). While that additional disk space isn't actually actively being used by SQL Server, doing so allows it to operate more efficiently for when it does need to (internally the data grows at a slower rate) as opposed to constantly requesting space from the disk, which would be sub-performant. It also guarantees SQL Server will have the space it needs as it grows.

Finally, the last note I'll make is that restarting the server or SQL Server instance, is actually counterproductive to the design of it. By clearing out all of the cached objects in Memory, your SQL Server will likely run slower for the first day or so while it rebuilds the caches, and you'll potentially see more performance issues as a result of it, as opposed to leaving it be and letting SQL Server do what it was designed to do.

---

Long story short, in an environment whose sole purpose is to support one application (SQL Server in this case), it is normal to allocate most of the resources to that one application so it can run as efficiently as possible. Otherwise the resources are a waste (physically and monetarily) if they're sitting around, not being used.