March 31, 2009 at 6:43 am
I have a 32 bit OS with 8GB of RAM and SQL 2005 SP2 EE. I enabled both PAE and AWE on the box and rebooted. I noticed the page file is 12GB, however the memory usage for SQL is still at 1.7GB. I have provided Lock pages in memory privilege as well.
Also I capped max server memory at 6GB and min server memory is set to 1GB.
What am I missing here?
March 31, 2009 at 6:48 am
Do you see the message "Address Windowing Extensions enabled." in your sql server error logs?
--Ramesh
March 31, 2009 at 6:54 am
Before the reboot...
Configuration option 'awe enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.
Post the reboot...
Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
output from sp_configure 'awe enabled'
awe enabled0110
I made the PAE change and AWE configuration change (incl. the RECONFIGURE) and then rebooted.
March 31, 2009 at 7:12 am
SA.. as Ramesh asked above, do you see that entry on your SQL Server log? This would indicate that SQL Server is properly taking the changes and actually using them.....
March 31, 2009 at 7:15 am
So, it means the the SQL Server is not running in AWE mode.
Run these T-SQL commands to enable AWE:
EXECUTE sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXECUTE sp_configure 'awe enabled', 1
GO
RECONFIGURE;
GO
After changing the settings, restart the SQL Server and check if SQL Server is in AWE mode or not using:
EXECUTE sp_configure 'awe enabled'
GO
It should have the values 0, 1, 1, 1
Then revert the advanced options settings:
EXECUTE sp_configure 'show advanced options', 0;
GO
RECONFIGURE;
GO
--Ramesh
March 31, 2009 at 7:17 am
How are you determining how much memory SQL Server is using?
The task manager will not show memory that is allocated via AWE.
The best way to see how much memory SQL Server is actually using is to look at the Windows Performance Monitor and add the SQL Server counters, Total Server Memory and Target Server Memory.
Alternatively you could use the DBCC command:
DBCC MEMORYSTATUS
but this can be confusing if you do not know what you are looking for.
Let us know how you get on.
Cheers,
March 31, 2009 at 7:50 am
Richard,
There is no log entry that says AWE enabled.
John,
I was looking in Task Manager to see how much memory SQL is using. I looked at the Total Server and Target Server memory counters and they are reading 1.6GB + for each of these. This leads me to believe that the AWE configuration hasn't taken effect.
I will need to schedule a SQL Server restart this week and see if it helps.
Thanks for all your help, I will revert back with my finding.
March 31, 2009 at 8:02 am
That will certainly help as you need to restart the SQL Server Service after you have enabled the AWE configuration option, in order for it to take effect.
You should then see a message in your SQL Server Log similar to the following:
"Address Windowing Extensions is enabled. This is an informational message only; no user action is required."
Hope this helps.
Cheers,
April 2, 2009 at 3:54 am
One thing to check is if the /3GB switch is present in the startup parameters for the server.
Complete apologies, this was not meant to have been here.
April 2, 2009 at 6:25 am
The /3GB switch isn't part of server start up. I thought /3Gb and / PAE are exclusive. Is that not true?
April 2, 2009 at 7:23 am
The /3GB switch is not the answer to your problems. It would however permit SQL Server to have access to an additional 1GB of memory should you wish.
By enabling the /3GB switch all you would be doing is reducing the amount of memory currently available to the OS Kernel from 2GB down to 1GB.
Once you have re-started SQL Server you will see the effects of AWE memory allocation take affect. You can confirm the memory allocation of SQL Server by looking at the Windows Performance Monitor and checking the SQL Server counters, Total Server Memory and Target Server Memory.
Hope this helps but let me know if you have any questions.
Cheers,
April 2, 2009 at 7:30 am
i have this scheduled for next weekend...will keep updating...
April 2, 2009 at 12:34 pm
You can use the sys.dm_os_memory_clerks DMV as follows to find out how much memory SQL Server has allocated through AWE mechanism.
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks
April 3, 2009 at 3:34 am
The Windows Task Manager on a 32-bit server only ever knows about memory use below the 4GB line, so it will never show you how much AWE memory is in use by SQL Server.
You say you have 8GB on the box, have SQL memory capped at 6GB and do not have the /3GB flag in boot.ini.
Without the /3GB flag, SQL is limited to using 2GB memory below the 4GB line. You have another 4GB memory above that line (maybe), so you are asking SQL to allocate all possible memory. There may be problems with this request.
1) The box may have 8GB installed, but most servers have a ILO card (or similar) that takes up to 300 MB away from Windows. This would mean that Windows with PAE enabled can only address 7.7GB, and taking 2GB away for the Windows kernel leaves a maximum of 5.7GB to be allocated to a AWE-aware application.
2) When SQL starts up and tries to allocate the AWE memory, it only makes 1 request for the memory, and will only get it if Windows can supply that memory as a contiguous chunk. If you ask for 6GB memory, it may not be available.
3) As others have said, the /3GB and /PAE flags are not exclusive.
The /PAE flag enables the Windows extentions that allows it to make memory above the 4GB line available to AWE-aware programs. The PAE extentions are also used by the data execution protection features of Windows, so if you have the /NOEXECUTE=OPTOUT flag in boot.ini you also have implicitly enabled the full PAE feature set.
The /3GB flag restricts the Windows kernel to using only 1GB of memory, leaving 3GB below the 4GB line to be used by applications. You can use the /3GB flag on a 32-bit server that has less than 16 GB memory, although if the server is heavily loaded it may become unstable if you have /3GB and more than 12 GB memory. Although you can safely use the /3GB flag on a server with only (say) 512MB memory, it does not have any impact intil you have more than 3GB memory on the server.
Memory above the 4GB line on a 32-bit server is not freely available to applications, because they cannot directly address it. For high-end memory, Windows assigns a page number to each 4KB segment of memory above the 4GB line. An application needs to be AWE-aware to make use of this memory. SQL Server can use this memory for its database buffer pool but nothing else. When a AWE-aware application wants to access one of these 4KB segments, it asks Windows to make it available. Windows then copies the segment to a location below the 4GB line and tells the application where to find it. The application can then read and write to this copy as required. Windows has a limited pool of memory to hold these copied segments, and uses a least-recently-used algorithm to decide when a segment is stale. If the segment is stale and has been updated, Windows writes it back to its original location. If the segment is stale and has not been updated Windows simply overwrites it with a new segment. In both cases it tells the application that used this segment it is no longer available.
You can probably guess that keeping track of segments over the 4GB line takes some Windows memory, which is why you cannot use the /3GB flag if you have more than 16GB memory. Windows needs more than 1GB memory if the box has more than 16GB memory.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 3, 2009 at 5:10 am
what is the OS version/edition you are using?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 44 total)
You must be logged in to reply to this topic. Login to reply