Sql-server – Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram

sql serversql-server-2005

I'm using Window Server 2003 Enterprise, Microsoft SQL Server 2005 Standard Edition.

The problem is the SQL Server memory gets stuck at allocating 1.7GB RAM max even though I have 11GB of RAM left.

Following the advice from a post in this thread, in which the thread starter had the same issue, I tried adding /PAE to my boot.ini, adding the network service account which is running sqlserv.exe to Lock Pages in Memory user right, and reconfiguring SQL to use AWE to allocate memory.

But I had no luck. After restarting the server, the SQL database engine cannot allocate more than 60MB RAM, which is a terrible failure as I expected.

So after that, I must restore the Lock Pages in Memory setting: remove the network service account from Lock Pages in Memory option, restart my server, and I'm back to the first problem. The SQL Server database engine is stuck at allocating 1.7GB RAM.

So the Lock Pages in Memory keeps the SQL database engine stuck at allocating 60MB RAM? And how can I resolve the first problem now?

Best Answer

As you might know, the reason it allocates 1.7 GB of working set is 32-bit memory limitations. On 32-bit editions of Windows, by default each process gets 2 GB of usable virtual address space, and by default 32-bit editions of SQL Server leave a few hundred MB outside of its largest consumer of memory, the buffer pool.

It is most likely just a misunderstanding that SQL Server allocated only 60 MB of memory when you enabled locked pages. Memory allocated through AWE doesn't show up on the processes tab in Task Manager (or other tools that report a figure based on the processes' working set size) because it isn't part of the process' working set. If SQL Server used a smaller amount of memory when you enabled Lock Pages in Memory, this suggests that AWE was working and the SQL Server process had most likely allocated more than 60 MB of memory.

There are several ways to view memory allocated by AWE, including but not limited to the following.

  • Query sys.dm_os_memory_clerks, for example as follows:

    SELECT SUM(awe_allocated_kb) AS [Total KB AWE Allocated] 
FROM sys.dm_os_memory_clerks;

  • View the Performance Monitor counter SQL Server: Memory Manager\Total Server Memory (KB)

A few notes on your question:

  • As you probably know, basically anyone who does this for a living will tell you that it's long past time to migrate off the 32-bit operating system and 32-bit SQL Server. You'll be able to use far more RAM without any hassles on a 64-bit platform.

  • It's highly unusual for SQL Server to be running as the Network Service account.

  • Processes allocate memory, not RAM (which may not be the same thing)

  • You shouldn't need to add /PAE to boot.ini. Windows has booted a PAE-aware kernel by default since Windows 2003 SP1 unless you also disable Data Execution Prevention with the switch '/noexecute=alwaysoff'. (You don't want to do that, by the way.)

  • Using locked pages can cause out-of-memory errors if you don't set a reasonable memory limit in SQL Server or if something other than the SQL Server buffer pool -- either another program or another part of SQL Server -- is competing for memory in Windows.

References:

How It Works: SQL Server 32 bit PAE/AWE on (SQL 2005, 2008, and 2008 R2) – Not Using As Much RAM As Expected!

Fun with Locked Pages, AWE, Task Manager, and the Working Set…

Great SQL Server Debates: Lock Pages in Memory

Related Question