February 17, 2010 at 10:20 am
Hi,
We have 4 SQL Server 2005 EE x64 instance on one server and have 16 GB RAM. I have set the Max memory to each instance as 3 GB and left 4GB for OS. Now I want to set lock pages in memory.
If I set lock pages in memory option (by adding SQL Service account), how does it work for each instance? Does it work for all the 4 instances?
please advice
Thanks
February 17, 2010 at 11:55 am
Lock pages in memory is not required by x64, but it doesn't hurt to add it.
It will prevent the OS from paging out SQL Server memory when under memory pressure.
This can be good or bad.
If you're having memory pressure at the OS level on a SQL only box it really doesn't matter what the setting is... you're going to implode because
a. if you have lock pages in mem set... the OS doesn't have enough memory and cant' take any more
b. if it's not set, the OS can steal SQL Server memory back, which reduces the size of the bpool and reduces SQL functionality.
Either way you're done in.
Even still, I bother to set it on multi-instance machines but leave enough mem for the OS.
Read Slava Oks' article on memory sizing for multi instance machines. There is planty of mem SQL Server uses that is classified outside the max server memory setting (worker thread memory, for example).
Craig Outcalt
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply