Sql-server – SQL Server 2008 R2 Resource Managment

sql serversql-server-2008-r2

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.

Best Answer

" 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: