SQL Server 2000 Enterprise - memory usage
Hi,
I'm running SQL Server 2000 Enterprise on Windows Server 2003 Enterprise with 8 GB of Ram. For some reason SQL Server is not using more than 120 MB. I configured the options below to enable SQL Server to utilize more than 2 GB. If I keep all the default options, SQL Server will use approximately 1.7 GB of 2 GB allocated. I want to know why sqlserver is not using more than 120 MB with the settings below. Thanks in advance for any help you could provide on this issue.
sp_configure 'show advanced options', 1 RECONFIGURE GO sp_configure 'awe enabled', 1 RECONFIGURE GO sp_configure 'max server memory', 6144 RECONFIGURE GO-
Used the /3GB /PAE switch in the Boot.ini file allows SQL Server 2000 to use up to 3 GB of available memory
|
| |
How are you determining that only 120 MB is being used? Task Manager? On Windows 2003, you should use the SQL counters in perfmon.exe to determine correct memory consumption by sqlservr.exe if AWE is turned on.
Other things to pay attention to - are there any errors in the errorlog (at the top)?
Assuming that your memory reading is correct as pointed out by Greg, it looks to me that for some reason SQL Server is not using AWE.
If you use default memory setting for sp_configure, SQL Server should allocate all available memory during startup, so if nothing is running on you machine the engine will allocate up front more than 7 GB of memory.
To check if you use AWE please look for a message “Address Windowing Extensions is enabled” in the beginning of SQL Server error log after you restart SQL Server.
To properly enabled AWE you should reboot SQL Server after reconfiguring ‘awe enabled’. It is also important to enable OS support for AWE (if it was not already done) – “Lock pages in memory” should be granted to the account SQL Server is running
(see more details in BOL)
Thanks
Mirek
