February 15, 2006 at 3:46 am
I have installed SQL 2000 Enterprise Edition sp4 on a Windows 2003 sp1 Server which has 4GB RAM. I want SQL to use 3.5 GB RAM, as this will be the only App running on this Server. I have added the /3GB switch to the boot.ini, and configured SQL to use AWE. Have I missed aything here. And is there a definitive way of checking how much Memory SQL is using. Any help is greatly appreciated.
February 15, 2006 at 3:56 am
Don't use the AWE because you don't have > 8Gb RAM.
Adding the /3Gb is enough.
Check out http://www.sql-server-performance.com/sql_server_performance_audit5.asp
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 15, 2006 at 4:53 am
I believe that there is a "Lock Pages In Memory" policy setting that needs to be applied to the service account. This is not enabled by default even for Administrators.
February 15, 2006 at 5:04 am
Thanks for your replies, I have turned off AWE, but still not sure if SQL is using the 3.5GB that I have specified. Also copied this quote from MSDN2 - 'On 32-bit operating systems, setting this privilege when not using AWE can significantly impair system performance.' looks like I shouldn't enable this setting.
February 15, 2006 at 6:11 am
SQL Server will not use more than 2.8 GB of physical RAM. Its limitation of process running on 32 Bit operating system.
/3GB is the option by which SQL can use maximum RAM as per your configuration.
"And is there a definitive way of checking how much Memory SQL is using. Any help is greatly appreciated"
You can use Perfmon and use below counter.
SQLServer:BufferManager: Target Server Memory
SQLServer:BufferManager: Total Server Memory
February 16, 2006 at 7:55 am
My current advice is also to not use the 3GB switch. Here is a recent, yesterday, comment from an engineer at Microsoft.
Unless you have the benchmarks, and dedication to monitoring a server with /3GB, I would recommend against using it.
The effects of 3GB are profound on the kernel:
Instead of the normal 2G/2G split (kernel and usermode apps), now usermode applications get 3GB, and the kernel gets 1GB. This means our Non-Paged pool/Paged Pool/System PTEs/Desktop Heap/Session Heap/Filesystem cache, are all reduced in size significantly. Sometimes this is beneficial, most of the time it is not.
Terry
February 16, 2006 at 10:40 am
Besides the /3GB in the boot.ini file you'll need to run:
exec sp_configure 'min server memory (MB)',3072
exec sp_configure 'max server memory (MB)',3072
exec sp_configure 'set working set size',1
reconfigure with override
then reboot
You may get an error message in the SQL error log about 'failed to allocate "n" (a huge negative number) of Kb', do not worry, it's a known bug, you still get the memory allocation you requested. You may also receive a 'could not set working set size to "n" Kb'. If you get this message you need to assign 'lock pages in memory' right to the account that starts the SQL Serve service.
Now when it comes to using 3 Gb of RAM, you'll probably never see more than 2.7 or 2.8 Gb being used by sqlservr.exe in the task manager. This is normal.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
February 17, 2006 at 3:35 am
This suggests to me that if you have a SQL Server with 4GB RAM then there is little benefit in allowing it to look beyond the 2GB threshold?
The other 2GB is best reserved for the OS?
As I am looking at a clustered environment then I would guess that the above statements have credence?
If the above are true then how much memory should be in a SQL box before it is worth the hassle of configuring it to use AWE?
February 17, 2006 at 6:48 am
For 4GB on the server:
Enable /3Gb and leave to settle at 2.8GB ish (see this KB http://support.microsoft.com/default.aspx?scid=kb;EN-US;316749, SQL leaves 384MB out of 2-3GB). Trying to increase beyond 3GB will most likely squeeze kernel mode memory and cause PTE/pool/heap etc memory issues .
If you have more than 4GB, then you maybe don't need the 3Gb, because of the was PAE/AWE memory is handled and treated. Beyond 16GB, you can't anyway because the OS needs the extra 1GB kernel mode to mange AWE.
I've seen MS recommend that /3Gb is not used on clusters with >4GB.
However, for a standalone dedicated MSSQL box with 4Gb or less RAM, personally I'd use /3GB. Also, I would not play with set working set size etc - there is just no need.
You are very unlikely to suffer PTE/pool/heap etc memory issues because little happens on the server except MSSQL activity. Also, MSSQL does not use the OS file cache, so less kernel mode RAM is needed for that.
I've *never* had a box give memory errors, except when someone (not me) bollixed up max memory with AWE and only left 128MB and starved the OS and MOM etc.
If you want to run your box as file server/web server/application server etc, then using 3GB *could* generate issues, but for a plian vanilla dedicated MSSQL box, use /3Gb and leave it there.
And before anyone flames me for *not" tweaking settings, read this:
http://msdn.microsoft.com/SQL/?pull=/library/en-us/dnsqldev/html/sqldev_01262004.asp
And try running a dedicated MSSQL box...
August 14, 2007 at 10:29 am
SOLUCION !!!!:
Lo que esta faltando es activar el awe
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
reiniciar SQL Server
y luego ejecutar los minimos y maximos de memoria deseados
Configurar un límite de 1 GB para min server memory y 6 GB para max
server memory.
sp_configure 'min server memory', 1024
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
Ver http://technet.microsoft.com/es-es/library/ms190673.aspx
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply