Share via

SQL memory config info for ConfigMgr 2012

  • Article

This information came out in RC2 and we'll see if it is changed once ConfigMgr 2012 goes RTM.

Supported versions of SQL Server 2008 for RC2:

· SQL Server 2008 SP2 Standard and Enterprise CU7

· SQL Server 2008 R2 SP1 and CU4

· SQL Server Express 2008 R2 and CU4 (secondary sites)

 

https://technet.microsoft.com/en-us/library/gg682077.aspx#BKMK_SiteSysReqFunction

 

A note about SQL Server Memory Note:

When you use a database server that is co-located with the site server, dedicate 50 percent of the available addressable system memory for SQL Server.

When you use a dedicated SQL Server, dedicate 80 percent of the available addressable system memory for SQL Server.

Configuration Manager requires SQL Server to reserve a minimum of 8 gigabytes (GB) of memory in the buffer pool used by an instance of SQL Server for the central administration site and primary site and a minimum of 4 gigabytes (GB) for the secondary site. This memory is reserved by using the Minimum server memory setting under Server Memory Options and is configured by using SQL Server Management Studio. For more information about how to set a fixed amount of memory, see How to: Set a Fixed Amount of Memory (SQL Server Management Studio).

 

Updating this post 3/20/2015:
Wow, I posted this during CM2012 RC2 timeframe. Seems like a very long time ago.  These same metrics still apply. If you are running SQL server locally on the primary (or CAS), allocate 50% of total memory of the server to the SQL maximum memory setting, leaving the rest for OS, ConfigMgr, etc. General recommendation would be to have enough total memory on the server to set the minimum SQL memory to 8GB (8192MB) and the max to the 50% number. If you are using a dedicated SQL server for the ConfigMgr database, general guidance is to set the maximum to 70-80% of total memory of the server and I would still recommend using 8192MB for the minimum.

Comments

  • Anonymous
    January 01, 2003
    To "Not very helpful" and/or "Found the issue!"

    I'm guessing both posts came from the person, but maybe not. For the first poster, you didn't find me sharing guidance on how to configure min and max sql memory helpful. That is interesting feedback, given that the vast majority of customers I work with are using default values, which is very much a concern. I would think some published guidance on this topic would be quite helpful to most.

    On the index creation memory setting, that setting obviously was not the focus of this post. But, I'll be glad to share my thoughts. You say the value shouldn't be 0 but you don't provide what value you think it should be. In my experience, and in most scenarios, the default value of 0 is the recommended setting. With a value of 0, SQL dynamically manages the memory allocated for creating indexes and if it needs more memory to create indexes and memory is available it takes it. Perfect. And some verbatim from Techet (https://technet.microsoft.com/en-us/library/ms175123(v=sql.105).aspx) "The index create memory option is self-configuring and usually works without requiring adjustment"