August 21, 2003 at 1:51 am
Hi!
I have a server (HP 6xPIII-700 Xeon , 4Gb RAM) with about 70Gb total databases on it.
The Cache Hit Ratio counter is low ~75%, SQL Compilations/sec counter is about 30
I wonder why the server keeps about 150-370Mb of available memory all the time?
Why it does not use all the memory to increase the Cache Hit Ratio and decrease the number of SQL Compilations/sec?
/* It is MS SQL 2000 sp3 + MS Win 2000 Adv Server sp4 */
Thanks.
August 21, 2003 at 8:32 am
Which edition of your SQL Server? How do you configure the memory allocation for SQL Server?
August 21, 2003 at 8:55 am
This is Enterprise Edition of MS SQL Server
This is an abstract from sp_configure output:
name, minimum, maximum, config_value, run_value
awe enabled, 0, 1, 1, 1
max server memory (MB), 4, 2147483647, 4000, 4000
min memory per query (KB), 512, 2147483647, 1024, 1024
min server memory (MB), 0, 2147483647, 0, 0
show advanced options, 0, 1, 1, 1
user options, 0, 32767, 1056, 1056
I also have /3G switch in boot.ini:
[boot loader]
timeout=5
default=multi(0)disk(0)rdisk(0)partition(2)\WINNT
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /3G
C:\="PC-DOS"
August 21, 2003 at 9:16 am
After enabling AWE, SQL Server will reserve almost all available memory, leaving 128 megabytes (MB) or less. In your situation, You really don't have to enable it because there is 4GB memory. Try to disable AWE and see what is going to happen.
August 21, 2003 at 11:42 pm
After I disable AWE, the SQL Server will reserve only 3Gb of memory...
Right?
August 22, 2003 at 7:00 am
/3GB enables user-mode programs (such as SQL Server)to access 3 GB of memory instead of the usual 2 GB that Windows NT/2000 normally allocates to user-mode programs and leaves 1 GB for OS.
Edited by - allen_cui on 08/22/2003 07:00:59 AM
August 23, 2003 at 1:12 am
quote:
/3GB enables user-mode programs (such as SQL Server)to access 3 GB of memory instead of the usual 2 GB that Windows NT/2000 normally allocates to user-mode programs and leaves 1 GB for OS.
- But I do not need 1Gb memory to OS, I only need about 200Mb for OS and the rest (3,8Gb for MS SQL) At this time MS SQL 2000 eats about 3,5Gb of RAM.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply