Configuration Manager site sizing and performance FAQ

Separate the Configuration Manager inboxes and SQL Server files on at least two different volumes. This separation lets you optimize cluster allocation sizes for the different kinds of I/O they perform.

For the volume hosting your sites server inboxes, use NTFS with 4K or 8K allocation units. ReFS writes 64k even for small files. Configuration Manager has many small files, so ReFS can produce unnecessary disk overhead.

For disks containing SQL Server database files, use either NTFS or ReFS formatting, with 64K allocation units.

Modern arrays of solid-state drives (SSD) and Azure Premium Storage can provide high IOPS on a single volume, with few disks. You typically add more drives to an array for additional storage, not additional throughput. If you're using physical spindle-based disks, you may need more IOPS than you can generate on a single volume. You should allocate 60% of the total recommended IOPS and disk space for the .mdf file, 20% for the .ldf file, and 20% for the log and data temp files. The .ldf and temp files can all reside on a single volume with 40% (20% + 20%) of your allocated IOPS.

SQL Server versions earlier than SQL Server 2016 created by default only one temp data file. You should create more, to avoid SQL Server locks and waiting for access to a single file. Community opinions vary on the best number of temp data files to create, from four to eight. Testing reveals little difference between four to eight, so you can create four equally sized temp data files. Your tempdb data files should be up to 20-25% the size of your full database.

When configurable, set RAID controller memory to 70% allocation for write operations and 30% for read operations. In general, use a RAID 10 array configuration for the site database. RAID 1 is also acceptable for small-scale sites with low I/O requirements, or if you use fast SSDs. With larger disk arrays, configure spare disks to automatically replace failing disks.

Example: Physical machine with physical disks

Sizing guidelines for a colocated site server and SQL Server with 100,000 clients are 1200 IOPS for site server inboxes and 5000 IOPS for SQL Server files.

Your resulting disk configuration might look like:

1. Doesn't include recommended spare disks.
2. This value is from Example disk configurations.

Hyper-V delivers similar performance to a physical server, if hardware resources (CPU cores and pass-through storage) are 100% dedicated to the virtual machine (VM). Using fixed-size .vhd or .vhdx disk files causes a minimal 1-5% I/O performance impact. Using dynamically expanding .vhd or .vhdx disk files causes up to 25% I/O performance impact for the Configuration Manager workload. If you need dynamically expanding disks, compensate by adding an additional 25% IOPS performance to the array.

When running your Configuration Manager site server or SQL Server inside a VM, isolate the Hyper-V host OS drives from the VM OS and data drives.

For more information about optimizing VMs, see Performance Tuning Hyper-V Servers.

Example: Hyper-V VM-based site server

Sizing guidelines for a colocated site server and SQL Server with 150,000 clients are 1800 IOPS for site server inboxes and 7400 IOPS for SQL Server files.

Your resulting disk configuration might look like:

1. Doesn't include recommended spare disks.
2. Fixed-size, pass-through .vhdx for the VM drive dedicated to the underlying volume.
3. This value is from Example disk configurations.

Start by reading the Configuration Manager on Azure frequently asked questions.

Azure infrastructure as a service (IaaS) VMs that leverage Premium Storage-based disks can have high IOPS. On these VMs, configure additional disks for anticipated disk space needs, rather than for additional IOPS.

Azure storage is inherently redundant and doesn't require multiple disks for availability. You can stripe disks in Disk Manager or Storage Spaces to provide additional space and performance.

For more information and recommendations on how to maximize Premium Storage performance and run SQL Servers in Azure IaaS VMs, see:

• Optimize application performance

• Disks guidance

Example: Azure-based site server

Sizing guidelines for a colocated site server and SQL Server with 50,000 clients are eight cores, 32 GB, and 1200 IOPS for site server inboxes, and 2800 IOPS for SQL Server files.

Your resulting Azure machine might be a DS13v2 (eight cores, 56 GB) with the following disk configuration:

1. This value is from Example disk configurations.
2. Azure guidance allows for placing the TempDB on the local, SSD-based D: drive, given it won't exceed available space and allows for additional disk I/O distribution.

Example: Azure-based site server (for instant performance increase)

Azure disk throughput is limited by the size of the VM. The configuration in the preceding Azure example may limit future expansion or additional performance. If you add additional disks during initial deployment of your Azure VM, you can upsize your Azure VM for increased processing power in the future, with minimal upfront investment. It's much simpler to plan ahead to increase site performance as requirements change, instead of later needing to do a more complicated migration.

Change the disks in the preceding Azure example to see how the IOPS change.

DS13v2

1. Disks are striped using Storage Spaces.
2. This value is from Example disk configurations. VM size limits performance.
3. Azure guidance allows for placing the TempDB on the local, SSD-based D: drive, given it won't exceed available space and allows for additional disk I/O distribution.

If you need more performance in future, you can upsize your VM to a DS14v2, which will double CPU and memory. The additional disk bandwidth allowed by that VM size will also instantly boost the available disk IOPS on your previously configured disks.

DS14v2

1. Disks are striped using Storage Spaces.
2. This value is from Example disk configurations. VM size limits performance.
3. Azure guidance allows for placing the TempDB on the local, SSD-based D: drive, given it won't exceed available space and allows for additional disk I/O distribution.

Both can perform adequately, assuming the single server is appropriately sized, or network connectivity is sufficient between the two servers.

Remote SQL Server requires the upfront and operational cost of an additional server, but is typical among the majority of large-scale customers. Benefits of this configuration include:

• Increased site availability options, such as SQL Server Always On
• Ability to run heavy reporting with less overheard to site processing
• Simpler disaster recovery in some situations
• Easier security management
• Role separation for SQL Server management, such as with a separate DBA team

Colocated SQL Server requires a single server, and is typical for most small-scale customers. Benefits of this configuration include:

• Lower costs for machines, licenses, and maintenance
• Fewer points of failure in the site
• Better control for planning downtime

By default, SQL Server uses all available memory on your server, potentially starving the OS and other processes on the machine. To avoid potential performance issues, it's important to allocate memory to SQL Server explicitly. On site servers colocated with SQL Server, make sure the OS has enough RAM for file caching and other operations. Make sure there's enough RAM remaining for SMSExec and other Configuration Manager processes. When running SQL Server on a remote server, you can allocate the majority of the memory to SQL, but not all. Review the sizing guidelines for initial guidance.

SQL Server memory allocation should be rounded to whole GB. Also, as RAM increases to large amounts, you can let SQL Server have a higher percentage. For example, when 256 GB or more of RAM is available, you can configure SQL Server for up to 95%, as that still preserves plenty of memory for the OS. Monitoring the page file is a good way to ensure there is enough memory for the OS and any Configuration Manager processes.

You may run into memory contention issues if there are more than 16 physical cores and not enough RAM on your SQL Server. The Configuration Manager workload performs better when at least 3-4 GB of RAM per core is available for SQL. When adding cores to your SQL Server, be sure to increase RAM in proportional amounts.

In general, availability groups have negligible effect on performance of the system when sufficient networking is available between the replica servers. You can have rapid database log .ldf file growth in a busy availability group environment. However, log file space is automatically released after a successful database backup. Add a SQL Server job for the Configuration Manager database to perform a backup, for example every 24 hours, and an .ldf backup every six hours. For more information about availability groups and Configuration Manager, including more about SQL Server backup strategies, see Prepare to use a SQL Server Always On availability group.

SQL Server compression isn't recommended for the Configuration Manager database. While there are no functional issues with enabling compression on a Configuration Manager database, test results don't show much size savings compared to the potential sizable performance impact to the system.

Any secrets in the Configuration Manager database are already stored securely, but adding SQL Server encryption can add yet another layer of security. There are no functional issues with enabling encryption on your database, but there can be up to a 25% performance degradation. Therefore, encrypt with caution, especially in large-scale environments. Also remember to update your backup and recovery plans to ensure you can successfully recover the encrypted data.

For supported versions of SQL, see Support for SQL Server versions. From a performance standpoint, all supported versions of SQL Server meet required performance criteria. However, SQL Server 2016 or newer tends to outperform SQL Server 2014 in some aspects of the Configuration Manager workload. Also, running SQL Server 2014 at SQL Server 2012 compatibility level (110) improves performance in general. At installation time, Configuration Manager databases running on SQL Server 2014 are set to compatibility level 110. SQL Server 2016 or newer is set to that SQL Server version's default compatibility level, such as 130 for SQL Server 2016. Upgrading SQL Server in place doesn't update compatibility levels until you install the next major Configuration Manager current branch version.

If you see unusual timeouts or slowness on certain SQL queries on SQL Server 2016 or later, such as when using RBAC in the Admin Console, try changing the SQL Server compatibility level on the Configuration Manager database to 110. Running at SQL Server compatibility level 110 on SQL Server 2014 and newer versions of SQL Server is fully supported. For more information, see SQL query times out or console slow on certain Configuration Manager database queries.

As of January 2018, you should avoid the following SQL Server versions, because of various known performance-related or other potential issues:

• SQL Server 2012 SP3 CU1 to CU5
• SQL Server 2014 SP1 CU6 to SP2 CU2
• SQL Server 2016 RTM to CU3, SP1 CU3 to CU5

Yes, update indexes as often as once a week and statistics as often as once a day to improve SQL Server performance. Third-party scripts and additional information available from the Configuration Manager and SQL Server communities can help optimize these tasks.

In large sites, some SQL Server tables, such as CI_CurrentComplianceStatusDetails, HinvChangeLog, might be large, depending on your usage patterns. You may need to reduce or alter your maintenance approach for them one by one.

SQL Server Express doesn't have any significant performance implications on secondary sites, and it's adequate for most customers. It's also easy to deploy and manage, and is the recommended configuration for nearly all customers at any size.

There's one situation where a full SQL Server installation might be needed. If you have a large number of distribution points and packages or sources in your environment, it's possible to exceed the 10-GB size limit of SQL Server Express. If the number of packages times the number of distribution points is more than 4,000,000, such as 2,000 DPs with 2,000 pieces of content, consider using full SQL Server at your secondary sites.

Leaving your setting at 0 (use all available processors) is optimal for overall processing performance in most circumstances.

Many Configuration Manager administrators follow the guidance at Recommendations and guidelines for the "max degree of parallelism" configuration option in SQL Server. On most modern large hardware, this guidance leads to a suggested maximum setting of eight. However, if you run many smaller queries compared to your number of processors, it may help to set it to a higher number. Limiting yourself to eight isn't necessarily the best setting on larger sites when more cores are available.

On SQL Servers with greater than eight cores, start with a setting of 0, and only make changes if you experience performance issues or excessive locking. If you need to change MaxDOP because you are encountering performance issues at 0, start with a new value at least greater than or equal to the minimum recommended number of cores for that site's SQL Server sizing. Going lower than this value nearly always has negative performance implications. For example, a remote SQL Server for a 100,000 client site needs at least 12 cores. If your SQL Server has 16 cores, start testing your MaxDOP setting with a value of 12.

Take care when disabling antivirus protection on any system. In high volume and secure environments, we recommend disabling active monitoring for optimum performance.

For more information about recommended antivirus exclusions, see Recommended antivirus exclusions for Configuration Manager 2012 and Current Branch Site Servers, Site Systems, and Clients.

Changing a few key IIS settings, such as WsusPool Queue Length and WsusPool Private Memory limit, can improve WSUS performance, even on smaller installations. For more information, see Recommended hardware.

Also make sure you have the latest updates installed for the operating system running WSUS:

• Windows Server 2012: Any non "Security only" cumulative update released October 2017 or later. (KB4041690)
• Windows Server 2012 R2: Any non "Security only" cumulative update released August 2017 or later. (KB4039871)
• Window Server 2016: any non "Security only" cumulative update released August 2017 or later. (KB4039396)

See The complete guide to Microsoft WSUS and Configuration Manager SUP maintenance.

Traditional server performance monitoring works effectively for general Configuration Manager. You can also leverage the various System Center Operations Manager management packs for Configuration Manager, SQL Server, and Windows Server to monitor basic health of your servers. You can also directly monitor the Windows Performance Monitor (PerfMon) counters Configuration Manager provides. Monitor the backlogs in the various inboxes for early warning signs of potential site performance issues or backlogs.

See also

• Site sizing and performance guidelines
• Configuration Manager on Azure frequently asked questions