/3Gb switch not working :-(
-
Hi guys,
Production Environment is
Hardware:Compaq ML 530 with 2X 2.8Ghz Xeon and 4GB of RAM
O/s:WIN 2K Adv Server SP4
SQL 2K sp3a - Single default server instance
Here is what I just did on the production server:-
Insert a line in the boot.ini that says "/3gb"
From EM changed the max memory to be 3006MB
Reboot the box
Run sp_configure 'max server memory' and it says:
max server memory (MB) 4 2147483647 3006 3006
Windows task manager reports 4Gb physical RAM
Available RAM always hangs around 1.96Gb
The processes tab on task manager reports sqlserver.exe always using 1.8Gb đ .
Please can someone tell me I've missed a step here, why on earth can't I get sql to use 3Gb of RAM.
Regards,
Uday
-
this from BOL :
awe enabled Option
In MicrosoftŽ SQL Server⢠2000, you can use the Microsoft WindowsŽ 2000 Address Windowing Extensions (AWE) API to support up to a maximum of 64 gigabytes (GB) of physical memory. The specific amount of memory you can use depends on hardware configuration and operating system support.
Note This feature is available only in the SQL Server 2000 Enterprise and Developer editions.
Enabling AWE
To enable AWE, set awe enabled to 1. SQL Server will reserve almost all available memory, leaving 128 megabytes (MB) or less, unless a value has been specified for max server memory.
If the option has been successfully enabled, the message "Address Windowing Extension enabled" is printed in the SQL Server error log when the instance of SQL Server 2000 is started.
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.
Disabling AWE
To disable AWE, set awe enabled to 0. This setting is the default. The AWE API is not used. SQL Server 2000 operates in a normal dynamic memory allocation mode and is limited to 3 GB of physical memory.
Usage Considerations
Before enabling AWE, consider the following:
When awe enabled is set to 1, instances of SQL Server 2000 do not dynamically manage the size of the address space. SQL Server will reserve and lock almost all available memory (or the value of max server memory if the option has been set) when the server is started. It is strongly recommended that you set a value for the max server memory option each time you enable AWE. Otherwise other applications or instances of SQL Server 2000 will have less than 128 MB of physical memory in which to run.
If the total available memory is less than 3 GB, the instance of SQL Server 2000 will be started in non-AWE mode even if awe enabled is set to 1. In this situation, you do not need to manage AWE memory because dynamic memory allocation is used automatically.
You can determine the amount of memory you can safely allocate to instances of SQL Server 2000 by identifying how much memory is available after all other applications to be used on the computer have been started.
Use the SQL Server Performance Monitor Total Server Memory (KB) counter to determine how much memory is allocated by the instance of SQL Server running in AWE mode. Configure the max server memory option to leave some additional memory free to allow for the varying needs of other applications and Windows 2000. For more information, see Monitoring Memory Usage.
Important Using the awe enabled option and the max server memory setting can have a performance impact on other applications or on SQL Server running in a multi-instance or cluster environment. For more information about using AWE memory, see Managing AWE Memory.
Example
The following example shows how to enable AWE and configure a limit of 6 GB for max server memory:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
-
What version of SQL Server are you using?
You need Enterprise Edition or Developer Edition to go above 2gb
-
AWE is not enabled for 4GB or less.....you just use the /3GB switch in the boot.ini
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
Hi Ford,
Yes, fully aware of AWE settings and I have *not* enabled this. Only used the /3Gb entry in boot.ini. No luck what so ever.
Rgds,
Uday
-
Ian,
SQL 2K Ent Edition is what I 'm using.
Thanks,
Uday
-
Did you find a resolution for this? I have a new Windows 2003 server with 4G memory running SQL Server 2000 SP4. I also use the /3G switch in the boot.ini file but Task manager says sqlservr.exe is using 1.8G. This is the same as before I added the /3G switch and rebooted the server.
Francis
-
fhanlon,
Please take a screen capture of perfmon with the following counters in it : SQLServer Total Memory & Target memory and email it to me. Also email me output of sp_configure from your server. I will be able to tell you what the story is after I see this output.
Here is my story and resolution. It turns out that I'm running W2K Std Edition as indicated by the boot.ini file entry. I did'nt trust that entry and tried to work around that issue. But, if I'm running W2K o/s it has to be either W2K ADV/DATA-CENTRE editions. Once this is confirmed, all I have to do is have the "/3GB" switch in the boot.ini file and every thing will be sweet.
Rgds,
Uday
-
In perf mon both target server memory and total server memory are 1662704.
This is sp_configure
name minimum maximum config_value run_value
----------------------------------- ----------- ----------- ------------ -----------
affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
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 2147483647 2147483647
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 32767 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
Max Server memory in sp_configure is set higher that what perfmon says I'm getting?
The boot.ini file says:
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /3G /fastdetect
Francis
-
Sometimes it helps to see everything all at once. Now that I look at the boot.ini I see the error (I think) I have /3G when I should have put /3GB
Damn.. now I need to schedule another restart
Francis
-
So, did that fix in the boot.ini file work for you?
Rgds,
Uday
-
Yes it works fine now. Thanks
Francis
-
To see how much memory SQL server is using, instead of relying on the task manager, it would be better to rely on the total server memory and target server memory counters in the performance monitor.
-
Good point Manju, I think I mentioned it to the previous poster. I was badly stuck cuz, it took me a while to work out that I was running W2K std edition. I can't get any memory setting changes done with this o/s.
Rgds,
Uday
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply