Select Page

TempDB Database Enhancements with SQL Server 2016

Author: Eric Russo | | July 28, 2016

TempDB is a critical system databases that has been available with SQL Server for ages. It is critical for performance because it is a shared database for the whole instance. Not only is it capable of alloweing users create temporary tables, but it is also used internally by SQL Server to perform hashing, sorting, and reindexing. It can also keep the version store to support various isolation levels. But it is also a database that needs considerable tuning from an administrator’s point of view when working in production environments. There is no one-size-fits-all with TempDB.

The Microsoft product support team has continued to add enhancements to the TempDB database. If you have worked with SQL server performance optimization, you might recall trace flags # 1117 and 1118. These are documented by Microsoft under knowledge base article 328551, Concurrency Enhancements for the TempDB Database. I highly suggest you look at the KB article mentioned above to further inform your understanding. Or take the easier road and simply move to SQL Server 2016.

Here are some quick tests on SQL Server 2016 along with its predecessor, SQL Server 2014 that demonstrate the benefits of new enhancements.

SQL Server 2016 Does Not Require Trace Flag 1117

To test it, we have created four TempDB data files in both SQL Server versions, each 8 MB. This is how it looks on both SQL instances.

< Now, we have run the following script to create a large table in TempDB. Our goal is to grow the TempDB database files.

CREATETABLE #tempdbtable(i int,j char(8000))

GO

INSERTINTO #tempdbtable VALUES (1,’SOME DATA’)

GO 10000

The above script would create a wide temporary table in the TempDB database and insert about 10K rows. Since the initial size of TempDB is not sufficient, it will cause the file to grow. If we look at the TempDB file after the script execution, we can see the size difference.

SQL Server 2016 Does Not Require Trace Flag 1118

Microsoft SQL Server 2016 doesn’t need these trace flags because their behavior is already enabled by default. Along with the trace flag, the SQL Server 2016 setup also has a TempDB configuration screen during installation. This is how it looks:

The settings are under a tab called “TempDB” on the “Database Engine Configuration” page. This screen automatically picks up the number of CPUs on the machine and provides the number of files needed. The size, growth, and number of files can be adjusted during the installation itself. This is going to remove one of an additional “post-install-scripts” steps which big companies generally follow.

In short, SQL Server 2016 delivers improvements to make TempDB more scalable out of the box. There is no need to add more files, size them, or add trace flags.

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.

CONTACT US

Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.

EXPLORE JOBS