<div>
Thanks reb73, I believe that was the explanation I was looking for. &nbsp;Going a bit further, is there any performance advantage to setting the 'set working set size' option on?
</div>


<div>
Not really, unless you are worried about enough memory not being available for SQL Server when required. &nbsp;If this is a dedicated SQL Server, then you wouln't have to set the working set size..
</div>


<div>
From the Trouble Shooting and performance white paper :<br />
<br />
AWE mechanism technically is not necessary on 64-bit platform. It is, however, present there. Memory pages that are allocated through the AWE mechanism are referred as locked pages on the 64-bit platform. <br />
<br />
On both 32- and 64-bit platforms, memory that is allocated through the AWE mechanism cannot be paged out. This can be beneficial to the application. (This is one of the reasons for using AWE mechanism on 64-bit platform.) This also affects the amount of RAM that is available to the system and to other applications, which may have detrimental effects. For this reason, in order to use AWE, the Lock Pages in Memory privilege must be enabled for the account that runs SQL Server. <br />
From a troubleshooting perspective, an important point is that the SQL Server buffer pool uses AWE mapped memory; however, only database (hashed) pages can take full advantage of memory allocated through AWE. Memory allocated through the AWE mechanism is not reported by Task Manager or in the Process: Private Bytes performance counter. You need to use SQL Server specific counters or Dynamic Management Views to obtain this information.<br />
For more information about AWE mapped memory, see Managing memory for large databases&nbsp;and Memory Architecture&nbsp;in SQL Server Books Online &nbsp;topics and Large Memory Support (<a href="http://msdn.microsoft.com/library/en-us/memory/base/large_memory_support.asp" rel="ugc">http://msdn.microsoft.com/library/en-us/memory/base/large_memory_support.asp</a>) on MSDN.<br />
<br />
<br />
Note that the Mem Usage column in Task Manager and corresponding performance counters do not count memory that is allocated through AWE. Thus the information is insufficient if AWE is enabled. In this case, you need to look at the memory distribution inside SQL Server to get a full picture.<br />
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.<br />
<br />
select &nbsp; &nbsp; sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb] <br />
from &nbsp; &nbsp; &nbsp; &nbsp;sys.dm_os_memory_clerks<br />
<br />
Note that in SQL Server, currently only buffer pool clerks (type = MEMORYCLERK_SQLBUFFERPOOL<wbr />) use this mechanism and only when AWE is enabled.<br />

</div>


<div>
Bullshite - did it again...
</div>


<div>
And it is not entirely accurate !!
</div>


<div>
Mark, Are your comments related to the information in my accepted post? 
</div>


<div>
Yep...<br />
<br />
If the same value is specified for both min server memory and max server memory, then once the memory allocated to the Database Engine reaches that value, the Database Engine stops dynamically freeing and acquiring memory for the buffer pool.<br />
<br />
In fact MIN and MAX is not the memory that SQL Server needs to use, commonly misunderstood concept - as you allude to - they are configuration options to establish upper and lower limits to the amount of memory used by the buffer pool of the Microsoft SQL Server Database Engine<br />
<br />
set working set size Option is still present in the sp_configure stored procedure, but its functionality is unavailable in Microsoft SQL Server 2005. (The setting has no effect.)<br />
<br />
AWE is being measured, so, AWE has been enabled which does require Windows Lock Pages in Memory policy to be set for the account running SQL Server Service.<br />
<br />
SQL Server buffer pool uses AWE mapped memory; however, only database (hashed) pages can take full advantage of memory allocated through AWE. <br />
<br />
<br />
Also see : <a href="http://blogs.msdn.com/boduff/archive/2008/03/31/sql-server-memory-architecture-myth-busting-and-how-sad-is-awe-32-bit.aspx" rel="ugc">http://blogs.msdn.com/boduff/archive/2008/03/31/sql-server-memory-architecture-myth-busting-and-how-sad-is-awe-32-bit.aspx</a><br />

</div>


<div>
@Mark sounds right to me...... &nbsp;Just my 2 cents.
</div>


<div>
chuckboone -&gt; <br />
<br />
I did provide you information that I believed to be correct, but my understanding in not entirely accurate and Mark has clarified that with detailed reasons. Can you please review the information provided by mark_wills and re-evaluate your options in accepting the correct solution.<br />
<br />
Thanks!<br />

</div>


<div>
@samiam41: where did you come from ?<br />
<br />
@reb73: that is an honorable and noble thing.<br />
<br />
@chuckbone: your answer is it has not yet hit 12 gig.<br />
<br />
And unless you have very good reasons, reset those MAX and MIN memory settings for all the previous postings. You are not doing SQL or the machine any favours. The measure that you should be looking at is PRIVATE BYTES, WORKING SET, TOTAL SERVER MEMORY and TARGET MEMORY - that is what SQL Server would like to be able to use. <br />
<br />
SQL also requires memory for (and there are other things) :<br />
- The SQL Server worker threads<br />
- The Multipage Allocator of SQL Server Memory Manager<br />
- Various DLLs and components that the SQL Server process loads within the address space of the SQL Server process<br />
- The backup and restore operations<br />
<br />
So, unless you are really on top of it, and understand the impacts and implications, don't change settings until you do understand, and have the metrics in place to identify exactly what you need to change... Chances are it is more the operating environment than some of those internal settings (e.g. hyperthreading)<br />
<br />
Finally, in closing, read this ALL the way through : <a href="http://support.microsoft.com/kb/918483" rel="ugc">http://support.microsoft.com/kb/918483</a><br />
<br />
<br />

</div>


<div>
@mark_wills -- thanks so much for your sage advice. &nbsp;This whole bit started with what appeared to be a very minor performance issue (or more likely a network or application problem), then upon investigating wondering why SQL didn't appear to be using the memory available to it. &nbsp;Thanks again.
</div>


<div>
Very professional responses from everyone. &nbsp;Great technical explanation Mark. &nbsp;Very well written..
</div>


<div>
<div class="content wysiwyg-content">
I have a Win2003 Enterprise x64 with 16GB RAM, and SQL 2005 Enterprise x64. &nbsp;I don't believe SQL is using all of the memory that I've allocated to it. &nbsp;If I do a DBCC MEMORYSTATUS I get this:<br />
<br />
VM Reserved = 17115768<br />
VM Committed = 321336<br />
AWE Allocated = 7143424<br />
Reserved Memory = 1024<br />
Reserved Memory In Use = 0<br />
<br />
and looking at the output from sp_configure I see:<br />
<br />
max server memory (MB) = 12288<br />
min server memory (MB) = 12288<br />
<br />
It's my understanding that the AWE Allocated on this platform is the amount of pages locked in memory, but why is this around 7GB instead of being closer to 12? &nbsp;I also see available memory on the server (via perfmon - available MBytes) is around 7GB.<br />
<br />
Thanks for your help.
</div>
</div>


I have a Win2003 Enterprise x64 with 16GB RAM, and SQL 2005 Enterprise x64.  I don't believe SQL is using all of the memory that I've allocated to it.  If I do a DBCC MEMORYSTATUS I get this:

VM Reserved = 17115768
VM Committed = 321336
AWE Allocated = 7143424
Reserved Memory = 1024
Reserved Memory In Use = 0

and looking at the output from sp_configure I see:

max server memory (MB) = 12288
min server memory (MB) = 12288

It's my understanding that the AWE Allocated on this platform is the amount of pages locked in memory, but why is this around 7GB instead of being closer to 12?  I also see available memory on the server (via perfmon - available MBytes) is around 7GB.

Thanks for your help.

Memory usage on MS SQL 2005 x64 Enterprise

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.