SQL Server Performance

To Lock Pages in Memory or not

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by knightprincesqlsp, Oct 17, 2007.

  1. knightprincesqlsp New Member

    After reading a lot of blogs about the issue of Paging Out in SQL Server 2005 Standard x64 under Windows Server 2003 Standard x64, I am wondering if it is needed in my server, which I will neeed to deploy in a couple of days. So until then, i really have no real world perf mon data yet.
    Contrary to every blog I read and from MS itself, SQL Server 2005 Standard Edition x64 can use Lock Pages in Memory feature, not being ignored as claimed. I was able to confirm this by finding it in SQL Event Log for 'using the lock pages in memory' and finding AWE being used when DBCC MEMORYSTATUS is ran.
    OS: Windows Server 2003 Standard Edition SP2 x64
    SQL: SQL Server 2005 Standard Edition SP2 x64 build 9.00.3186
    RAM: 4GB
    CPU: 2 Quad-Core
    Service Account SQL is running on: Local System (i know, it's not recommended but that's how the current policy is set up, until I have made my recommendation on Policy changes.
    1. With the above specs, is there a need to be pro-active and follow MS recommendation to lock pages in memory? I am hesitant to apply this because SQL is running under the Local System account, which other services on the server are also running from. BTW, this is a dedicated SQL Server. and only the default instance.
    2. If I Lock pages in memory for SYSTEM, will this not affect the overall performanc eof windows as other services running under SYSTEM, will have their pages locked also?
    3. Is it really needed to set the maximum server memory, to say in my case, 3GB, if I set Lock Pages in Memory?
    I am hoping that setting Max Server Memory to default (dynamically configured), without locking pages in memory, will be a good decision at pre-deployment.
    Any thought will be highly appreciated.
  2. FrankKalis Moderator

  3. satya Moderator

  4. knightprincesqlsp New Member

    I got excited about this blog, but when I double check my ErrorLog, I dont see lock pages being used. I have restarted my server, but still no log for it.
    It's probably the default when you are using EE, but to SE, you need to manually set it up. This leaves me to the original concern that, is there really a need for me to set it up when I only have 4GB of RAM on x64?
  5. satya Moderator

    Test it, no harm in doing so and have you gone through second one too above?

Share This Page