Understanding Memory - SQL Server 2012 Internals and Troubleshooting - page 72

Checking That Your Max Server Memory Is Ef ective
How you decide to coni gure Max Server Memory when you build a server (there are many opinions
on the matter) isn't as important as measuring its effectiveness and adjusting it when the server has
run its expected workload. An easy way to do this is using performance monitor counters,
specii cally, MSSQL$<instance>:Buffer Manager\Page Life Expectancy (PLE) (also see the
section “Clerks, Caches, and the Buffer Pool”) and Memory\Available MBytes . The balance
between these two counters shows you how effective your Max Server Memory setting is.

➤

PLE: Shows you how many seconds SQL Server expects to keep a page in the data cache and
is a good measure of memory pressure on SQL Server

➤

Available MBytes: Shows how much physical RAM Windows has that isn't doing anything

If your PLE is low (<300 is dei nitely low but you might choose a higher threshold), then check your
Available MBytes to see how much unused RAM is available. Windows starts to aggressively trim
(see next section) all application working sets when it has less than 5MB available, so anything close
to this on a production server should be considered an urgent problem.

The minimum Available MBytes you should have is 100MB but even this is cutting it too close
because any application that is run on your SQL Server can easily use that up. Instead, try aiming
for 500 or 600MB as a minimum or even 1GB to be sure. That way, if you need to run any support
tools on the server, there will be plenty of RAM for them.

So, if your PLE is low and you have plenty of Available MBytes because you were conservative
with your Max Server Memory setting, then you have scope to increase your Max Server Memory,
thereby increasing your PLE. Conversely, if your Available MBytes is low because you were
aggressive with your Max Server Memory setting and your PLE is very high, then you can reduce
your Max Server Memory to give some RAM back to Windows.

Here are some example scenarios to illustrate this point:

➤

Max Server Memory is 30GB on a server with 32GB RAM. PLE averages 10,000 and
Available MBytes is 90MB. Solution : Lower Max Server Memory by at least 500MB.

➤

Max Server Memory is 46GB on a server with 50GB RAM. PLE averages 10 and Available
MBytes is 1500MB. Solution : Increase Max Server Memory by 500MB to 1000MB.

➤

Max Server Memory is 60GB on a server with 64GB RAM. PLE averages 50 and Available
MBytes is 20MB. Solution : Lower Max Server Memory by 100MB and buy more RAM
(quickly).

Lock Pages in Memory
Lock Pages in Memory ( LPIM ) is used as a work-around for a problem than can occur between
Windows and SQL Server, and it was especially bad on older versions of SQL Server, which could
run on Windows Server 2003 and earlier.

If there isn't enough free physical memory in Windows to service a request for resources from a
driver or another application, Windows will trim the working set (which refers to the physical