SQL Server 2016 – IFI activation and TempDB optimization
This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!
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…
Now during installation, you can simply tick and
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.
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
/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.
This post might contain affiliate links. We may earn a commission if you click and make a purchase. Your support is appreciated!
Björn continues to work from Mexico as a Senior Consultant – Microsoft Data Platform and Cloud for Kramer&Crew in Cologne. He also remains loyal to the community from his new home, he is involved in Data Saturdays or in various forums. Besides the topics around SQL Server, Powershell and Azure SQL, he is interested in science fiction, baking 😉 and cycling.
Amazon.com Empfehlungen
Damit ich auch meine Kosten für den Blog ein wenig senken kann, verwende ich auf diese Seite das Amazon.com Affiliate Programm, so bekomme ich - falls ihr ein Produkt über meinen Link kauft, eine kleine Provision (ohne zusätzliche Kosten für euch!).
Auto Amazon Links: No products found.