TempDB-Performance-Tuning out of the box – T-SQL Tuesday #87

TempDB-Performance-Tuning out of the box - T-SQL Tuesday #87

T-SQL Tuesday is a recurring blog series which was launched by Adam Machanic (b | t). Every month, a blogger is hosting a topic around the SQL Server and anyone can write a blog post on this particular topic.

This month, Matt Gordon (b | t) our host and the topic is about new features in the SQL Server (starting with the 2014 version) which fixes old issues, see more details in his announcement post.

My topic for this TSQL2sDay is the new possibility to configure the TempDB as part of the SQL Server installation.

much easier Implementation of the Best Practice for TempDB

Previously, after you’ve finished a SQL Server installation, you had to implement best practice recommendations from Microsoftthe best practice recommendations from Microsoft in a more or less complicated way. Either you approach the topic in a more conventional way and adjust the values manually via the SQL Server Management Studio or you had made it a little bit easier to automate this by a T-SQL or Powershell script. But still, you had to add these configuration adjustments every time to achieve an optimized TempDB performance.

Essentially the TempDB performance comes basically with the hardware configuration, in this case, the physical separation between user databases and TempDB by transferring the TempDB onto a high-performance storage. Microsoft recommends the strict separation of UserDB and TempDB, not only through separate folder structures or separate partitions but should by separated (fast) disks. There is no need for a great RAID configuration, a RAID 1 or RAID 10 would be great, but no real need, because the TempDB is always “re-created” when restarting SQL Server, so it would not be a “drama” when it is corrupt or the discs below would crash. (Ok, here I don’t care about the possible loss of data and time).

Overview of Best Practice recommendations

So many adjustments are not necessary to get a better performance of TempDB, I would like to briefly summarize this:

  • Physical separation of user databases and TempDB
  • Splitting the TempDB into individual data files according to the number of processors
  • Autogrowth parameter adjustments
  • Trace flag depending on usage / requirement

Once this physical optimization has been implemented in the storage area, you can make the logical adjustments in the configuration. Depending on the processor/core count, Microsoft also recommends splitting data files into several separate files instead of one large data file in order to optimize the performance of the IO streams (parallelization). Likewise, depending on the application or requirements, the data files can initially be set to a fixed size so that the internal structure of the data files are optimized and all files are the same size. (Similar to trace flag 1117/1118). Otherwise, the Autogrowth parameter should be set to 10% so that the data files in conjunction with Trace flags 1117 and 1118 contribute to an optimized, uniform structure of the TempDB.

SQLServer2016 - Configure TempDB during Installation

SQL Server 2016 brings integrated simplifications

The SQL Server 2016 now brings this whole logical optimization right to the installation process… now you can tell the SQL Server how to set the number and distribution of TempDB data files and their auto growth parameters within the installation steps. Microsoft has finally integrated the long-published Best Practices recommendations into the installation process. So you only have to create the hardware prerequisites for the TempDB and can start to install.
Ok, here, too, you have to worry about which values you want to set, but at least you do not have to worry about these configurations afterward!

THANKS to Microsoft for this improvement.

During the installation process, you can set how many data files you want to create, the initial size and the Autogrowth parameter. Theoretically, you are even able to name a separate path for each individual data file (division by the number of cores) or even a separate (SSD) disc. Everything is displayed on a clear screen and can be used also from an inexperienced DBA.

Through this new “Toy” some old “problems” could be reduced … 😉

SQL Server 2016 – IFI activation and TempDB optimization

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


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.


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.