Configuring AWE and SQL 2005 Memory ! Is this right ?

Hi All

I would appreciate any help from you all with regards to this:

I have a SQL 2005 standard installation with windows server 2003 enterprise Operating system

The server has 7GB of memory available to it, but as I know SQL 2003 standard edition would make use of the total server memory available to it.

Here are the changes that I have made. (I have converted it all to GB for easy reading)

I added the 3GB switch to the boot.ini file

I enabled AWE

I set mini server memory to 5GB

I set max server memory to 6GB

I turned on Performance monitor and the Target server memory counter = 6GB, the total server memory is also set to 6GB.

Now after all the changes above, the server seems to run slower and looking at task manager, the PF usage is around 6GB. If I change the max server memory, the PF usage shoots up again, it MAX server memory is set to 5GB, PF usage jumps to 5GB as well..

Can anyone please confirm that the changes I made were indeed correct and possibly educate me on the configuration and how to make the best use of server memory etc.

I look forward to hearing from you all.

Thanks

"PF Usage" is the amount of memory in use. SQL Server will use every byte of memory you make available to it, so when you set the maximum memory in SQL to 6GB, it used 6 GB. When you set max memory to 5GB, SQL Server used 5GB.

Memory access is 2 or 3 orders of magnitude faster than disk access, so caching more data in memory is usually a good thing for server performance. When you say that the server seems to run slower when you allocate more memory, are you talking about SQL Server serving queries, or the performance of client applications running on the same machine?

The more memory SQL Server uses, the less memory the OS has for other applications to use. On my workstations (which only have development databases on them), I normally limit SQL server to 128 MB of RAM. That leaves plenty of available memory for client applications like Managment Studio and Outlook.

Hope this helps,
Steve

|||

We'll be running SQL Server 2005 Standard edition with Windows Server 2003 Standard edition (32 bit). I'm told by Network Support that our OS is limited to using 4GB of RAM. If we upgrade our OS to Windows Server 2003 Enterprise edition, they say we can up the RAM to 32GB. (Max for our hardware/processor.) My question, though, is if we go to Enterprise edition on the OS, will SQL Server 2005 standard utilize as much RAM as it can? Will it go over 4GB or will we have to change the SQL Server edition to Enterprise as well, in order to take advantage of the extra RAM?

|||

More in this article.

SQL Server 2005 standard use as many RAM get.