You are here:   Research
  |  Login

Welcome to my blog, quickest way to find articles is usually to search for them.

Minimize
Search in All Title Contents
 
     

SQL Server 2016 shininess for ConfigMgr Current Branch

Mar 03 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.

 

image
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:\SQLTMP\tempdb.mdf',
SIZE = 4096MB,
MAXSIZE = 10240MB,
FILEGROWTH = 1024MB)
GO
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev_2, FILENAME = 'G:\SQLTMP\tempdb_2.mdf', SIZE = 4096MB, MAXSIZE=10240MB, FILEGROWTH = 1024MB) 
ALTER DATABASE tempdb 
ADD FILE (NAME = tempdev_3, FILENAME = 'G:\SQLTMP\tempdb_3.mdf', SIZE = 4096MB, MAXSIZE=10240MB, FILEGROWTH = 1024MB) 
ALTER DATABASE tempdb 
ADD FILE (NAME = tempdev_4, FILENAME = 'G:\SQLTMP\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: https://github.com/configmgrhydration/setup

Other SQL Configurations for ConfigMgr

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








Happy deployment, and thanks for reading!


What our lawyers makes us say:

This information is provided "AS IS" with no warranties, confers no rights and is not supported by the authors or Deployment Artist.

Copyright © 2017 by Deployment Artist (the company behind deployment research). All rights reserved. No part of the information on this web site may be reproduced or posted in any form or by any means without the prior written permission of the publisher.

Shorthand: Don't pass off our work as yours, it's not nice.

Blog Archive

Minimize