April 30, 2010 at 10:58 am
Scott Coleman (4/30/2010)
According to the Microsoft engineers that wrote SQLOS (and were available for questioning at the PASS conference), SQL Server responds to memory pressure by recognizing when available physical RAM drops below a certain threshhold. It does not wait for notification from the OS.And while I would not be willing to give any other applications on a SQL Server system the right to lock pages in memory, I can't imagine running SQL Server on a 64-bit system without it. If the system is so "configured incorrectly" that it will be unstable if SQL Server tries to manage the page buffer memory allocation, you shouldn't be trying to run SQL Server on it in the first place.
We run a number of 64 bit systems. If I understand you right, you are saying that you cannot imagine running a 64 bit system without "locking pages in memory". Sorry to be ignorant, but can you say a little more about this?
April 30, 2010 at 1:48 pm
(Assuming we're talking about 64-bit SQL Server 2005 Enterprise, or 2008 Enterprise or Standard)
The "Lock pages in memory" right (granted to the user account that SQL Server runs under) is required to use AWE. If SQL Server has this right it will use the AWE API for memory allocation. This allows slightly more efficient memory allocation by SQL Server for some memory pools such as the page buffer, and also has the effect of making AWE-allocated memory off limits to virtual memory paging. (Enabling AWE via sp_configure on a 64-bit system does nothing, the only issue is whether the SQL Server account has the lock pages permission.)
Since SQL Server will run more efficiently if it can lock pages in memory, I want to enable this for every server that is primarily a SQL server.
I was responding to TheSQLGuru's counter argument, which referenced http://support.microsoft.com/kb/918483, showing that problems may arise if too many applications are locking too much memory. As it correctly points out, if too many apps are locking too much memory, the only thing left to page out is the code you're trying to run. I think that if there is a conflict, it must be the other application's fault. Yes, I'm a grumpy ole DBA. All you kids get yer memory-hogging apps off my lawn!
Some references:
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply