Configuring SQL Server memory for ConfigMgr 2012

A common SQL Server configuration error is allowing SQL Server to use all available memory.

Te default “Maximum server memory (in MB)” setting (highlighted) allows SQL Server use ALL available memory. For the most part, SQL does a good job of managing the available memory, however, best practice is setting a maximum available memory.

image

For a Windows Server dedicated for use by SQL Server, general rule of thumb is to allocate approximately 80-90% of available server memory to SQL Server. Monitor the Windows Server memory usage.

If you are running ConfigMgr 2012 on the same server with SQL Server; I’d recommend allowing about 8GB of memory for ConfigMgr and the Windows Server OS, then allocate the remainder of memory to SQL Server by using the Maximum Server memory setting shown earlier. If possible, allocate in even 1024MB chunks.

For example:

Windows Server has 32GB total memory, and is running ConfigMgr 2012 and SQL Server. Subtract 8GB memory for the Server OS and ConfigMgr 2012. The remainder should be used for SQL.

32GB – 8GB = 26GB (SQL Server)

26GB * 1024 = 28672 (MB – enter in SQL property sheet)

image

Ideally, the server should not be using the server page file, as this will lead to performance degradation. Monitor Windows Server memory usage and make adjustments if needed.

Advertisements
This entry was posted in ConfigMgr, SQL Server, SQL Server 2012. Bookmark the permalink.

One Response to Configuring SQL Server memory for ConfigMgr 2012

  1. Pingback: How to monitor Windows Server memory usage | Steve Thompson [MVP]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s