June 8, 2009 at 11:18 pm
I just started supporting one server x64 bit SQL 2005 with 16 gigs of RAM , I see there is no AWE enabled from sp_configure and neither Max Memory is set , but still dbcc memorystatus and
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks
shows that it has 14 gig of "AWE "
Issue is this server is commiting all 16 gig of RAM on the server , now if i want to change this AWE value where from i can change?
Thanks
June 8, 2009 at 11:24 pm
On X64 bit systems, AWE settings are ignored(it's used in 32 bit systems to allow sql server to use more than 4 GB of memory). What you need to do here is set the max server memory that SQL can use for itself as per your requirements.
June 8, 2009 at 11:35 pm
Thanks , but in x64 bit this AWE setting will be used as "locked pages" , so 14 gigs is still being used by SQL Server as locked pages on buffer pool , correct?
I am just wondering where from this setting is coming if no Max Memory is defined , and how i can change this
do i need to change max memory to override this awe ?
June 8, 2009 at 11:39 pm
You can use sp_configure to set max server memory.
sp_configure 'Max server memory', value
reconfigure
As Ps said, 64 bit is not affected by AWE settings. Once you have configured the memory check for target server memory, and total server memory counters
June 8, 2009 at 11:57 pm
Let me clear some doubts :
---------------------------
AWE is only meant for 32 bit architecture because of its virtual memory limitaion of 4 GB .For 64 bit the virtual memory is more than you can imagine (multiply 2 with itself 64 times).
So there is no need to enable AWE in sp_configure .Even if you enable it , it will be ignored .Just to add to your knowledge (even though not required here)AWE alone is not sufficient and you also need to enable /PAE in the boot.ini file in order for the OS to see more than 4 GB of RAM.
Point 2 : Lock pages in memory is the OS setting .If you add the SQL Server service account in the LPIM security setting , OS will not trim SQL Server's workign set and it will remian in RAM .
This is not a good practice but sometimes unavoidable .FOr eg. If we have an issue of SQL Server working set trimming , any person with incomplete knowledge will use LIPM ...even if you want to enable it , set the Max server memory to 70%-75% of the total RAM.
Point 3: SQL Server works on dynamic memory allocation and de-allocation .Its not going to release the memory all of a sudden , assuming that it might need it again and thus avoids the resource wastage in memory allocation .
To check how much SQL Server is consuming use perfmon > SQL Server :Memory manager (Total server memory and target server memory counters).
So , ideally the settign should be :
-> Cap you Max and min server memory
-> Do not use LIPM for SQL Server
-> no need to use AWE ..
-> check perfmon for SQL memory usage ...
HTH
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 9, 2009 at 10:49 am
Hi,
The best explanation I found, Microsoft used the 'AWE' param for other thing (to use locked pages in memory).
http://blogs.msdn.com/slavao/archive/2005/11/15/493019.aspx
Regards
June 9, 2009 at 10:21 pm
I have been through this blog post by Slava many times (i am ex-microsoft)..this was published in 2005 when SQL Server 2005 was RTMed.
from this post i disagree with the following sentence :
When running with locked pages enabled, SQL Server monitors box wide memory state and does release memory back to OS in the case of pressure. The key here is that internally SQL Server makes its own decision about what memory can be freed. Internal decision making significantly helps SQL Server to avoid performance degradation in cases of box wide memory pressure. Keep in mind that SQL Server won't release its memory below 'min server memory' specified through sp_configure.
When you use LIPM for SQL Server .OS will not trim SQL's working set .So SQL will not release the memory back to OS .For example , you have 32 GB of RAM and you set max server memory to 32 GB or say 30GB .You also add SQL Server service account to LIPM .this is like committing sucide as OS will start killing other services once SQL Server memory reaches its maximum (remember unlike SQL 2000 it will not grab entire 32 GB at the begening) .
In order to release the memory a SQL API sends a request to OS memory manager and asks if it wants the memory .But as we have added SQL Server in LIPM , SQL is not the candidate for the OS to be asked to release the RAM or say Trim the working set .
So i think that sentence needs to be corrected .
Regards
Abhay
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 11, 2009 at 2:54 am
An article from the Windows Server Performance Team suggests that the use of Lock Pages in Memory is often harmful to overall performance.
In situations where Lock Pages in Memory seems to be beneficial to SQL Server, a greater performance boost would be obtained by reducing SQL Server memory. See http://blogs.technet.com/askperf/archive/2008/03/25/lock-pages-in-memory-do-you-really-need-it.aspx.
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
June 11, 2009 at 5:21 am
Hey all,
Have been reading all the posts and thought i'd chime in a point that i think has been overlooked.
We have an a/p 64-bit with 64gb ram and it runs normally at 51.4gb ram usage. Most of this is sql, however sql allocated memory is 36.7% free (allocated but not used).
What we have found is that sql allocates two lots of memory, awe (for lack of a better description) and sql.exe. The max memory setting governs how much memory awe can use (which is giving back under pressure) but this value is not linked to the sql.exe memory allocation (which in our case runs at about a gb).
Just looking at taskmanager, you cant see where the memory has gone but if you look in depth at the counters and buffers, it all adds up.
Adam Zacks-------------------------------------------Be Nice, Or Leave
June 11, 2009 at 11:11 am
@edvassie: thanks for the reference to the article, this article explains very well what happens when you use AWE on x64 SQL Server Enterprise Ed. And answers the original question.
It tells that you must be careful with blindly setting max server memory while using the 'lock pages in memory' privilige (for SS EntED on x64) , since it can affect other apps.
But I can't read from the article that it can give a 'performance boost' if you don't use it as you suggest. Or am I missing something?
If you use it carfuly, it's simply a faster way of reading data from disk into memory.
June 12, 2009 at 4:28 am
My reading of the article is that if you over-allocate memory then Windows will run slower than if memory is not over-allocated.
You may find that adding LPIM to the SQL service account appears to protect SQL performance compared to not having it BUT...
SQL will still be affected by slow windows performance due to lack of overall memory. If you reduce the amount of memory being requested, Windows will run faster allowing SQL to run faster.
Therefore, my reading of the article is that if you ever have a situation where LPIM 'protects' SQL performance you probably have an underlying memory overallocation problem, and that maximum SQL performance will be obtained by reducing SQL max memory to the point the LPIM no longer is needed.
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
June 12, 2009 at 9:15 am
Here's something else to add to the argument:
Configure SQL Server memory for best practices.
Sounds easy, right? Go into SQL Server Management Studio, right-click on the server name and click Properties, go into Memory, and just configure it. There’s only a couple of fields – how hard could it be?
SQL Server 2005 memory settings
Oh, this screen is full of danger and pitfalls.
First, that tricky checkbox that says “Enable AWE”. You might think you only have to check that box if you’re using a 32-bit server with more than 4 gigs of memory, but nooooo. According to a couple of Microsoft folks I’ve spoken with (and I don’t mean first line support), that checkbox causes SQL Server to handle memory differently – not just enable AWE. In fact, that box should be checked on 64-bit servers, too – any box with more than 4 gigs of memory!
Second, the minimum and maximum memory amounts are important, especially since we gave the SQL Server account the permission to lock its pages in memory. If other applications are running on this server, we need to specify how much memory we want SQL Server to take.
Ideally, no one would ever remote desktop into a SQL Server and run programs. Unfortunately, this happens, and we have to plan for it by leaving enough free memory for people to run things like SQL Server Management Studio. When I’m first building a server that isn’t running any other applications at all, I like to leave 10% of the memory free, or 4gb, whichever is larger. Then I monitor the free memory over the course of a month or two, and adjust it up or down during the next outage window.
If the server does multiple duties like act as a web server or application server, we have to be much more conservative with memory. Application owners never seem to know how much memory they’ll really use in production: SAP BW’s Netweaver, for example, tends to use anywhere from 10% to 50% of the memory on our production server, and it’s tough to predict. As a result, we have to leave the SQL Server’s memory allocation at just 50% of the available memory on the server.
The only way to know the right answer long term is to use Perfmon or a performance monitoring utility to watch the server’s free memory. I’ve written up a separate blog post on using Perfmon for SQL Server monitoring.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply