Hello,
I read a lot of artical but still can't understand.
I have SQL 2005 ent edition 32BIT on Windows 2003 Ent R2 32 bit.
I have 4GB on my machine.
What switch I need ton the boot.ini
/3GB
/Userva=2900
/PAE (is this only if I have more than 4GB )
Thanks
Thanks but this is for SQL 2000/7 and I need for SQL 2005,
Aslo I can't found there the /USERVA
Idanle,
You need to look at the bottom of the support link doc Satya gave to you...
PAE is useful for systems owning more than 4GB of RAM
/3GB limits the kernel space to just 1GB instead of 2GB to allow your applications to use up to 3GB instead of up to 2GB.
AWE is used by certain programs that support it (like SQLSRV05), usually along with PAE to provide your applications with more than 2GB of address space which is the norm.
Since your server is 4GB you should use /3GB if you believe the server won't slow down by limiting it to 1GB of kernel address space. PAE need not concern you for 4GB
Thanks a lot
so if I have SQL 2005 on 4GB rammachine I need to use
/3GB /AWE
If you want all your applications on the server (including SQL server) to use 3GB then use /3GB
If you want only SQL Server to use 3GB then use AWE
You're welcome
Dear JohDas,
my server runs with Windows 2003 small business server sp2 and we use SQL 2005 workgroup edition sp1. The physical memory is 3.5 Gbytes, paging file 2-4 Gbytes. Also the server has two Intel Xeon cpus. This is a dedicated SQL server.
We dont get any memory erros but we monitored a poor performance (user transactions take longer to finish), so we want to allow SQL to use more memory, we are trying to figure out the correct configuration. Do you think the following setup for boot.ini is correct and adequate:
/3Gb /userva=2560 /AWE
Regards,
ZindrosH
Hi ZindrosH,
the memory SQL Server reserves is different from what the OS keeps for itself. You could have 4 GB of RAM and have SQL Server using only 512MB of it due to bad configuration.
Use this query to see the configuration about the minimum and maximum memory that SQL Server uses:
SP_CONFIGURE ¡®show advanced options¡¯,1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
and change it accordingly to your needs
Cheers,
John
Dear John,
we haven't set any specific values to min &max server memory. We use the default values.
Because the box has more than 2 Gbytes (it has 3.5 Gbytes) memory and SQL 2005 Workgroup edition might use up to 3 Gbytes we would like to let SQL to use more memory. To my knowledge, one way, is to set specific values to boot.ini file. I am not pretty sure what these values should be.
May be this: /3Gb /userva=2560 /AWE
or
May be this: /3Gb /userva=2560 /PAE
or any other combination or option
Because the server is out to production we do not have the chance to play with these values.
Regards,
ZindrosH
PAE is only required for SQL Server to use more than 4 GB of physical memory - you don't need it
Generally, for a standalone SQL Server, AWE is preferable to 3GB as it allows only SQL Server to take advantage of more than 2GB of RAM
The account used for SQL services will have to be given Windows "lock pages in memory" administrative credentials.
and then running this query to set AWE and max server memory:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 3072
RECONFIGURE
GO
You should always keep at least 1 GB of RAM for the OS
For more info check this KB article: 274750 and SQL books online
Dear John,
thank for your time and patience. I have read not only the links you provided but much more. Thats why I am confused....!
So, PAE is out.
Also since AWE is not supported for SQL 2005 Workgroup edition (I get a relative error when I try to set AWE from Management Studio), what remains is to use /3Gb and /userva=2560 to boot.ini. I guess, this setting will allow 2.5 Gbytes for all applications, including SQL and 1 Gbyte for OS.
What do you suggest, should I try this
ZindrosH