SQL Performance Tips for MEMCM – Part 2

Tips for optimizing SQL Server performance for MEMCM – Part 1 started with the Windows Server platform. This blog covers the optimizing performance for SQL Server from the focus of MEMCM.

Give me more speed!

SQL Server

Recommended SQL Server settings

 SQL Server Version

Always run MEMCM using the latest support SQL version. Keep SQL Server up to date with the latest Cumulative Update. These contain bug fixes, and potential performance enhancements. Follow this site for the latest updates: Latest updates for Microsoft SQL Server

 Memory settings – Max/min

On occasion, it may be appropriate to add additional memory to the server platform. For new installations, or increased memory, be sure to adjust SQL Server Max memory usage when additional memory is added to the Windows server.

Always modify the default “Minimum server memory (in MB)” and the “Maximum server memory (in MB)” values. The minimum server memory value should be set to 8GB and the maximum value to a fixed value (reference Server Properties property page).

General recommendation is to not let SQL Server use all available server memory. Under certain conditions, SQL Server may not release memory back to the Operating System. The following SQL Server properties sheet (example) defines the minimum as 8GB and the maximum as 32GB.

SQL Server proe

Note: If MEMCM is collocated with SQL Server on the same Windows Server, add an additional 8GB memory for MEMCM.

Avoid page file swapping

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. This should allow a minimum of 4-6 GB of remaining memory for the Server OS. Monitor the Windows Server memory usage. Ideally, the server should not be using the server page file, as this will lead to performance degradation. Validate with Performance Monitor or third party tools, if available.

MAXDOP

SQL Server MAX Degree Of Parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the computing and thread resources that are used for the query plan operators that perform the work in parallel.

For the most part the recommendation, MAXDOP should be set to 0.

SQL Server MAXDOP and effect on ConfigMgr | Steve Thompson [MVP] (wordpress.com)

Latest document indicates similar findings with some specific recommendations for very large sites:

Site size and performance FAQ – Configuration Manager | Microsoft Docs

Configuration Manager site size and performance guidelines

This article is a must read for site sizing and performance guidelines. This article focuses on the largest contributor to Configuration Manager performance bottlenecks: the disk input/output subsystem or IOPS. Highly recommended based on projected site sizes. Includes references for Azure VMs.

Updating Indexes and Statistics

Keep your Indexes and Statistics updated. This one item, could very well be the most important in improving and keeping the performance of your CM database running optimally. Ola Hallengren SQL Server Index and Statistics Maintenance scripts are the best solution out there. In part because they are open source, and you can fine tune the way they work. I’m not certain if the MEMCM index maintenance tasks have been corrected, however, in the past these are known to fail to update.

There are a few different ways to implement these, first documented the process in 2013! Since then, I’ve received numerous replies indicating how well this solution works.

An even easier way to implement is using the updated deployment: Create the Optimize Database Solution using PowerShell and dbatools

Database compatibility level

Some console performance issues are related to the database compatibility level. Please read Ander’s blog for details on this issue:

MEMCM Console Slow Performance Caused by SQL Compatibility Level

Maintenance Plans

CM Database Backup considerations. While not exactly a performance issue, it’s important to backup the SQL Server database(s) and CM cd.latest folder. Use these guidelines.

Please add comments if there are things that I’ve either overlooked, or you have found that made a huge difference in performance?

References:

Sql-recommendations-for-MECM/SQL recommendations for MECM – White Paper v2.6.pdf at master · stephaneserero/Sql-recommendations-for-MECM · GitHub

https://docs.microsoft.com/en-us/mem/configmgr/core/plan-design/configs/site-size-performance-guidelines

Advertisement