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

SQL Performance Tips for MEMCM – Part 1

Tips for optimizing SQL Server performance for MEMCM, we’ll start with the Windows Server platform. This is the first in a series of performance tips.

Windows Server

Since SQL Server is installed on Windows Server, and in many cases co-located with MEMCM, it is important to spend the time to configure Windows Server for optimal performance.

Recommended Windows Server/SQL platforms

Use the latest supported Windows Server version, and the latest supported SQL Server version. Keep both platforms up to date with latest security updates and bug fixes. For more information: Supported SQL Server versions – Configuration Manager | Microsoft Docs

Performance Power Setting – check control panel setting, in the past I’ve always recommended High Performance over Power Saver. Windows Server 2022 brings a “balanced” mode, which might be indicated for smaller site servers. For more information: Overview about power and performance tuning for the Windows Server | Microsoft Docs

RAM – make sure you have enough. SQL Server uses memory to cache data and will attempt to keep as much data as possible in memory. Accessing data from memory will always be faster than disk.  For configuration, setting minimum and maximum values, be aware of maximum support memory specifications by edition see the following information: Memory Management Architecture Guide – SQL Server | Microsoft Docs

Monitor page file utilization – objective is to minimize page swapping to disk, which will be inherently slower that accessing memory.

Disk layout

The physical (or virtual) layout of the disk is important. Some general recommendations:

• Install the OS on its own drive.
• All drives used for SQL Data or Log files should be formatted as 64KB NTFS.
• Create a dedicated drive for TempDB files
• Larger environments may need a dedicated drive for the Windows Server page file
• IOPS – have sufficient IOPS to support the activity. Generally speaking, SSD will provide the best performance.

For more information: SQL Server Design Considerations | Microsoft Docs

Recommended hardware

Good article which includes general starting points for Disk I/O performance, available memory and CPU recommendations.  Recommended hardware – Configuration Manager | Microsoft Docs

Next in the series will cover SQL Server configuration settings for performance.

Upgrade MEMCM site Windows Server 2012 to Server 2016 – Lessons learned

Issue

MEMCM was no longer downloading servicing updates. Service updates stuck in downloading state. Site server running on Windows Server 2012, MEMCM build was stuck at v2103..

Planning/Research

Upon investigating, it seemed likely the issue was related to the Windows Server version. Further, Windows Server 2012 needs to first be upgraded to Windows Server 2016. Note: WS 2012 R2 can be upgraded directly to WS 2019.

I’ll include links I found useful at the end of this post. I did note on most upgrade scenarios, a recommendation to remove WSUS from the server prior to the upgrade and keep the SUSDB database. No one is using WID, right? Check that you are on the latest supported SQL Server version for the server build.

Checklist

Prior to running any upgrade, my recommendation is to create a checklist. Here’s mine.

1. Document file content locations for Content Library and WSUS updates.
2. Verify backups of server VM, all DB’s
3. Remove WSUS from server (may need a reboot)
4. Run Windows Server 2016 setup, upgrade option, choose desktop experience and keep current settings/data
5. Post Upgrade, run the post upgrade step >  Roles and features, add WSUS
6. Run MEMCM site reset

Difficulties/Solution

Post upgrade I had difficulties adding the WSUS role back. After a little trial and error, and one link I found (in resources), I needed to choose the option of adding the SUSDB. My concern at the time was it was going to over-write the SUSDB. Of course, I had a backup, but I didn’t want to restore this unless I had too. it turns out that this step did not over-write the database. Several services did not start and were set to manual startup mode (details in resources). Another issue I found was Server 2016 required a few additional features added for everything to install properly (link in resources).

One reboot later, the MEMCM console was properly functional, and the service updates showed that build v2111 and hotfix was ready to install. No more stuck on ‘downloading’ issues.

Ended up not needing a MEMCM site reset.

Resources

https://docs.microsoft.com/en-us/windows-server/upgrade/upgrade-2012-to-2016

https://docs.microsoft.com/en-us/mem/configmgr/core/servers/manage/upgrade-on-premises-infrastructure

Create the Optimize Database Solution using PowerShell and dbatools

As a follow on to a frequently referenced, and still accurate blog post: Optimizing SCCM Databases Revisited – Let’s automate the creation of this optimization solution using PowerShell and DBATools. Let’s get building!

If you have not used dbatools, read Dave’s article: Cool SQL Tricks with DbaTools and MEM ConfigMgr start there, then come back. You’ll need to install the dbatools PowerShell module as he has described, additionally can be found in the references section.

This script will perform the following:

1. Create a new database named ‘DBA’
2. Install the latest version of Ola Hallengren database maintenance solution into ‘DBA”
3. Install a custom SQL Agent job and task specifically with optimum MEMCM database tuning settings
4. Schedule the Agent Job to run weekly on Sunday @ 1:00AM

Note: Couple of updates to this script. Change DB recovery model to simple, change owner to SA. Change variables as appropriate.

 

# Create new database for Database Maintenance plan, install Ola's solution, create and schedule IndexOptimize task
# 8/27/2021
# Author: Steve Thompson

# Change variables as appropriate
$SQLInstance = "localhost"
$DBName = "DBA"

# Create a new database on the localhost named DBA
New-DbaDatabase -SqlInstance $SQLInstance -Name $DBName -Owner sa -RecoveryModel Simple

# Install Ola Hallengrens Database Maintenance solution using the DBA database
Install-DbaMaintenanceSolution -SqlInstance $SQLInstance -Database $DBName -ReplaceExisting -InstallJobs

# Create a new SQL Server Agent Job to schedule the custom Agent Task
New-DbaAgentJob -SqlInstance $SQLInstance -Job OptimizeIndexes -Owner sa -Description 'Ola Hallengren Optimize Indexes' 

# Create a new SQL Agent Task step with the optimal parameters for MEMCM
New-DbaAgentJobStep -SqlInstance $SQLInstance -Job OptimizeIndexes -StepName Step1 -Database $DBName -Command "EXECUTE dbo.IndexOptimize

@Databases = 'USER_DATABASES',

@FragmentationLow = NULL,

@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

@FragmentationLevel1 = 10,

@FragmentationLevel2 = 40,

@UpdateStatistics = 'ALL',

@OnlyModifiedStatistics = 'Y',

@LogToTable = 'Y'"

# Optionally, create a schedule to run the SQL Agent Tast once a week on Sunday @ 1:00AM
New-DbaAgentSchedule -SqlInstance $SQLInstance -Job OptimizeIndexes -Schedule RunWeekly -FrequencyType Weekly -FrequencyInterval Sunday -StartTime 010000 -Force

Closing notes: I’ve not added error handling as I’m not that smart. Consider this a starting point to modify and customize as you wish. All of the command syntax detail used can be found in the dbatools link.

References:

dbatools: https://docs.dbatools.io/

Ola Hallengren SQL Server Maintenance Solution:  https://ola.hallengren.com/

SQL Server recommendations for Microsoft Endpoint Configuration Manager environment Whitepaper Review

The team at Microsoft has done a great job in outlining many of the important SQL Server settings for MEMCM.

If you have not read it, I recommend downloading, reading and implementing these settings.

Update [6-2020]: Here is the latest version of the SQL recommendations for MEMCM Technet gallery will be deprecated, use this link…

There are few things I noticed that I’d do a little differently. I’m not saying that the white paper is wrong, rather DBAs usually have their own opinion about how things should be configured.

Items:

Memory – per the whitepaper it is good to cap the max memory SQL Server will use.  Additionally, set a minimum memory for SQL as well. Recommend 8GB – enter as 8192 MB. [Note]: corrected in the latest version!

TempDB files – completely agree that you need more for MEMCM. Start with 8 tempdb files, of equal size… adjust number up or down as needed. Sizing calculator is included in the following link as well.

Reference: Properly size SQL Server TempDB for ConfigMgr

Index Tuning and Optimization – do not use the SQL maintenance plan. I’m glad they include Ola Hallengrens open source Maintenance Script. Here is how I recommend configuring the Agent job…

Reference: Optimizing SCCM Databases Revisited

What’s new in SQL Server 2019

Now generally available, here’s what’s new.

Each version of SQL Server tends to build and add additional capabilities onto the prior version. SQL Server 2019 is no exception this trend.

Probably the most interesting enhancements are:

SQL Server 2019 introduces Big Data Clusters for SQL Server.

While you can still use SQL Server 2019 in the traditional way, it now expands to offer far more capabilities.

 

New feature or update	Details
Scalable big data solution	Deploy scalable clusters of SQL Server, Spark, and HDFS containers running on Kubernetes. Read, write, and process big data from Transact-SQL or Spark. Easily combine and analyze high-value relational data with high-volume big data. Query external data sources. Store big data in HDFS managed by SQL Server. Query data from multiple external data sources through the cluster. Use the data for AI, machine learning, and other analysis tasks. Deploy and run applications in Big Data Clusters. The SQL Server master instance provides high availability and disaster recovery for all databases by using Always On availability group technology.
Data virtualization with PolyBase	Query data from external SQL Server, Oracle, Teradata, MongoDB, and ODBC data sources with external tables, now with UTF-8 encoding support. For more information, see What is PolyBase?.

What does this mean? Consider the following scenarios:

Scenarios

SQL Server Big Data Clusters provide flexibility in how you interact with your big data. You can query external data sources, store big data in HDFS managed by SQL Server, or query data from multiple external data sources through the cluster. You can then use the data for AI, machine learning, and other analysis tasks. The following sections provide more information about these scenarios.

Data virtualization

By leveraging SQL Server PolyBase, SQL Server Big Data Clusters can query external data sources without moving or copying the data. SQL Server 2019 (15.x) introduces new connectors to data sources.

Data lake

A SQL Server big data cluster includes a scalable HDFS storage pool. This can be used to store big data, potentially ingested from multiple external sources. Once the big data is stored in HDFS in the big data cluster, you can analyze and query the data and combine it with your relational data.

Scale-out data mart

SQL Server Big Data Clusters provide scale-out compute and storage to improve the performance of analyzing any data. Data from a variety of sources can be ingested and distributed across data pool nodes as a cache for further analysis.

Integrated AI and Machine Learning

SQL Server Big Data Clusters enable AI and machine learning tasks on the data stored in HDFS storage pools and the data pools. You can use Spark as well as built-in AI tools in SQL Server, using R, Python, Scala, or Java.

Improvements:

It also provides additional capability and improvements for the SQL Server database engine, SQL Server Analysis Services, SQL Server Machine Learning Services, SQL Server on Linux, and SQL Server Master Data Services.

Resources:

• What’s new in SQL Server 2019

• SQL Server 2019: Not your Grandpa’s SQL Server

The ConfigMgr Accidental DBA

Or, Be Your Own DBA!

An Administrator for Microsoft System Center Configuration Manager (ConfigMgr) either becomes the lead administrator for ConfigMgr because it was planned, or unplanned.

Once they assume control of ConfigMgr, in most cases, they are now the DBA (Database Administrators) as well.

If the ConfigMgr administrator role was planned, then fantastic! The assumption is made that with this planning the admin understands what is required to properly administer the SQL Server component. If the SQL Server component was not really considered, (I’ve seen this a few times) then this article could still help.

After all, ConfigMgr is an application (granted a complex application) that uses SQL Server as the backend to store information on clients, inventory, settings, packages, applications, etc.

If the ConfigMgr administrator role was unplanned – as in; “Your manager reports that Sam the lead ConfigMgr admin left the company to pursue making a fortune as a consultant, and you (Jane) from the help desk have been promoted to lead ConfigMgr Admin (sorry Jane ;)”. Although it could be a great opportunity for Jane. Not only is the newly minted ConfigMgr admin responsible for the CM product, they are now responsible for SQL Server.

The Accidental DBA, or Involuntary DBA, now has to administer SQL Server.

There is another consideration in writing this article, in some companies, the DBA’s administer ALL SQL Server instances. Sometimes the existing DBA’s do a great job at this, other times the DBA will start locking things down to the point where ConfigMgr no longer works, and CM admins can no longer do their jobs!

Another case reported in the community; the DBA team would take longer than 1 week to restore a CM database for Disaster Recovery. In a hierarchy, the database MUST be restored within 3-4 days. A restore greater than 1 week would be completely unacceptable (to me).

A subtitle for “The ConfigMgr Accidental DBA” could be “Be your own DBA!”. Take control of the ConfigMgr SQL Server instance and manage it yourself. If you are new to SQL Server, this article will help you.

IMO, this would be a great use case to start managing your own CM SQL infrastructure.

Now what?

The DBA (Database Administrator) role does require some unique skills. The remainder of this article is providing resources and links (which will likely be updated over time) to assist.

Some immediate suggestions:

Define very clear RTO and RPO guidelines, these will greatly help your ability to recover.

• RTO – Recovery Time Objectives (how long will it take)
• RPO – Recovery Point Objectives (how much data can I afford to loose)

Create a backup plan. Immediately!

Where to start?

Community articles

SQLSkils has a fantastic set of guides written for SQL Server. Writing by leading industry experts, these guides are designed to be consumed 1 day at a time for 30 days. If you are just starting out, you NEED to read and implement the SQL Server Backup section first.

Troubleshooting SQL Server: A Guide for Accidental DBAs – a free ebook is available from Redgate (some awesome tools for SQL BTW), while dated, it still contains information relevant to supporting SQL Server today.

Configuration Manager SQL Server Backup guidelines – make sure you have solid backups in place. Be sure to test restores on a regular basis.

Optimizing SQL Server for ConfigMgr – hey, it’s my blog. You’ll want to get very familiar with Ola Hallengrens’ solution. Once you have a handle on backup, you want it to run fast, right?

Microsoft SQL Server Versions List – Monitor for, and apply updates for SQL Server.

DBATools – PowerShell module. Automate SQL Server tasks…

Community support

Twitter – post your SQL related question with a #sqlhelp hashtag and you should get some assistance.

Member Note: You’re in the right place to get answers from a pretty huge community of SQL Server DBAs – that is invaluable.

Microsoft SQL Server support forums

Community suggestions

In preparing this article, I polled the twitter alias asking what the Accidental DBA needs to know. Some of these points I’ve touched on. For the thread, click here.

Notes from the Twitter alias and SQLSkills link:

You can’t do much better than starting here https://www.sqlskills.com/help/accidental-dba/ … -I would add that you should choose the order that suits you best (imho backups should be first!) #sqlskills #sqlhelp [Mark Broadbent @retracement]

Make sure you set your backup schedule so that you can meet your RTO and RPO. And test your restores. Your backups aren’t any use if they won’t restore. [Frank Gill @skreebydba]

For testing restores, sp_DatabaseRestore is handy, as you can do the restore, run CHECKDB, then drop the restored DB after in a few lines of tSQL. That reduces the space requirements if you perform for each DB serially. Working on a blog post to share an example. [Allen McGuire @SQLTrooper ] + next as well.

If you do it in an Agent job, you can have output files for each DB tested on top of the steps being logged to the CommandLog – part of the free Ola backup/maintenance solution (which sp_DatabaseRestore relies on anyway).

MMS 2019–Planned Sessions

Are coming to one of the premier System Center Configuration Manager events, MMS 2019 (Midwest Management Summit)?

If so, consider coming to see Benjamin Reynolds and myself for one or all of the following sessions. Stop by and say hello.

AMA – Ask Me Anything

Note: we first ran one of these sessions at the MMS Desert Edition and it was very well received. Bring your questions!

Description:
Have a question about SQL, T-SQL, Reporting, or SQL/Site Server HA? Now’s your chance to ask Steve and Benjamin anything you want!
What you will learn:

• SQL (basic or advanced)
• SSRS
• Power BI
• SQL Server HA (Availability Groups)

SQL Performance turning techniques for ConfigMgr and more!

Note: we’ll likely cover suggested server and SQL Server configuration as well.

Description:
Have you ever had SQL queries that ran slow, and weren’t sure what to do? Learn techniques, tips and tricks on how to enhance your SQL Server query performance. How to troubleshoot, available tools, extended events, execution plans and more!
What you will learn:

• Troubleshooting techniques
• What makes a bad vs. good query?
• Indexes – how to identify and how to use?
• Extended events

HA for ConfigMgr/SQL Server Update

Description:
Have you wondered about SQL Always On Availability Groups? What is it? How does SQL Server High Availability benefit your environment? What considerations are involved in the design? How do you install it? Learn how can you use this to extend ConfigMgr high availability capabilities. Tips and tricks. Come learn the latest update to this technology from two industry experts.
What you will learn:

• SQL High Availability Options – FCI vs Always On Availability Groups
• Efficiently moving the ConfigMgr DB to an Availability Group
• ConfigMgr HA

SSRS ReportServer log file uncontrolled growth

When installing SSRS, there are two databases that get installed (default); ReportServer and ReporServerTempDB.

With SSRS, whether for SCCM or reporting in general, by default the ReportServer database Recovery model is enabled as FULL. Which means that all activity in the ReportServer database will get logged to the transaction file.

The side affect of this, the Transaction log file can get quite large with time. Recently, I assisted a client with a low disk space issue on a logical drive. After research, we determined that the ReportServer transaction log file had grown quite large… >60GB in size.

The fix? Change the ReportServer database  Recovery model to Simple. To reclaim the space on disk, shrink the ReportServer transaction log file.

To back up the ReportServer database, and perhaps custom reports; DO enable regular database backups, here is one approach.

To change the recovery model, from SSMS, right click the ReportServer database > Properties. Click on Options, then change the Recovery Model to Simple. Then, OK.

 

To shrink the Transaction log file, right click the ReportServer database > Tasks > Shrink > Files

From the Shrink file dialog, choose file type of LOG. Shrink action of Release Unused space, then OK.

Note: Thanks Ken.

Microsoft MVP Anniversary – 23 years!

In cleaning out some old paperwork, I re-discovered my original MVP award letter, dated 12-13-1995. Yes, it was a real letter, mailed to me!

The following year, this letter from Bill Gates was included. Some very insightful comments here… one I like:

“The interactive network will allow us to work together, to instantly communicate with each other, and stay connected, no matter where we are.”

Quite a profound statement given it was 1996, and I received a letter from Microsoft the year before!

It has been an amazing 23 years, have met a LOT of really nice people during this time. Being part of the community has had a very positive impact on my career.

I’ll also offer a shout-out to the MVP leads and the product teams that really value our opinions.