SCCM Guru Webcast Q&A #4 – Memory Capacity Planning

This is part of the blog series where I finish answering all the questions that came up during my SCCM Guru Webcast session.

QUESTION:
Often, we see SQL Server 2008 SP2, 64-bit consume all of max memory. Multiple blogs discuss that this is by design and there are even suggested tables on setting up upper limits on SQL for SCCM. Please share your knowledge on what is correct and best practice on memory capacity planning and allocation of SQL Server 2008 for SCCM 2007. 

ANSWER:
The best answer is “It Depends” because it really does.  You need to know exactly what’s going to be running on your server and how much memory those things are going to take at their worst.

BACKGROUND:
In SQL, there’s a setting called “max server memory (MB)”.  That setting determines the maximum amount of RAM set aside just for SQL.  By default, this is set to something like 2 PetaBytes, a number higher than the amount of RAM you have in your machine for quite a while.  The SQL folks claim that it’s fine to keep this setting as is for most people because SQL and Windows do a much better job of sharing RAM and allocating/deallocating to meet the demands of the system.  But in practice, I have had problems where if this was left to the default, eventually SQL would consume all RAM and problems ensued.  It seems like I remember someone telling me that the system has to be under HEAVY memory pressure in order to trigger the memory release from SQL.  So, you WILL want to set this to a number lower than the amount of physical RAM you have in the machine. 

I can’t exactly say how much you should reserve for your system because it really depends on what else is on that box besides SQL and it depends if you’re set to 32-bit or 64-bit SQL.  In our case, we have a SCCM server with SQL on the same box and it’s 64-bit.  We reserve 26GB for SQL which leaves 6GB for the OS and ConfigMgr.  We’ve had to tweak it a few times over the years to give SQL less memory and CM more memory.  6GB seems to be just right for us.  Then again, we have 350K clients or more and have 100+ people in the CM console at any one time.  Perhaps 6GB is high for others.  It all depends on what you need.

HOW TO SET MAX SERVER MEMORY

1. Using SQL Server Management Studio
   Right click on the SQL server, click PROPERTIES
   
   Click on the MEMORY page and set the “Maximum server memory (in MB):” value.
   
2. USING TSQL
   Run the following from SSMS:
   (change the N’26000′ to your own value)

EXEC sys.sp_configure N’show advanced options’, N’1′  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’max server memory (MB)’, N’26000′
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N’show advanced options’, N’0′  RECONFIGURE WITH OVERRIDE
GO

SPECIAL 32-BIT CONSIDERATIONS
32-bit operating systems can address up to 4GB of memory space (2^32 = 4294967296).  2GB are for the Kernel (the OS) and 2GB are for user mode programs.  So, if you have a server that’s running a 32-bit OS but you have more than 4GB of RAM, then you need to use the /PAE boot parameter (physical address extensions) in your boot.ini and you need to configure SQL for AWE.  Oh, and if you’re wondering, only use /3GB if you’re told to by product support.  It robs the kernel to get more user mode memory…not necessarily what you want.

PAE (or Physical Address Extensions) is essentially a way for a 32-bit server to see all that memory above 4GB for software that’s able to use it.  That’s a way simplified way of saying it.

AWE (or Address Windowing Extensions) is essentially a way for a 32-bit product like 32-bit SQL Server to do some fancy “memory bookkeeping” to use that PAE memory in 32-bit chunks

SPECIAL 64-BIT CONSIDERATIONS
Obviously, if you CAN use a 64-bit box, you SHOULD use it.  You can easily address all the memory on the system.  About the only major consideration I can think of is to make sure the SQL Server account (the account used to start SQL) is granted the “Lock Pages in Memory” permission.  This will prevent the operating system from paging SQL memory out to the pagefile under pressure.

EXTERNAL LINKS:
/PAE Boot Parameter
http://msdn.microsoft.com/en-us/library/ff557168.aspx

Enabling AWE memory for SQL server
http://msdn.microsoft.com/en-us/library/ms190673.aspx

Enable the Lock Pages in Memory Option
http://msdn.microsoft.com/en-us/library/ms190730.aspx

Enabling Memory Support for Over 4GB of Physical Memory
http://msdn.microsoft.com/en-us/library/ms179301.aspx

Understanding the VAS reservation (MemToLeave) in SQL Server
http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/07/07/understanding-the-vas-reservation-aka-memtoleave-in-sql-server.aspx

INF: Using DBCC MEMORYSTATUS to Monitor SQL Server Memory Usage
http://support.microsoft.com/kb/271624

Number2 (John Nelson) 

MyITForum – Forum Posts
MyITForum – Blog