July 14, 2003 at 11:35 am
We are starting to install more than 4 GB of RAM in some of our servers, and we are also starting to use awe (with the /PAE switch in BOOT.INI) with SQL 2000 SP3. Here is the (successful) errorlog entry from where we turned on awe over the weekend:
2003-07-12 16:41:26.04 server Address Windowing Extensions enabled.
As I watched the server today, I was expecting to see the Cache Hit Ratio go higher than before, but it is at the same level (~86%). Is a better Cache Hit Ratio an unrealistic expectation?
We are obviously in unknown territory with awe at this site. What else should I be watching?
July 14, 2003 at 12:22 pm
Which edition of SQL Server you are running? How much addtional memory did you add? Can you post the result of sp_configure here? How large is your database?
July 14, 2003 at 12:48 pm
>>Which edition of SQL Server you are running?
SQL 2000 Enterprise Edition
>>How much addtional memory did you add?
We went from 4 GB to 6 GB.
>>How large is your database?
There are 8 user databases on the server. The largest database is 3.5 GB in size and rarely used. The next largest database is 1.5 GB in size.
July 14, 2003 at 12:49 pm
>>Can you post the result of sp_configure here?
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 1 1
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 1 1
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2200 2200
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 0 0
nested triggers 0 1 1 1
network packet size (B) 512 65536 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 5 5
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 1 1
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0
Edited by - shew01 on 07/14/2003 12:52:04 PM
July 14, 2003 at 1:21 pm
To use AWE memory, you must run the SQL Server 2000 database engine under a Windows 2000 account that has been assigned the Windows 2000 lock pages in memory privilege.
To obtain the correct amount of SQL Server memory usage, you can use the Total Server Memory (KB) performance counter, activated through System Monitor, or select the memory usage from sysperfinfo.
You may consider add the /3gb parameter to the boot.ini file. This allows user applications to address 3 GB of virtual memory and reserves 1 GB of virtual memory for the operating system. In your situation, your application (SQL Server) will be able to use up to 5 GB memory.
The problem in your configuration is the 'max server memory (MB)', you allocated only 2200 MB to you SQL Server. You should allocate more memory that depends on whether this server is dedicated database server or some other applications also run in it.
If it used by SQL Server alone, you can set ''max server memory (MB)' to maximum.
July 14, 2003 at 1:43 pm
>>To use AWE memory, you must run the SQL Server 2000 database engine under a Windows 2000 account that has been assigned the Windows 2000 lock pages in memory privilege.
I found a reference to this earlier, and I granted the lock pages in memory right to the account that runs SQL Server a week or so prior to enabling awe, and the server has been rebooted since enabling awe. To my knowledge, this requirement is satisfied.
>>To obtain the correct amount of SQL Server memory usage, you can use the Total Server Memory (KB) performance counter, activated through System Monitor, or select the memory usage from sysperfinfo.
Here is what I see on the server:
select cntr_value, cntr_type from sysperfinfo
where
object_name = 'SQLServer:Memory Manager' and
counter_name = 'Total Server Memory (KB)'
cntr_value cntr_type
----------- -----------
2265952 65536
>>You may consider add the /3gb parameter to the boot.ini file. This allows user applications to address 3 GB of virtual memory and reserves 1 GB of virtual memory for the operating system. In your situation, your application (SQL Server) will be able to use up to 5 GB memory.
I discussed using /3GB with one of our technical specialists here prior to enabling awe, and he discouraged me from using it. He said:
quote:
The problem with using the 3GB switch is that the OS is then limited to 1GB and it needs to use additional memory to manage the AWE memory. That's why you see this mentioned in the SQL documentation (and similar lines in other AWE docs.)"In order to allow AWE to use the memory range above 16 GB, be sure the /3gb parameter is not in the boot.ini file. If it is, Windows 2000 will be unable to address any memory above 16 GB. When allocating SQL Server AWE memory on a 32-GB system, Windows 2000 may require at least 1 GB of available memory to manage AWE."
Of course, we can't have more than 8GB with Advanced Server 2000, but it would still require a good chunk of the 1GB left to the OS to manage the AWE memory. I think we should run without the 3GB switch and see how well it works. If we decide to turn it on, we'll have to look for signs that the OS is running out of memory for itself (like blue screens).
What is your take on this?
>>The problem in your configuration is the 'max server memory (MB)', you allocated only 2200 MB to you SQL Server. You should allocate more memory that depends on whether this server is dedicated database server or some other applications also run in it. If it used by SQL Server alone, you can set ''max server memory (MB)' to maximum.
Actually, before we made a change, SQL Server was consuming 1.7 GB of memory. By setting 'max server memory' to 2200, we were attempting to give SQL Server a significant boost (~30%) in available RAM without starving a large application, which accesses the databases and also resides on the server.
July 14, 2003 at 7:00 pm
Unlike many of the other counters available for monitoring SQL Server, this counter averages the Buffer Cache Hit Ratio from the time the last instance of SQL Server was restarted. In other words, this counter is not a real-time measurement, but an average of all the days since SQL Server was last restarted. Because of this, if you really want to get an accurate record of what is happening in your Buffer Cache right now, you must stop and restart the SQL Server service, then letting SQL Server run several hours of normal activity before you check this figure.
quote:
I discussed using /3GB with one of our technical specialists here prior to enabling awe, and he discouraged me from using it. He said:quote:
--------------------------------------------------------------------------------
The problem with using the 3GB switch is that the OS is then limited to 1GB and it needs to use additional memory to manage the AWE memory. That's why you see this mentioned in the SQL documentation (and similar lines in other AWE docs.)
"In order to allow AWE to use the memory range above 16 GB, be sure the /3gb parameter is not in the boot.ini file. If it is, Windows 2000 will be unable to address any memory above 16 GB. When allocating SQL Server AWE memory on a 32-GB system, Windows 2000 may require at least 1 GB of available memory to manage AWE."
Of course, we can't have more than 8GB with Advanced Server 2000, but it would still require a good chunk of the 1GB left to the OS to manage the AWE memory. I think we should run without the 3GB switch and see how well it works. If we decide to turn it on, we'll have to look for signs that the OS is running out of memory for itself (like blue screens).
--------------------------------------------------------------------------------
What is your take on this?
I agreed with your technical specialist that you need to monitor system carefully and think enabling /3Gb and AWE with less than 8GB physical memory shouldn't be an issue. We have a sever running 12 GB memoey and /3GB enabled.
quote:
Actually, before we made a change, SQL Server was consuming 1.7 GB of memory. By setting 'max server memory' to 2200, we were attempting to give SQL Server a significant boost (~30%) in available RAM without starving a large application, which accesses the databases and also resides on the server.
You may consider to allocate more memory to SQL Server. In order to decide how much more you should increase, use performance monitor to monitor memeory : available bytes (MB) counter to see how much physical memory havn't been used. Meanwhile, you should also monitor memory comsumption by your large application on counters process : working set and working set peak. The application might benefit from /3GB enabled.
Are you running some kind of ERP application?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply