AWE upper memory limit

Hi,

I have a question that I hope someone can help me with.

I am running SQL Server 2005 on a Windows 2003 Standard edition (x32). I have 6GB of memory in it but have realised that Windows can't use more than 4GB (I have enabled /PAE and /3GB in the boot.ini).

My question is:

Can SQL Server make use of the extra memory I have in the server? Otherwise I guess it is pointless having more than 4GB in there.

Thank you for your help!

Kind Regards

Stefan Ghose

Hello Stefan -

The way memory is used between 32-bit processors, the various Windows operating systems and SQL Server can be confusing. Because of 32-bit architectures and software design decisions, the first 2GB of RAM in a Windows 2000 or 2003 server is directly addressable by applications like SQL Server. That means that query plans, memory buffers and the like all go in this space. No changes are needed in any configurations, either at the OS or SQL Server level.

With the /3GB switch turned on in the BOOT.INI file, SQL Server increases this base limit to 3GB. This is great for SQL Server, but if you have other apps on your server, it can starve the OS, leaving only 1GB for all operations. Experts are divided on whether this is a good thing. I turn it on when the box is dedicated to SQL Server, and watch for errors in the event logs. If I see memory or I/O errors, or if the box becomes unstable in testing, I turn it off.

If you have 4GB in your server as you've indicated, that's all you need to do. Inside of SQL Server, I also recommend setting a floor and ceiling limit of 3GB, so it doesn't have to dynamically manage the RAM. That just saves a few ops, which is always a good thing. Again, I only do this if SQL Server is the only app on the box.

If you have over 4GB of RAM on the server, SQL Server needs a few things to use it. First, you have to enable the /PAE switch in the BOOT.INI file. Next, you need to set the SQL Server to use AWE memory. With these two things configured, the server will use the space above 4GB as a read-ahead buffer. It doesn't help much with query plans and those sorts of base memory operations, but it will speed up large queries.

Buck Woody

|||

Thanks Buck!!

That was just what I was looking for... another thing... since I already have enabled AWE on my SQL server do I need to do anything with Max memory usage? Or can I just leave it at its default value?

Kind Regards

Stefan Ghose

|||The default is fine - you have it dynamic, I assume?