0

I have a server running Windows Server 2008 R2 Enterprise 64Bit, with 256 GB RAM.

I am running SQL Server 2008 R2. With normal usage, SQL Server is using about 232 GB of RAM. Occasionally it will creep up approaching 100% RAM usage; this has been causing a lot of problems

I know that I can set maximum server memory usage. I don’t know what to set the usage to. I have been going over the SQL Server documentation and TechNet forums and have not been able to find a best practice or even a consensus as to the correct settings.

I guess what I am looking for is a SQL Server 2008 R2 resource management best practice guide would someone be able to point me in the right direction, or offer some advice. Thanks in advance.

Improve this question
3
  • 256GB RAM and goes to 100% may be it is 256MB? If so you are very low on RAM..
    – apomene
    Feb 5, 2014 at 14:28
  • 1
    Why do you think SQL Server using all of the RAM is the problem? Maybe there are other problems and you don't see them until SQL Server has no more memory to use to alleviate them. By design, SQL Server will use all of the memory you allow it to. It does this because storing data and other information in memory is much quicker than leaving it on disk. If you artificially limit SQL Server to some lower amount of memory (let's say 150 GB), what are you going to use the remaining memory for? Sitting free, unused, and not letting SQL Server use it is going to cause more problems than you think.
    – Aaron Bertrand
    Feb 5, 2014 at 14:47
  • It is running up to 100% and SQL server is crashing. I am attempting to place an upper bound in hopes that SQL server will do a better job at management.
    – Jon Petersen
    Feb 5, 2014 at 15:19

1 Answer 1

Reset to default
3

" Occasionally it will creep up approaching 100% RAM usage; this has been causing a lot of problems"

That's often a red herring, as SQL Server will use all of RAM (but will give it back under OS memory pressure).

Rule of thumb leave 2GB or 5-10% of total physical memory free (Max Memory setting) whichever is larger.

Glenn Berry has a good resource here:

Also:

Improve this answer
1
  • Thanks this is exactly the type of information I am looking for.
    – Jon Petersen
    Feb 5, 2014 at 16:17

Your Answer

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