SQL Server 2016 shininess for ConfigMgr Current Branch

By Johan Arwidmark / March 3, 2017

A quick update on SQL Server 2016 changes that are helpful for ConfigMgr Current Branch installations.

The usual ranting

As you probably know, ConfigMgr performance greatly benefits from tuning the SQL Server setup a bit, and unless you have been hiding under a rock the past the decade, you also know that the SQL Server instance should be local to the ConfigMgr site server. The reason for having SQL Local? For simplicity, for being the most reliable and well tested configuration, for best availability, and by all means for the best performance. Here is a longer post on the the topic if you need to convince your manager or DBA: Why you should not use remote SQL Server with ConfigMgr 2012.

Also make sure to always run ConfigMgr in a virtual machine, never directly on a physical box. If you need better performance, because your current Hyper-V or VMware farm can't deliver the performance needed, by all means use a dedicated Hyper-V or VMware host for the ConfigMgr VM. But YOU DO WANT to have ConfigMgr in a VM, capisce 🙂

TempDB improvements in SQL Server 2016

The smartest single change you can do in SQL Server for ConfigMgr (and SQL Server version), is to assign multiple files to the TempDB database, and to make sure they are the same size too. ConfigMgr is simply using the TempDB database a lot, and having multiple TempDB files speeds up the performance greatly.

Now, in SQL Server 2016 you can do that directly during setup, and as a bonus, the various trace flags you typically configured on older versions of SQL, namely trace flags 1117 which controls simultaneous growth of the TempDB files, and trace flag 1118 which reduces page latch contention, are now enabled by default.

Note: Page latch contention, or TempDB latch contention, is just a fancy wording for the system being slow due to internal buffer handling in the TempDB database, but if you really want to dive in deep, I recommend reading this whitepaper: Diagnosing and Resolving Latch Contention on SQL Server.

The SQL Server Setup GUI, allowing a maximum TempDB file size to 1 GB (each).

SQL Server 2016 TempDB setup for ConfigMgr Current Branch

If running the normal setup of SQL Server 2016, you can configure the TempDB files directly in the GUI, specifying the number of files, location etc. which is very nice. But please note that the maximum size you can specify in the GUI is 1 GB for each file, and even for a medium ConfigMgr site setup, you probably want to increase it a bit more. The recommendation is to set the total file size it to 25 – 30 percent of your ConfigMgr Database. So if your ConfigMgr database is 40 GB, and you have 4 vCPUs (minimum) for the VM, the files should be 2.5 GB each.

You can also use the SQL Server unattended options to set the number of files, location, and size, but you're still limited to 1 GB per file.

SQLTEMPDBFILECOUNT=4 
SQLTEMPDBFILESIZE=1024 
SQLTEMPDBFILEGROWTH=256 
SQLTEMPDBDIR=G:\SQLTempDB 
SQLTEMPDBLOGFILESIZE=256 
SQLTEMPDBLOGFILEGROWTH=0 
SQLTEMPDBLOGDIR=G:\SQLTempDB

Setting the proper size

For most ConfigMgr sites, the maximum file size you can set via the SQL setup, again 1 GB per file, is going to be to small. So to fix this you simply do what you always have done, you run a script that configures it after setup. Here is an example:

Use master
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'G:\SQLTemoDB\tempdb.mdf',
SIZE = 4096MB,
MAXSIZE = 10240MB,
FILEGROWTH = 1024MB)
GO
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev_2, FILENAME = 'G:\SQLTemoDB\tempdb_2.mdf', SIZE = 4096MB, MAXSIZE=10240MB, FILEGROWTH = 1024MB)
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev_3, FILENAME = 'G:\SQLTemoDB\tempdb_3.mdf', SIZE = 4096MB, MAXSIZE=10240MB, FILEGROWTH = 1024MB)
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev_4, FILENAME = G:\SQLTemoDB\tempdb_4.mdf', SIZE = 4096MB, MAXSIZE=10240MB, FILEGROWTH = 1024MB)
GO

ConfigMgr Hydration

You can download the above script, and many other SQL configuration scripts from the ConfigMgr Hydration setup repository on GitHub: http://github.com/configmgrhydration/setup

Other SQL Configurations for ConfigMgr

In addition to the TempDB tweaks, you also want do some other tweaks.

Rebuild indexes, see post here on how to do that: Optimizing SCCM Databases Revisited
Configure a backup: Configuration Manager SQL Server Backup guidelines
Size the database, and configure disk layout: Sizing your ConfigMgr 2012 R2 Primary Site Server
Cap the database log files: System Center 2012 Configuration Manager SQL recommendations
Change the recovery mode for Reporting Services: How to shrink the SQL Server Reporting Services log database