SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Warren Peace
Warren Peace
Right there with Babe
Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)

Group: General Forum Members
Points: 783 Visits: 476
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?
Paul Randal
Paul Randal
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5752 Visits: 1717
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
Paul Randal
Paul Randal
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5752 Visits: 1717
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
Michael Earl-395764
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8423 Visits: 23078
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
Warren Peace
Right there with Babe
Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)Right there with Babe (783 reputation)

Group: General Forum Members
Points: 783 Visits: 476
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search