SQL Server Performance Forum – Threads Archive
Windows 2003 Ent and SQL 2000 Ent max memory
So I have read that Windows 2003 Enterprise Edition supports up to 32G of memory using AWE. I also read that for over 16G, you cannot use the 3G switch because the OS needs 2 to manage it. So is this correct for Windows 2003 EE and SQL 2000 EE? between 4G-8G, /PAE and /3GB in boot.ini and awe enabled in SQL to limit OS to 1 and let SQL use 3G-7G between 8G-32G, /PAE in boot.ini and awe enabled in SQL to let SQL use 6-30G (Can SQL2000 EE on Windows 2003 EE use close to 30G of RAM?)your 1st statement is correct, but then you inserted 8GB instead of 16GB in your next 2 statements
so, yes, s2k ee and w2k3 ee can use 32GB together (2GB for OS, 30GB for SQL)
the key consideration is the 2-3GB lower memory is the only memory (more precisely, address space) that can be used for most data structures,
i believe the memory above 4GB can be used for buffer cache (data), not proc cache or others. this means awe memory is most useful for OLTP applications, where the primary memory usage is for buffering data pages,
but awe is not effective for DSS apps that need very large intermediate data structures, for this, you consider a true 64-bit solution
Fantastic. Thank you for the quick response.
One additional question. I have found lots of articles on procedure cache and buffer cache, but nothing really that lists the physical limits of their sizes (the 4GB you reference). Anyone have information on it?
Searched in Technet?
I will post if I found any reference. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I am trying to set the AWE settings in win2k3 enterprise edition…I can find the bootcfg.exe application but not the boot.ini file. I am assuming that I use the bootcfg.exe app to set AWE. Can you tell me how I go about adding the /PAE switch as well as the /3GB switch? My server has 8gb of RAM and I would like sql server 2k to access up to 4gb of that…
All help appreciated!
Thank you,
Michael
Your boot.ini file is probably just hidden. You may have to change your settings to see it. Tools -> Folder Options -> View Tab -> Hidden Files and Folders -> Show Hidden Files and Folders. It will be in the root of C by default. It is also set to read only by default, so make sure to uncheck that option in the file properties before editing. Just append /PAE and /3GB to the boot option. The line in the boot.ini may look something like this.
multi(0)disk(0)rdisk(0)partition(2)WINNT="Microsoft Windows 2000 Advanced Server" /fastdetect /PAE /3GB
Satya, yes, I have searched technet, SQL books online, MSDN and the web trying to find something on it. From what I have found, it looks like with SQL2000 (unlike 7) the address space is shared between both the data and proc cache. I couldn’t find anything about a hard limit for address space use in any case, but it could be something burried in a text book or not currently well documented.
Thank you! I couldn’t find it because the ‘Hide Protected System Files’ option was enabled…got it now! Thanks again…
Michael
I have set my boot.ini file parameters accordingly with the /PAE and /3GB switches. When I run sp_configure (with no params to see how everything is set) it tells me that the maximum memory is 2147483647 (2.14gb?) and config value is 4096 with a run value of 4096. The 4096 is consistent with the settings set via EM. Shouldn’t the maximum memory setting now be something like 4096000000 (roughly 4GB) when I run sp_configure?
Thanks,
Michael
With the /3GB switch on, the OS gets 1G and all other apps get 3G of the standard 4G. Since you want SQL to use more than 3G, you have to enable SQL to use the AWE memory space you enabled with the /PAE switch. 1 – Grant the SQL service account (cannot be local system) the ability to ‘lock pages in memory#%92 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
4 – Stop and restart SQL server. Note:
Task manager will not accurately display AWE allocated memory utilization. You must use performance monitor to accurately pull the SQL memory usage. Using performance monitor, select SQLServer: Memory Manager and choose the Target Server Memory and Total Server Memory counters. If the counter levels are not at the desired value, SQL is not configured properly (after SQL startup, it may take a couple minutes for SQL to acquire all the RAM)
I enabled sql server to use 4GB of RAM and everything seems to be working fine now…last question – do I need to remove the /3GB switch from the boot.ini file since I have sql set to use 4GB of RAM?
Thanks for the info on Perf Monitor vrs Task Manager…never would have caught that one!
Thank you,
Michael
It is really up to you on how to devide up the 4G of non-SQL RAM. You can either leave 3G for apps and 1G for the OS (using the /3GB switch) or you can leave 2G for apps and 2G for the OS. The /3GB switch limits the OS to 1G instead of the default 2G. Since you have 8G total, implementing the /3GB switch leaves 7G for applications.
Thank you…that makes perfect sense to me now. Any idea why SQL Server would disappear from the list of objects to monitor in Performance Monitor?
I’ve seen all kinds of problems with SQL counters, especially if you have SQL 2000, sp3 installed. Check out this article and see if you have the same symptoms.
http://support.microsoft.com/default.aspx?scid=kb;en-us;812915
We just put in a new server with 6GB of RAM. I set SQL 2Ksp3 max memory value at 4 GB, put in /3GB/PAE in my boot.ini, enabled AWE and granted privliges to the account running sql to enable locking in memory. My performance tanked. A job that normally runs in 5 minutes without AWE enabled took nearly 3 hours. Is there something more I’m missing? We are assuming the locking in memory is locking OS memory and not freeing it back up but were not sure. Haven’t been able to find anything on Microsoft to suggest we did not set things up properly but something is definitely wrong. Any suggestions?? Terry Crosby
Can you verify if SQL is using 4G of RAM or not by using the SQL counters? This would at least tell you if you have all the AWE settings correct. You may also want to lose the /3GB switch, move SQL max memory to 3G and see if anything changes. I haven’t heard of this specifically. Matt
Using Task manager you can look at it for current usage, but using PERFMON counters will give you definete information about resource usage. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
We use Spotlight for SQL server and I can verify that SQL is using 4.8 GB. What other AWE setting are there? All we did was enable AWE, grant privileges to the startup account. I verified in the SQL log that AWE wae running. I have a feeling I’m missing something. Would I really want to drop the /3GB switch? Terry Crosby
If SQL is using more than 4G, it is using AWE memory, no doubt about it. It is an all or nothing thing. I do see that you set the max mem for SQL to 4G, but you monitored it using 4.8? Just for testing, I think I would get back to the basics. Remove both the AWE and 3G settings and set SQL to use just 2G max. Run the queryjob again and try to rule out AWE. There could be another subsystem that is actually causing the problem. If this is more of a production server and can’t be tinkered with, just try to troubleshoot each component (disk, CPU, memory, etc). There are some pretty good articles on this site includinghttp://www.sql-server-performance.com/sg_sql_server_performance_article.asp. You can also try using SQL profiler to track the resource usage of the individual query. Trying to diagnose a problem with profiler may be fairly difficult without playing with filters and it may be a tedious process.
Actually, we did set max memory to 4.8, figuring 1GB for the OS was sufficient. SQL is the only thing running on this server and yes, it’s the production box. Once we disabled AWE, performance shot back up to normal, quicker processing. All articles I’ve read pointed to a very simple setup, which we followed. I wouldn’t think more memory would be a bad thing so I’m like totally confused!! Terry Crosby
That does sound quite strange. Maybe now try backing SQL down to 3G with the AWE and /3G switch enabled. Don’t forget about all those misc processes that run on most SQL servers like backups, monitoring, hareware agents, etc which could be causing contention. I think there may be some SQL related processes that operate outside of the max memory too and this could be causing some resource problems (maybe SQL full text, etc). Can you also isolate the problem to the one job? IE – do other queries also perform much more slowly when AWE is enable?
Terry, not sure if you subscribed to this topic or not, but there has been some more discussion about problems associated with the /AWE and /3G switch on another thread http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=2501). I saw problems with large files and both of these boot options. I am wondering if your problems could have been related…
Biged123456, I encountered a problem in max memory setting. My server is W2K advanced version with SQL2K Ent version and 7600 MB RAM. The boot.ini has /3GB /PAE and AWE is set to 1 (as discussed above). However, when I sp_configure ‘max server memory’ above 5678 MB (ex. 6154 MB), the SQL error log showing a warning message ‘can not initiate 6154 MB memory’. Could you please kindly help me out.
You mention it appears to work below 5678M. I would set the max memory at or under this value and confirm SQL is using the memory using performance monitor. If it is using over 4G, then the OS and SQL are configured properly to use more than 4G. If both are configured correctly, then it could be possible that you simply don’t have enough available memory for SQL to go over 5678M. You have 7600M in the system and the OS is reserving 1G with the /3GB enabled. This leaves a bit over 6500M for SQL and all other apps. It could be that other apps (backups, monitoring, etc) are using this memory. More likely, it could be that the /3GB switch is not working and the OS is grabbing 2G (especially with the max memory settings you mention). I haven’t found anything on how to confirm if /3GB is working or not, but am sure there is a memory counter that may shed some light on it.
Hi biged123456. Thanks a lot for your valuabled information. Is it possible for you switch to another thread (AWE Enable and 8 GB RAM) which I have opened? I am most appreciated for your help.
]]>