2005 Memory Management Questions

I've read and noticed SQL 2005 handles memory differently then 2000. In 2000 if I told a server it had 6GB to use, it allocated the memory. In 2005 I have one 32-bit server with 6GB of memory and one 64-bit server with 32 GB. If Target Server Memory is the amount of memory SQL Server would like to have, how does that correspond to Maximum Server Memory? Also, how is Target Server Memory determined?

32-bit
Physical Memory = 8GB
Target Server Memory = 6GB (Willing to consume)
Total Server Memory = 690MB (Currently consuming)
Minimum Server Memory = 2GB
Maximum Server Memory = 6GB

For the 32-bit server the Target Server Memory matches Maximum Server Memory

64-bit
Physical Memory = 32GB
Target Server Memory = 28GB (Willing to consume)
Total Server Memory = 397MB (Currently consuming)
Minimum Server Memory = 4GB
Maximum Server Memory = 30GB

For the 64-bit server the Target Server Memory is less then the Maximum Server Memory

Lock Pages in Memory is set for the service account. Neither server above has yet to be released to production and only the 32-bit server has any users. In 2000 when SQL Server started I could count on it using about 1.72GB of memory immediately. Seeing the servers above consume only only 690MB and 397MB has me concerned. Is this just a case of SQL Server 2005 handling memory better then 2000?

Thanks, Dave

Hi,

Is this Enterprise Edition of SQL Server that we are talking about here?

So if you had AWE enabled this would still showup as 1.7 GB as the AWE mapped memory would not show up under the visible region and you would not be able to view this from the Task Manager counter.

Also, SQL Server 2005 Enterprise Edition is designed to handle dynamic AWE memory releasing and acquiring as and when required.

Slava Oaks Weblog on MSDN has a clear explanation on how SQL Server manages memory for both 2000 and 2005 and 32 and 64 bit installations.

DBCC MEMORYSTATUS would be a more definitive way of looking at SQL Server Memory consumption. (http://support.microsoft.com/default.aspx?scid=kb;EN-US;907877)

The 1.7 GB that you see is the Visible Buffer Pool to SQL Server.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;274750

http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx

HTH|||

Hi,

Is this Enterprise Edition of SQL Server that we are talking about here?

So if you had AWE enabled this would still showup as 1.7 GB as the AWE mapped memory would not show up under the visible region and you would not be able to view this from the Task Manager counter.

Also, SQL Server 2005 Enterprise Edition is designed to handle dynamic AWE memory releasing and acquiring as and when required.

Slava Oaks Weblog on MSDN has a clear explanation on how SQL Server manages memory for both 2000 and 2005 and 32 and 64 bit installations.

DBCC MEMORYSTATUS would be a more definitive way of looking at SQL Server Memory consumption. (http://support.microsoft.com/default.aspx?scid=kb;EN-US;907877)

Helpful links:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;274750

http://msdn2.microsoft.com/en-us/library/aa175282(SQL.80).aspx

HTH|||

I'm using Enterprise Edition. The OS is Server 2003 EE. PAE is set and AWE is enabled for the 32-bit server. I've run DBCC MEMORYSTATUS and also perfmon SQL Server: Memory Manager - Target Server Memory and Total Server Memory as noted in my post. I do not see 1.7GB. For example, "AWE Allocated" under DBCC MEMORYSTATUS shows just over 500MB being used by my 64-bit server. I'm guessing the 1.7GB of memory is no longer needed for SQL 2005 at startup like it is for SQL Server 2000.

Thanks, Dave

|||

SQL Server 2005 Enterprise Edition is designed to manage AWE memory dynamically. It will acquire and release memory as and when required. The AWE mapped table is maintained in the Buffer Pool which maps AWE pages to the RAM. And also it would be advisable to enable Lock Pages in Memory for the SQL Server startup account to prevent inadvertent trimming of the SQL Server working set.