Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Differences in memory for 32bit SQL Standard and 32bit SQL Enterprise Expand / Collapse
Author
Message
Posted Tuesday, October 23, 2007 11:07 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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?
Post #414184
Posted Tuesday, October 23, 2007 11:35 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
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
Post #414193
Posted Wednesday, October 24, 2007 12:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: Banned Members
Last Login: Tuesday, July 22, 2014 3:58 AM
Points: 2,622, Visits: 327
Try & Check this information link provided herewith.... Making the configurations accordingly....

http://www.sql-server-performance.com/articles/per/system_storage_configuration_p1.aspx
Post #414209
Posted Wednesday, October 24, 2007 12:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 3, 2014 11:47 AM
Points: 2,038, Visits: 1,664
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
Post #414215
Posted Wednesday, October 24, 2007 6:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 5:07 AM
Points: 2,732, Visits: 23,078
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.
Post #414366
Posted Wednesday, October 24, 2007 9:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:54 PM
Points: 212, Visits: 466
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.
Post #414497
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse