I'm the only one working on this server and followed documentation about optimizing SQL memory usage when I saw that SQL was using all of the memory available of the server.

One of the steps I did is to fix the max memory size to 20 Gb (server for test purposes with 32 Gb memory).

After tuning, yesterday, SQL Server was only using 3-4 Gb but today, it has again reached the max memory usage I allocated.

Why is SQL Server keeping all 20 Gb? What will happen when users will begin to use the server if the max memory size is already used?


1 Answer 1


It is holding that memory because you used it once so, obviously, you will probably use it again. This is the way SQL Server works: it will take the memory it needs (up to the max you've allowed), and will only give it back to the OS if the OS demands it. If you're not demanding memory back from SQL Server, why do you expect it to give it up? Allocating and deallocating memory is inefficient, and SQL Server isn't designed to give you memory back so you can let it sit idle. For similar reasons, it doesn't automatically shrink files for you after you delete or reorganize data: grow/shrink operations are expensive, and if the file is just going to grow again tomorrow...

If you are running SQL Server on your laptop where you need the memory for other tasks, then you should consider setting a lower max server memory setting. This way SQL Server will never encroach on the memory you think you need.

If you are running SQL Server on a server, then it likes to be the only thing on the box. If the server is hosting other applications etc. that need memory, then you can either reduce max server memory further, or move those applications to another server. If there are no other applications that need the memory, then why do you care whether it's using 20 GB, or 16 GB, or 12 GB, or 100 MB? Are you going to lease out the remaining memory temporarily to other servers until SQL Server needs it again? Didn't you give it 20 GB of memory so it will use 20 GB of memory?

There is absolutely no reason you should be worried that SQL Server is using the amount of memory that you've told it is ok to use. This is exactly how it's supposed to work. When users run queries against the server, presumably, they will likely be accessing data that's already in memory (a large portion of SQL Server's memory allocation is buffer pool). If your database is a lot bigger than 20 GB, then you may consider actually raising the max server memory setting to get the best use out of the memory and how much I/O comes from there rather than disk (which is a lot slower). Artificially lowering the max server memory because you think Windows needs 12 GB of memory to operate is like paging to disk - you're forcing more I/O through slow disk subsystems that could be performed in much faster memory.

Again, if nothing else is running on the server, there's no reason you need more than 3 or 4 GB for the OS, leaving everything else to SQL Server. And again, you shouldn't be worried if you have set SQL Server's max memory to 28 GB, and it keeps "using" 28 GB even after all the users have gone home. This just means that in the morning it won't take any work to get back up to 28 GB.

  • 1
    It should be added that SQL Server is one of those servers (together with Exchange) that are designed to scale up. And in larger installations it is alone. Always. In fact, SQL Server often has carefully planned large disk layouts to get proper I/O. As Aaron says, there simply is no reason to give memory back when it is caching data.
    – TomTom
    Commented Jun 13, 2013 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.