SQL Server not releasing memory after query executes

Question

I think I have a basic question here that many might have encountered. When I run a query in SQL Server it will load in memory all the data it needs for query execution (for example, if there is a join then it would load the necessary data from those two tables) but when the query finishes executing the memory consumed by SQL Server is not released.

I noticed this because a few days back I was analyzing a query that takes up a lot of tempdb space. When I used to run the query it would (by the end of execution) consume upto 25 GB of RAM. This 25 GB RAM would not be released unless I restarted the MSSQLSERVER service.

How do you guys do SQL Server memory management? This is clearly an issue right?

I would also like to hear if you do something specific to clear the memory used up by a single query.

Thanks in advance!

Answer 1

SQL Server is indeed designed to request as much RAM as possible which will not be released unless this memory is explicitly required by the operating system. I think the best approach is to limit the amount of RAM the server can use which will allow the OS to have a set amount of resources to use no-matter-what. To set this How to configure memory options using SQL Server Management Studio:

Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) managed by the SQL Server Memory Manager for an instance of SQL Server.

1. In Object Explorer, right-click a server and select Properties.
2. Click the Memory node.
3. Under Server Memory Options, enter the amount that you want for Minimum server memory and Maximum server memory.

You can also do it in T-SQL using the following commands (example):

exec sp_configure 'max server memory', 1024 reconfigure 

To restrict the consumption to 1GB.

Note: the above is not going to limit all aspects of SQL Server to that amount of memory. This only controls the buffer pool and the execution plan cache. Things like CLR, Full Text, the actual memory used by the SQL Server .exe files, SQL Agent, extended stored procedures, etc. aren't controlled by this setting. However these other things typically don't need all that much memory, it's the buffer pool and the execution plan cache which need the bulk of the memory.

I hope this helps.