CODEWITHSUNDEEP
sql-serverdatabasesql-server-2012
aditya y
aditya y

Reputation: 19

SQL Server is not releasing memory after Daily Load

We have a SQL Server 2012 Enterprise Edition, 128GB of RAM, Windows 2008R2. The SQL Server job runs every day at 3 AM and takes 5 hrs to load data into the database. During this process, SQL Server utilizes 123GB (max memory allocated).

After the job completes, SQL Server is not releasing the RAM.

Queried memory utilization where buffer pool shows 97GB. Users don't access database during this time. I restarted SQL Server services to bring RAM down. I didn't find a correct answer related to this issue. Why is it not releasing the RAM? How can we bring RAM utilization down?

SQL Server Job -> SSIS package -> Import data from Mysql to SQL Server database

Thanks

Upvotes: 0

Views: 6975

Answers (3)

Paul Williams
Paul Williams

Reputation: 17040

By design, SQL Server holds on to the RAM that is has allocated. Much of the RAM is used for the buffer pool. The buffer pool is a cache that holds database pages in memory for fast retrieval.

If SQL Server were to release some memory, and someone were to run a query that requests it right afterwards, the query would have to wait for expensive physical I/O to produce the data. Therefore, SQL Server tries to hold as much memory as possible (and as configured) for as long as possible.

The RAM settings here specify the min server memory and the max server memory. Careful setting of the max memory setting allows room for other processes to run. The article quotes a complicated formula for determining how much room to leave:

  • From the total OS memory, reserve 1GB-4GB to the OS itself.
  • Then subtract the equivalent of potential SQL Server memory allocations outside the max server memory control, which is comprised of stack size 1 * calculated max worker threads 2 + -g startup parameter 3 (or 256MB by default if -g is not set). What remains should be the max_server_memory setting for a single instance setup.

In our servers, we usually just wing it and set the max memory option to several GB below the total physical memory. This leaves plenty of room for the OS and other applications.

If SQL Server memory is over the min server memory, and the OS is under memory pressure, SQL Server can release memory until it is at the min server memory setting.

Reference: Memory Management Architecture Guide.

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. SQL Server tries to reach a balance between two goals:

  • Keep the buffer pool from becoming so big that the entire system is low on memory.
  • Minimize physical I/O to the database files by maximizing the size of the buffer pool.

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.

...

As more users connect and run queries, SQL Server acquires the additional physical memory on demand. A SQL Server instance continues to acquire physical memory until it either reaches its max server memory allocation target or Windows indicates there is no longer an excess of free memory; it frees memory when it has more than the min server memory setting, and Windows indicates that there is a shortage of free memory.

As other applications are started on a computer running an instance of SQL Server, they consume memory and the amount of free physical memory drops below the SQL Server target. The instance of SQL Server adjusts its memory consumption. If another application is stopped and more memory becomes available, the instance of SQL Server increases the size of its memory allocation. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.

If, for some reason:

  1. You absolutely MUST have that memory back
  2. You know you do not need it for a while
  3. You are willing to pay a penalty for virtual memory allocation and physical I/O to retrieve data from disk the next time you need that memory

Then you can temporarily reconfigure the database max server memory setting to a lower value. This can be done through the SSMS user interface, or you can use an sp_configure 'max server memory' followed by reconfigure to make the changes programatically.

Upvotes: 1

NotAnAuthor
NotAnAuthor

Reputation: 1291

Full disclosure: I did not try it myself. You should not try it on your production environment before testing it somewhere else.

This is from a DBA answer:

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'max server memory', 4096;  
GO  
RECONFIGURE;  
GO

4096 should be replaced by the value that you find acceptable as the minimum.

Should be followed by a similar command to increase the memory back to your original maximum.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69554

This is by design once SQL Server uses memory, it keeps hold of it and does not release it back to OS.

Your Task Manager may show all/nearly all memory used by SQL Server but if you want to see how much memory SQL Server is actually using you can use the following query. 

SELECT    (physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB
FROM sys.dm_os_process_memory;

Upvotes: 2

Related Questions