SQLTeam.com | Weblogs | Forums

How much memory will SQL Server use?


#1

I'm wondering up to how much Memory will SQL Server use in total? In particular when the max server memory is greater than the Server's memory.

Right now the Task Manager says it's using about 8 GB ram while the total on the server is about 12 GB ram. The usage is at 90%, so why did it stop there, and not go to 95%, what stopped it? I'm sure based on the queries we used, the Buffer Cache was full.

Partly I'm asking because I set up a second, smaller instance and I'm not sure how these 2 will co-exist, in terms of RAM usage. We do have a few other things running on the same server as well (and no, this isn't a Production Server).


#2

SQL Server will use up to the max memory defined for that instance - if that instance uses the default then SQL Server could use up all memory on the server.

If you have multiple instances on the same server - you have to set a max memory for both instances that total less than the available memory on the server, leaving enough memory for the OS to do its work.

With 12GB available on the server you should not exceed 10GB of memory combined. If your systems utilize CLR, SSIS, SSRS or any other applications then you need to account for those systems memory requirements.

With multiple instances it is also a good idea to set a minimum memory value - which is the minimum amount of memory that instance needs in order to perform adequately. If this is not set then the other instance could force that instance to give up more memory than it requires to function correctly.

And finally - remove any settings related to lock pages in memory if you are going to allow the multiple instances to steal memory from each other. For example, if you set the first instance to 6GB and the second instance to 10GB max memory - allowing the second instance to use up all available memory if needed, having locked pages in memory will block the second instance from gaining that memory once the first instance has allocated it...


#3

Thanks, makes sense.

What happened was I had both instances at the default (huge number). And one basically took over and left the other one with about 180mb. No wonder it was so slow.

I played around with the settings yesterday. I noticed even with the max memory exceeding the size of the server, it didn't take over the OS completely, it stopped at 90-95% (or was stopped)

Thanks it seems that's exactly what happened.