SQL Server 2016 shininess for ConfigMgr Current Branch

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.


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
MODIFY FILE (NAME = tempdev, FILENAME = 'G:\SQLTemoDB\tempdb.mdf',
SIZE = 4096MB,
MAXSIZE = 10240MB,
ADD FILE (NAME = tempdev_2, FILENAME = 'G:\SQLTemoDB\tempdb_2.mdf', SIZE = 4096MB, MAXSIZE=10240MB, FILEGROWTH = 1024MB)
ADD FILE (NAME = tempdev_3, FILENAME = 'G:\SQLTemoDB\tempdb_3.mdf', SIZE = 4096MB, MAXSIZE=10240MB, FILEGROWTH = 1024MB)
ADD FILE (NAME = tempdev_4, FILENAME = G:\SQLTemoDB\tempdb_4.mdf', SIZE = 4096MB, MAXSIZE=10240MB, FILEGROWTH = 1024MB)

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.

About the author

Johan Arwidmark

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments