May 25, 2006 at 9:27 am
By default SQL Server uses 2GB memory. Does anyone know which stored procedure or data dicitonary table can show me the default value or current value of maximum memory SQL Server can use?
thanks
May 25, 2006 at 9:38 am
Try this:
sp_configure 'max server memory (MB)'
That will give you the min, max, current and run values. Current and run values might differ if you've just changed the value but not restarted the SQL Server. So run value is the true value of what it's set at.
May 25, 2006 at 10:17 am
this is what I get when I run sp_configure 'max server memory (MB)'
name minimum maximum config_value run_value
max server memory (MB) 4 2147483647 2147483647 2147483647
what does run value mean in this case since the value for minimum, maximum, config_value, run_value are all the same?
thanks
May 25, 2006 at 10:32 am
Run_value is your current setting. When you change a configuration setting, it shows up under config_value. At that point, run_value and config_value would hold different values. When the new setting takes effect, either by restarting SQL Server or running RECONFIGURE depending on what setting you are changing, the run_value and config_value will again be the same.
May 25, 2006 at 10:32 am
The minimum possible value is 4 MB, the maximum possible value you can set is 2 GB, the config value is set to 2 GB and the run value is set to 2 GB.
The important value is the run value - that's what it's currently set to. If you changed the max memory to 1 GB, the config value would read 1 GB and the run value would ready 2 GB until you restarted the services.
Hope that helps,
May 25, 2006 at 12:04 pm
Makes sense. Now we are planning to change the boot.ini file so we SQL Server can use upto 3 GB of memory.
I was wondering if there is anything I need to do within SQL Server to tell it that it can use 3 GB of memory now .. instead of 2 GB?
thanks a bunch.
May 25, 2006 at 1:03 pm
You cannot do anything at the SQL Server level to get it to use 3 GB of memory because that is dependent on the processor you use and the operating system. In any 32 bit systems, the application is given 2 GB virtual address space(VAS) and the operating system keeps 2 GB VAS. You would have to add the /3GB switch in the boot.ini so that your application can get 3 GB and the OS keeps only 1 GB.
May 25, 2006 at 1:10 pm
What you just said makes sense. However my question was along the lines of what steps do I need to take to set AWE enabled in SQL Server.
After we make changes to boot.ini with /3 GB switch. I plan to run following in Query Analyzer
exec sp_configure 'awe enabled', 1
exec sp_configure 'min server memory (MB)', 3072
exec sp_configure 'max server memory (MB)', 3072
and possibly:
exec sp_configure 'set working set size', 1
now if the steps above are correct and in right sequence. Do I need to restart SQL Server Instance? Also I am running a 4 Node Cluster. Do I need to do anything on other nodes? thanks
May 25, 2006 at 1:18 pm
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 3072
RECONFIGURE
GO
You dont have to do anything speific on other nodes to enable AWE. Since SQL stops to manage memory dyamically and will grab all the 3 GB allocated to it, You would have to restart the instance of SQL Server.
May 25, 2006 at 1:28 pm
Ok here is my list of steps ... Please let me know what you think. Once we change the boot.ini file following steps would be taken
1 - Grant the SQL service
account (cannot be local system) the ability to ‘lock pages in memory’ in local security settings
2 - Grant SQL the ability to use the AWE extensions.
In SQL query analyzer, with the master database as the target run
Sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
3 - Configure SQL to use the new memory
sp_configure 'max server memory', 3072
RECONFIGURE
GO
4 – Stop and restart SQL server.
Do I need to restart every node after change to boot.ini file? As far as I have understood answer is Yes.
thanks
May 25, 2006 at 1:37 pm
The sequence of steps you listed out is accurate. When you say "restart every node" , make sure you "restart SQL Server" which is different in this case because when you bring a node down, SQL would failover to a different node. When this happens, SQL server restarts on the second node and starts up in AWE mode. you dont have to bother about failing it over to all the nodes. SQL will start in AWE mode when it failsover (provided there is enough memory to grab)
May 25, 2006 at 3:08 pm
You do not need to enable AWE to use the /3GB switch. If you only have 4GB of memory, AWE cannot be enabled. Also, once you have added the /3GB switch in the boot.ini file, you must bounce the server to get the OS to re-read the boot.ini file. If you have already done this and you are just trying to adjuct the Max Server Memory option, you do not need to bounce the server or restart SQL Server. If you are only attempting to adjust Max Server Memory, run RECONFIGURE WITH OVERRIDE after your sp_configure command.
May 25, 2006 at 3:10 pm
Just another thought, if you are running Windows Server 2000 Standard edition, you will not be able to use the /3GB switch either.
May 25, 2006 at 3:32 pm
well I am using windows 2003 server enterprise edition and sql server 2000 enterprise edition.
By the way I am now confused since John is suggesting i dont need to set AWE. however I think you have to reset awe.
John would you tell us why it doesnt need to be set. how would sql know that it can go above 2 gigs?
May 25, 2006 at 3:50 pm
I know from experience. I have configured servers to use the /3GB switch without enabling AWE and have been able to verify, through monitoring the Total and Target Server Memory counters, that SQL Server has 3GB available to it.
Also, BOL tells us that AWE is only for memory above 4 GB:
Standard 32-bit addresses can map a maximum of 4 GB of memory. The standard address spaces of 32-bit Microsoft Windows NT® 4.0 and Windows 2000 processes are therefore limited to 4-GB. By default, 2 GB is reserved for the operating system, and 2 GB is made available to the application. If you specify a /3GB switch in the Boot.ini file of Windows NT Enterprise Edition or Windows 2000 Advanced Server, the operating system reserves only 1 GB of the address space, and the application can access up to 3 GB. For more information about the /3GB switch, see Windows NT Enterprise Edition or Windows 2000 Advanced Server Help.
There are many more forum discussions on this topic on SSC and SS Performance and Microsoft that should support this. I would say try for yourself and use the PerfMon counters to verify the results.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply