January 13, 2009 at 3:27 am
I would like to know following:
1- How much by default SQL Server 2005 takes physical memory? and how can i know that?
I have windows Server 2003 EE R2 (SP2) with 16 GB RAM, SQL Server 2005 SP3 installed on same box, we have to dedicate this server only for databases. We have one database usually be working on that with 3 GB size only, I want to know is there any need to configure SQL Server to use atleast 12 GB of physical memory and rest of the 4 GB for OS. Is there any thing to do be configured on SQL Server 2005? Or default settings will manage memory automatically for SQL Server.
Furthermore, I tried AWE option which was not satisfying me because in default settings of SQL Server memory property show 0 MB as minimum and 2147483647 as max.
when i saw example i wanted to test if i configure values to 1024 X 12= 12288 MB wherein the default setting is giving more MB as max
sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 12288
RECONFIGURE
GO
Configuration option 'min server memory (MB)' changed from 0 to 1024. Run the RECONFIGURE statement to install.
Configuration option 'max server memory (MB)' changed from 2147483647 to 12288. Run the RECONFIGURE statement to install.
I think it would not required to enable AWE in my scenario, where it is bydefault giving excecive amout of memory already which is replaced to a small value from 2147483647 to 12288.
I am confused about setting AWE or not. Please help.
Shamshad Ali.
January 13, 2009 at 4:51 am
Unfortunately you don't write if you're using the 32-bit o 64-bit version. On the 32-bit version you need to enable AWE in SQL Server to address more than 4 GB RAM. With 64-bit that's not necessary, in fact the setting will be ignored.
A max memory of 2147483647 simply means that there's not limit and SQL Server can take all the memory available. Even on a dedicates database server I would suggest setting a limit so that the OS has enough memory. In your case 12 Gb sounds ok, though I wonder why you have such a powerful server if you only have one 3gb database.
[font="Verdana"]Markus Bohse[/font]
January 16, 2009 at 5:39 am
Unfortunately you don't write if you're using the 32-bit o 64-bit version. On the 32-bit version you need to enable AWE in SQL Server to address more than 4 GB RAM. With 64-bit that's not necessary, in fact the setting will be ignored.
A max memory of 2147483647 simply means that there's not limit and SQL Server can take all the memory available. Even on a dedicates database server I would suggest setting a limit so that the OS has enough memory. In your case 12 Gb sounds ok, though I wonder why you have such a powerful server if you only have one 3gb database
yeh markus is right you should describe that which version you are using is it 32-bit or 64-bit you can enabled AWE memory for 32-bit
AWE is not needed and cannot be configured on 64-bit operating systems
you can set min and max server memory to one value for performance improvement but before do that is it only dedicated database server and free up some memory for operating system
Raj Acharya
January 16, 2009 at 11:47 am
Regardless of x64/x32 you must leave adequate memory for the o/s. Despite your db only being 3GB, memory is also used by master. msdb, tempdb and system resource dbs. Believe me a server can use far more memory than the siz eof its database, especially if your developers write high io queries/reports!! I usually allocate a minimum of 4GB for o/s on x32 and 8GB for x64. Foget about minimum memory the important setting is max memory. For x32 you'll need awe enabled - don't do this unless you've set max memory first.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 19, 2009 at 4:53 am
Support for AWE is available only in the Enterprise and Developer editions and only applies to 32-bit operating systems.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply