Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
10  
What are you trying to fix? SQL Server should be using all the memory on the machine, attempting to stop it from doing so is the problem. –  Remus Rusanu Jun 13 '13 at 11:40
1  
SQL Server will, grudgingly give memory back to the OS if there's memory pressure. If there's no pressure, it keeps hold of the memory because, well, why not? It's as likely as not to want to use it again in the future. –  Damien_The_Unbeliever Jun 13 '13 at 12:11
2  
Exactly. This is a non issue. Like "I tell it to use X max, and it does so" - there is no issue. –  TomTom Jun 13 '13 at 14:14
2  
This is a user that doesn't understand that this is how SQL Server works. They come here to ask a question, and get torpedoed with downvotes. Not a good way to promote community. –  Eric Higgins Jun 13 '13 at 18:19
2  
@Eric I suspect any down-votes are for lack of research, not lack of knowledge or to spite community. They also may have been applied before the question was migrated here from SO (impossible even for us diamonds to see that). –  Aaron Bertrand Jun 13 '13 at 18:32
show 3 more comments

migrated from stackoverflow.com Jun 13 '13 at 14:05

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
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 Jun 13 '13 at 18:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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