Differences in memory for 32bit SQL Standard and 32bit SQL Enterprise

Question

Differences in memory for 32bit SQL Standard and 32bit SQL Enterprise

Warren Peace

SSCertifiable

Points: 5881
More actions
October 23, 2007 at 11:07 pm

#120811

What is the max memory that the 32bit version of SQL 2005 Standard can use and what is the max memory for 32bit SQL 2005 Enterprise can use.
I have a 32bit SQL Server Standard with 8GB of RAM and want to allocate 6GB solely for SQL the rest of the 2GB to the OS. I set the min memory to 6144MB and the Max memory to 6144MB but SQL still starts at ~60MB. I was expecting it to use all 6GB of RAM. The AWE is selected, the /3GB switch is in the boot.ini as well as the /PAE switch.
Am I missing something?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

Standard and Enterprise both support the OS maximum.

I'm guessing you're running on Windows 2003 Server? If so, the OS only allocates AWE memory to SQL Server on demand - different from the way it works on Windows 2000 Server where it will all be allocated at once. This is from http://msdn2.microsoft.com/en-us/library/ms178067.aspx

Use min server memory to guarantee a minimum amount of memory available to the buffer pool of an instance of SQL Server. SQL Server will not immediately allocate the amount of memory specified in min server memory on startup. However, after memory usage has reached this value due to client load, SQL Server cannot free memory from the allocated buffer pool unless the value of min server memory is reduced.

Note:

SQL Server is not guaranteed to allocate the amount of memory specified in min server memory. If the load on the server never requires allocating the amount of memory specified in min server memory, SQL Server will run with less memory.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 2

Try & Check this information link provided herewith.... Making the configurations accordingly....

http://www.sql-server-performance.com/articles/per/system_storage_configuration_p1.aspx

Paul Randal One Orange Chip Points: 29544 More actions · Answer 3

err - again, like the previous thread you replied to - not really any more useful info here.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

AWE Memory use does not appear in any of the standard memory use displays. If you want to see the use of AWE memory, you need to use perfmon and look at the AWE memory buffers.

So, if you have configured your memory correctly using AWE memory, you should see in task manager SQL using 60-80mb of memory and never taking anymore. That is normal.

To see if things are working correctly, look at the AWE memory buffers an make sure they are being used. In addition to this, if SQL cannot allocate the minimum memory allocated, you get a message in the SQL logs when the server starts, if you have not received a message about the server not being able to allocate the minimum memory, you are probably all set.

You should also do some testing. SQL 2005 memory allocation management is much better than it was in SQL 2000 and leaving it dynamic can sometimes give you better performance. I found that was the case with two of the last three SQL 2005 environments I set up recently.

Finally, make sure you understand the differences in the way SQL uses regular memory (the first 2-3gb it can take) and the way it uses AWE memory. AWE memory has a very limited subset of uses and you may find that there are many things you want SQL to cache that it cannot use AWE memory for. It is important to understand the differences so your expectations are in-line with reality.

Warren Peace SSCertifiable Points: 5881 More actions · Answer 5

Thanks alot everyon. I really appreciate your help on this one. I did not have this problem on our 64bit servers. They took whatever I told it to take.

Thanks again.