1

There are a number of questions along this line - but they either sometimes contradict each other, or don't show how to properly verify that everything is actually working - hopefully this can be comprehensive...

I'm running SQL Server 2005 SP3 Standard on Windows Server 2003 R2 Standard. My server has 8GB of memory installed - my system is almost entirely used as a Database Server - there are some services running on them, but the OS + services can run within 1Gb of RAM.

What I've done (please tell me if I'm doing something wrong):

  • /3GB in the boot.ini. (To increase the amount of user-space memory available - info)
  • /PAE in the boot.ini. (Windows claimed to be doing PAE even without this switch, somethow.)
  • Enabled AWE in SQL Server.
  • Enabled Lock Pages in Memory Option for users SYSTEM and Local Service. (info). SQL Server Standard doesn't seem to use this until Cumulative Update 4, which isn't installed on my server. (info)
  • Set Min/Max Memory to : 1024Mb/5112Mb

After doing all the above, we definately saw a level of improvement - but I'd like now to verify my settings, make sure that I'm making full use of the memory available. (There appeared to be a slowdown when max = 7Gb, so I edged off from that value, but it might have been just perceptual.)

To verify, I checked the following levels in PerfMon :

  • Process(sqlserv):Working Set : 76386304
  • SQL Server(Memory Manager) : Total Server Memory : 3538944 (I saw a doc that noted that this wasn't the full memory used by SQL Server, so I'm not sure whether to trust it)

So -- my questions...

  1. Should my max be around 7Gb? If not, what should it be?
  2. Why is total server memory at 3.5G, when it's been allocated 5G?
  3. What is the proper metric for the amount of memory allocated to SQL Server? The Working Set seems a bit large...
  4. Am I possibly missing any steps in the setup?
  5. Any recommended resources on starting to tune the caching system now?

Thanks

3 Answers 3

2

You won't be able to use AWE with Windows 2003 Standard. You need Enterprise, see Large memory support is available in Windows Server 2003 and in Windows 2000:

PAE is the added ability of the IA32 processor to address more than 4 GB of physical memory. The following operating systems can use PAE to take advantage of physical memory beyond 4 GB:

* Microsoft Windows 2000 Advanced Server
* Microsoft Windows 2000 Datacenter Server
* Microsoft Windows Server 2003, Enterprise Edition
* Microsoft Windows Server 2003, Datacenter Edition

To enable PAE, use the /PAE switch in the Boot.ini file.

The working set you see is 7MB, not 7GB. That is a normal working set for a process, since working set don't measure the amount of memory allocated to a process, but only the working set which is always much much smaller:

Shows the current number of bytes in the working set of this process. The working set is the set of memory pages touched recently by the threads in the process. If free memory in the computer is above a certain threshold, pages are left in the working set of a process even if they are not in use. When free memory falls below a certain threshold, pages are trimmed from working sets. If they are needed, they are then soft-faulted back into the working set before they leave main memory.

You need to look at Process\Virtual Bytes instead:

Shows the current size, in bytes, of the virtual address space that the process is using. Use of virtual address space does not necessarily imply corresponding use of either disk or main memory pages. Virtual space is finite, and by using too much, the process can limit its ability to load libraries.

The SQL Server memory is 3GB, which is all it can allocate on Windows Standard with /3GB switch.

So you'll need to purchase a Enterprise Windows license, or simply move to an 64 bit OS and 64 bit SQL deployment (which is really the only sane option).

3
  • I see. I could have sworn that I've read things to the contrary, but I can't seem to find them. Thanks! Do you know if the SQL Server Standard license automatically covers 32 and 64bit, or am I off to buy a new SQL Server license? Apr 16, 2010 at 2:34
  • I should probably turn down max memory to 3.5Gb then, shouldn't I? Apr 16, 2010 at 2:38
  • Licensing FAQ is at microsoft.com/sqlserver/2005/en/us/pricing-licensing-faq.aspx but I don't think it covers specifically your question. Your best bet is, by far, to ask MS. Apr 16, 2010 at 2:39
0

You won't be able to use all 8GB of memory without a 64-bit OS.

0

FYR:

For Windows 2003 Standard edition with 32bit the max memory limit is 4GB.

http://msdn.microsoft.com/en-us/library/aa366778(VS.85).aspx

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .