Setting up SQL Server 2005 to use all available memory in 32bit Windows Server 2003 - and verifying

Posted by Rizwan Kassim on Server Fault See other posts from Server Fault or by Rizwan Kassim
Published on 2010-04-16T00:06:11Z Indexed on 2010/04/16 0:13 UTC
Read the original article Hit count: 630

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

© Server Fault or respective owner

Related posts about sql-server-2005

Related posts about windows-server-2003