July 26, 2004 at 9:20 am
I have enabled AWE and assigned 5120MB of memory on the Enterprised server last week, the total Physical RAM is 8GB. Everything is fine after enabled AWE, but the memory utilization kept growing and reached 5270MB today. Why could it exceed 5120MB?
July 26, 2004 at 10:28 am
Did you assign the min and max to 5120?
Check sp_configure.
July 26, 2004 at 12:28 pm
I used the following script to assign max to 5120, but I could figure out why it can use more than (5120*1024)=5242880KB, it reachs 5256856KB and the number is still growing.
sp_configure 'show advanced options', 1RECONFIGUREGOsp_configure 'awe enabled', 1RECONFIGUREGOsp_configure 'max server memory', 5120RECONFIGUREGO
July 26, 2004 at 12:55 pm
Just curiously, How do you know the memory usage is increasing ......?
July 26, 2004 at 1:07 pm
I queried sysperfinfo; Also get Netiq report on SQL server service memory utilization in one hour interval.
July 26, 2004 at 1:47 pm
"awe enabled is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change awe enabled only when show advanced options is set to 1. You must restart the instance of SQL Server 2000 for changes to take effect."
Did you restart the server?
Can you post result of "execute sp_configure" here?
July 26, 2004 at 1:55 pm
| affinity mask | -2.147E+09 | 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 | 0 | 0 |
| locks | 5000 | 2147483647 | 0 | 0 |
| max degree of parallelism | 0 | 32 | 0 | 0 |
| max server memory (MB) | 4 | 2147483647 | 5120 | 5120 |
| 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 | 20 | 20 |
| remote proc trans | 0 | 1 | 0 | 0 |
| remote query timeout (s) | 0 | 2147483647 | 600 | 600 |
| scan for startup procs | 0 | 1 | 0 | 0 |
| 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 |
July 27, 2004 at 8:00 am
You might want to look at the article below.
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B555064
July 27, 2004 at 2:56 pm
Unfortunately BOL is not very clear: I would set min and max memory both to the same value to limit the amount of memory that SQL Server uses when AWE is enabled. With an 8 GB server, we have min and max set to 6144 so that it can only use 6 GB of memory. Once memory is paged to SQL Server, it is not released back to the OS. However, SQL Server will map / unmap it (just watch the AWE performance counters) and use it.
Apparently, using a low min value like 0 and the upper max value does not work with AWE. It will likely use all the memory save 128 Mb for the OS. BOL says something to that affect.
If SQL Server is the only application on the machine, you can probably go higher. We've had it as high as 7.5 GB for SQL Server, leaving 500 Mb for the OS and everything else. If you have hot swapping, you may need to leave more memory for the OS and hot swapping. If you have another instance on the machine, you may need to lower it. Though, the AWE memory can only be used for pages and not some other system objects that SQL Server uses, so too many instances on the same machine may be an issue.
July 30, 2004 at 1:27 pm
Actually, that is normal behavior for SQL server.
http://support.microsoft.com/default.aspx?scid=kb;en-us;321363
I'd just lower the Max vaule just a bit to have other extended store procedures have space to use that memory.
August 4, 2004 at 7:32 am
Note also that any Windows operating System , there is Virtual memory put on Harddisk that is the file PAGEFILE.SYS (i think its size is added to your real Memory )
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply