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