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.
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.
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:
Steve Thompson [MVP] 
Great content, it will helpful for us a lot