February 7, 2008 at 11:32 am
We have a server running SQL 2005 SP2 on Windows 2003 Standard with 4gb of memory and the /3GB boot.ini option. It serves about 50GB of data across several databases.
Due to an old configuration change and a recent crash that caused us to restart SQL, it is now using AWE.
Before, SQL Server would slowly build up it's memory usage to the maximum amount it could grab, and it would just hover around there. Now AWE is reserving all of the RAM we have specified as the max server memory, but we can't find much evidence of it using all of the RAM. It's currently set for 2,800 MB and has successfully reserved it.
Looking at the memory counters,
Connection Memory (KB) 2mb
Lock Memory (KB) 4mb
Optimizer Memory (KB) 1.2mb
SQL Cache Memory (KB) 36mb
Maximum Workspace Memory (KB) 790mb
Granted Workspace Memory (KB) 0
Total Server Memory (KB) 2867MB
I've only recently started monitoring the Page life expectancy one we noticed this memory change, and it usually drops to 0 several times an hour. Anytime a user runs a report it is zeroed out.
The memory pages/sec counter is 0, and the Checkpoint pages/sec is 0 most of the time (it remains 0 about 80% of the time when the PLE drops).
Buffer cache hit ratio is 99% and cache hit ratio is 98%. The working set for sqlservr is 240mb.
From what I have read, the PLE drops, and low average during the day (because of the drops) can indicate that we need more memory. However, from the other memory counters, it doesn't look like it is making use of what we already have. Am I missing something? Is there anything else I need to check? Is there any advantages or disadvantages for using AWE in our configuration?
We aren't having huge performance issues, but queries don't seem to be caching very long and our disk usage is higher because of it.
February 7, 2008 at 1:56 pm
Barrett Owen (2/7/2008)
Before, SQL Server would slowly build up it's memory usage to the maximum amount it could grab, and it would just hover around there. Now AWE is reserving all of the RAM we have specified as the max server memory, but we can't find much evidence of it using all of the RAM. It's currently set for 2,800 MB and has successfully reserved it.
This is one of the biggest draw backs of AWE ... I've asked Microsoft many a times and never been told a solid answer, but to my knowledge you'll never know how much memory is truly allocated to SQL with AWE enabled. Regardless, even without AWE enabled ... you still don't know. As you watch the memory usage increase, the memory isn't actually in use at all times by SQL Server, it is just reserved, which does not help to truly determine how much memory SQL needs to properly run. This is a large topic that I'd personally like to know more about as I have yet to get any real answers. I've heard many different perspectives from both Microsoft and the user community, but there still does not seem to be a definitive answer (that I know of anyways).
Am I missing something? Is there anything else I need to check? Is there any advantages or disadvantages for using AWE in our configuration?
For only having 4GB of memory on the server, I don't really see any reason to have it on. You should only need to enable it if you have more than 4GB on an x86 system.
Regardless, if you are using AWE, does your sql service account have access tolock pages in memory? Without it, AWE will not work properly.
February 7, 2008 at 2:07 pm
Adam Bean (2/7/2008)
Regardless, if you are using AWE, does your sql service account have access tolock pages in memory? Without it, AWE will not work properly.
Yep and I also verified the message in the logs. "Address Windowing Extensions is enabled. This is an informational message only; no user action is required."
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply