Hi,
I manage a few SQL 2000 Servers for online transactions or straigt through processing. Good performance is very important for this application, so I've been going over the configuration to see if there's performance to be gained.
Basicly I have 2 questions:
- Is my SQL Server using the maximum processor capacity
- How do I get my SQL Server to use all available internal memory
I'll start of by giving you some specs:
- 4 P4 processors (Hyperthreading makes it look like 8 processors)
- 4 GB memory (2 GB available)
- Windows 2000 Server Standard (SP3)
- SQL Server 2000 Enterprise (SP4)
Processors
I've read that the maximum number of processors SQL can use in this configuration is 4 (combining W2k Svr Standard and SQL 2k Enterprise). In the Enterprise Manager I rightclick the Server and click properties. On the general tab 8 processors are reported. On the processor tab 8 processors are selected. Now I am unsure what to believe; are all 4 processors being used by SQL or only 2 If only two processors are used, should I upgrade windows to Windows 2000 Server Enterprise (so 8 processors are supported) or should I disable hyperthreading
Memory
With this Windows/SQL combination, up to 4GB of memory should be supported. Yet it's clear SQL Server does not use all available memory, because 2GB of memory remains available. I've read some articles on AWE (Advanced Windowing Extensions), PAE (Physical Address Extensions) and a /3GB switch, but I can't filter out what I actually need to do in this situation.
note: I've configured SQL Server to use 3GB of memory. After rebooting the server I see an error in the logs that SQL can not allocate all memory.
Can anyone shed some light on to these issues Thanks in advance.
Erik.
SQL Server 2000 processor and memory usage tuning
Big Smile
You must add the /PAE switch to the boot.ini to enable AWE
Juergen
JohnSantana
Hi Mark,
Thanks for the reply. It didn't work for me though. First off I have to correct something: I am running Windows 2000 Advanced Server (not the standard version). I have run the following script:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 3072
RECONFIGURE
GO
And I've editted the boot.ini:
[boot loader]
timeout=3
default=multi(0)disk(0)rdisk(0)partition(1)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /3GB
C:\CMDCONS\BOOTSECT.DAT="Microsoft Windows 2000 Recovery Console" /cmdcons
After I reboot the machine, in the SQL Server error log I read: Warning: unable to allocate 'min server memory' of 2560MB. Also I read: Address Windowing Extensions enabled. SQL Server is using 1792MB of RAM, so nothing has changed after I applied the above.
By the way, the Lock Pages in Memory administrative credential is assigned to the Windows account the SQL Service runs on.
I must be missing something here. Can anyone help Thanks in advance.
Erik.
Nomada
1. Disable Hyperthreading since there is not much benefit to it for SQL (Win2k3's scheduler is HT aware, Win2k is not). Four physical processors is the limit of W2k Server Standard. You get 8 with HT, but HT does have much benefit for SQL (it can be worse sometimes) even though SQL will try to use all 8.
2. Use the /3GB switch and enable AWE. AWE will allow the buffer pool to page in more memory for database pages. The /3GB enforces a limit of 16GB of memory for the OS. The memory limit is not an issue for you, but it can be for bigger boxes.