SQL Server 2016 – IFI activation and TempDB optimization

Lazy DBA - unsplash.com - Photo by JESHOOTS.COM

Now that the SQL Server 2016 officially released, more and more details coming to light, so that you have to get slowly prepared for your first “real” installation. What has changed compared to SQL Server 2014 in part of installing everything? How should we adapt any existing documentation? When reading numerous tweets and blogs I stumbled across a Tweet to SQL Server 2016 by Thomas Larock, he has created a very nice overview of varieties of new features in SQL Server 2016, which can be used e.g. for increasing the performance of SQL Server. Previously you had a variety of parameters and settings to optimize performance before installation (for example, Instant File Initialization) or adjust the number of TempDB files after installation. I would like to respond to the changes in the context of the SQL Server 2016 installation, which I am personally very pleased to facilitate a part of my work.

Instant File Initialization

Previously you had always to configure some parameters BEFORE installation of SQL Server the local security policy with the system tool “secpol.msc” in order to grant permissions to SQL service user on “Perform Volume Maintenance Tasks / Perform volume maintenance tasks”. First, open the tool, then click on a tree and still find the right authority, and then locate the user in the user management … a really complicated way to the full write new files or file spaces to get around to so as the Autogrowth Event to accelerate …

Now with the new SQL Server 2016, it is much easier, because the installation process does the work for us now. Here it was Microsoft, which integrated such configuration optimally which was published through community over years as best practices, and applied in the installation process in order to create an easy opportunity… tada…

SQLServer2016 Instant File Initialization

Now during installation, you can simply tick and installation process takes care of the work for you. Many thanks for this to Microsoft!
If you install SQL Server 2016 from the command line, you also have the option specifying this parameter either in the ConfigurationFile.ini when calling the “Setup.exe” or directly as an option on the call

/SQLSVCINSTANTFILEINIT=”True”

Automatic TEMPDB Configuration

The configuration of the TempDB after the installation always took a little time of course, you could script it so far, but it was never as easy as it was before, it still meant a certain effort. Microsoft has also moved this configuration into the installation process so that you do not have to call another script after the installation.

SQLServer2016 Config TempDB

In this new tab under Database Engine Configuration, you can set the number of TempDB files, their initial size and auto-growth value. You can customize the paths of both file types individually, theoretically, you can also specify several paths for the TempDB data files. All these new configuration parameters could be passed to sql server installation via the ConfigurationFile.ini or command line.

/SQLTEMPDBFILECOUNT=”8″ /SQLTEMPDBFILESIZE=”16″ /SQLTEMPDBFILEGROWTH=”256″ /SQLTEMPDBDIR=”C:\tempdb” “D:\tempdb” /SQLTEMPDBLOGFILESIZE=”256″ /SQLTEMPDBLOGFILEGROWTH=”0″ /SQLTEMPDBLOGDIR=”E:\tempdblog”

The above optimization (or better simplifications) makes the installation and performance-oriented configuration of SQL Server much more comfortable. However, it also has a small drawback (*wave*), the DBAs lose some of their influence since now everyone has the opportunity to operate this performance screws.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.