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?

  • Why are you running 32-bit SQL on 64-bit Windows? Has the system only got 2GB in total? – Sim Nov 5 '09 at 12:05
  • Kinda inherited that. System only has 2 gb ram in total yes. – Nai Nov 7 '09 at 23:24

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.

  • erm that's kind of my point? unless im missing something here – Nai Nov 6 '09 at 13:32
  • It says that you don't normally need to do this on a 64-bit OS unless the Buffer Pool memory is getting paged out which is your problem. You aren't running a 32-bit OS so the AWE performance issue shouldn't be a problem. – Sim Nov 6 '09 at 14:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.