Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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

share|improve this answer
    
"Memory allocated through AWE doesn't show up on the processes tab in Task Manager". I've checked the performance with a simple Sql query and it's terrible with 60MB memory used. –  Mono Cular Sep 14 '13 at 6:56
    
I don't mean to suggest that your query ran quickly, only that Task Manager and other tools that report the size of a process' working set don't include allocations made through AWE. If query performance was slow, I'd suggest reproducing the problem and gathering further information to determine why. People here can help but this sounds like a separate question. –  James Sep 15 '13 at 15:54
add comment

Just a quick remark since i cannot make comments.

You should not be using the network service account to lock pages in memory. Everything that runs through it will be locked in memory. Create a new account and configure it.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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