Q & A: Does SQL Server always respond to memory pressure?

Non-yielding IOCP Listener - Stack Dump analysis

SQL in Stockholm — Thu, 11 Sep 2008 20:49:29 GMT

Here's something I was looking at yesterday which is worth a quick look. It's quite a common scenario

Welcome -- Ax Database Configuration Checklist part 1

Dynamics Ax Performance team — Wed, 12 Mar 2008 21:56:41 GMT

Welcome to the Dynamics Ax Performance Team's blog. We're putting together a team introduction and hope

Configuring Windows 2003 ( x64 ) for SQL Server

Grumpy Old DBA — Sun, 06 Jan 2008 23:49:12 GMT

There is an almost constant stream of posts on forums asking about configuration of SQL Server 2005 memory,

SQL Server Management Studio Standard Reports – Memory Consumption

Carpe Datum — Wed, 17 Oct 2007 19:12:44 GMT

I’m continuing a series on the Standard Reports you can find in SQL Server Management Studio (SSMS).

re: Q & A: Does SQL Server always respond to memory pressure?

Chris Conner — Mon, 20 Nov 2006 22:23:23 GMT

You should also remember that if your memory is 32GB, that the kernel is going to need 2GB of memory allocation, nevermind just the OS to run. :)

For machines that have > 16 GB of memory, I subtract 4 GB - 2 for kernel and 2 for OS and applications.

If I have 16 GB or less, I subtract 3 GB IF I am using the /3GB switch in boot.ini. If I am NOT using the /3GB switch, then the kernel is taking 2GB and since I always leave a buffer of 2GB for my applications and OS on machines that are using less than 16 GB memory that adds to 4GB to subtract instead of 3GB if I use the /3GB switch.

Like Slavo has said, this is a personal preference. :)

re: Q & A: Does SQL Server always respond to memory pressure?

nd-pike — Sat, 18 Nov 2006 14:07:14 GMT

Glenn,

I like to use the Pages Output/sec counter to monitor the hard page faults which are occurring on the system. As you increase 'max server memory', keep a history of this counter. If you start to note significant increases you may have set 'max server memory' a little too high, thereby forcing other user mode processes to page more. I make gradual increases in the 'max server memory' setting, e.g. 512MB or 256MB at a time, before assessing the Perfmon counters against a baseline.

re: Q & A: Does SQL Server always respond to memory pressure?

slavao — Tue, 14 Nov 2006 22:47:33 GMT

Glenn, we have to be careful here. I specifically avoid recommending setting max server memory to 30GB on 32GB system. You need to make sure that amount of available memory to OS doesn't drop below ~2GB under heavy load.

You are also correct that you need to monitor the above counters to find out right max server memory setting , however if amount of memory available to OS drops you are in danger of bringing the whole machine to its knees.

You might think about my recommendation as how to figure out upper limit for max server memory. If you notice based on other counters that SQL Server becomes memory bound and you can't increase max server memory anymore due to my recommendation, then to improve the situation you might need to buy more RAM

re: Q & A: Does SQL Server always respond to memory pressure?

Glenn Berry — Tue, 14 Nov 2006 21:46:47 GMT

Slava,

Another good post. For a dedicated SQL Server 2005 x64 machine with 32GB of RAM, setting Max Server memory to 30GB seems maybe a little low. Wouldn't you also want to look at metrics like Page Life Expectancy and Memory Grants Pending to decide what to set Max Server Memory at?