Home / server / Questions / 81416
Accepted
Nai
Asked: 2009-11-05 10:12:13 +0800 CST

SQL Server Lock Pages in Memory

  • 772

I recently encountered this problem on SQL Server whereby the ram was maxing out for no apparent reason resulting in sluggish performance. I'm running 64 bit OS on 32 bit SQL Server Standard Edition.

Checking the error logs reveals this:

A significant part of sql server process memory has been paged out. This may result in performance degradation

Apparently the fix for this is to enable SQL Server to lock pages in memory. The workaround can read from here.

However! According to MSDN, enabling lock pages in memory option whilst not using AWE can significantly impair my system. My current set up does not have enough RAM (2 GIGS) to take advantage of this feature.

So what gives? If I don't implement the fix, SQL Server slows down. If I do, it slows down too.

Does anyone have any thoughts or experiences with this?

sql-server-2005

1 Answers

  1. Best Answer

    I don't think the MSDN entry is right. Read the community comments further down:

    Correction to First Paragraph of this Topic From customer feedback, it was noted that the opening paragraph of this topic is not entirely clear. Please regard the following as the official first paragraph:

    How to: Enable the Lock Pages in Memory Option (Windows) The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing Extensions (AWE). This policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk. On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance.Locking pages in memory is not normally required on 64-bit operating systems. You will need to enable this right on 64-bit operating systems only when using Large Page Memory support or to configure SQL Server such that the Buffer Pool memory does not get paged out. Use the Windows Group Policy tool (gpedit.msc) to enable this policy for the account used by SQL Server 2005 Database Engine. You must be a system administrator to change this policy.

    • 1