SQL Server 2016 : Getting tempdb a little more right

If I look back on my career, and think about the most common SQL Server bottlenecks - be it customer environments, community members I've helped online or in person, or my own in-house systems - tempdb would have to rank in the top 3, and possibly an outright winner.

Part of this problem is the ridiculous defaults in all major versions of SQL Server to date. Here are the properties of the tempdb files for a 2014 SP1 system I created recently:

tempdb file properties dialog with default SQL Server 2014 settings

I have a problem with just about everything on this screen.

• Logical Names - tempdev? Really? This doesn't affect performance, but this smacks of "worked on my machine" syndrome; I'm not clear why the product was ever released this way. I doubt this will ever change, of course, because of backward compatibility fears, but the logical names should follow well-established conventions, like tempdb or tempdb_data, IMHO.
• Initial Size - Given all of the things that tempdb has to handle in modern versions of SQL Server, 8 MB and 1 MB are kind of ridiculous defaults - the files are practically guaranteed to grow as soon as you connect to SQL Server and do anything.
• Autogrowth - Even more so, autogrowth of 10% for both data and log files is not a good default at all - this means that each successive autogrowth event will be 10% larger than the last. Which means that, as time goes on, those growth events take longer and longer. Since all activity has to wait for growth events to complete, and since tempdb will often have multiple data files that need to grow (and they are often on the same drive), and since log files are not eligible for instant file initialization (and many systems aren't taking advantage of it for data files either), this is a performance nightmare just waiting to happen.
• Path - While they've made this better in recent versions by adding specifications for the tempdb data and log files during setup, they're hidden in a tab that many people skip, Database Engine Configuration -> Data Directories (click to enlarge):

  (I don't like that they call it Temp DB, either, but that's a pretty minor nitpick.)

Some additional things that happen with tempdb in current versions:

• SGAM and PFS contention - This is mostly resolved with trace flag 1118, which forces full extents to be allocated to new objects, instead of mixed extents (where multiple objects would contend for the same pages). Some of this contention was fixed with a behavior change in allocation methods in SQL Server 2008, but this CSS blog post continues to advocate using the trace flag to overcome some areas the fix didn't actually fix, and Paul Randal has said, "all SQL Server instances across the world should have this trace flag enabled by default" (source).
• Uneven autogrowth - If you have multiple tempdb data files, they will be used in a round robin fashion, unless they are not the same size. If one file has to grow and the others do not grow with it, SQL Server will tend to use the larger file for most operations, largely squashing the goal of spreading I/O across multiple files in the first place. To alleviate this, you should size all tempdb files the same, set the same autogrowth for each (as a fixed size, not a percentage), and implement trace flag 1117 (which forces all of a database's data files to grow when any individual file needs to grow). The catch: it applies to user databases, too - if any file in a filegroup grows, all files in that filegroup will grow, which is not always desired behavior.

SQL Sentry

With SQL Sentry, our installed software solution, you can effectively monitor, diagnose, and optimize your entire database environment. 

What SQL Server 2016 Fixes

Three items above are addressed, at least to some degree, in SQL Server 2016:

• Trace flag 1117's behavior is enabled - You will no longer need to specify the trace flag in order to get consistent, simultaneous growth for all files in tempdb. It will still be up to you to make sure you have identical size and autogrow settings for all of the data files. During a Q & A session at SQLintersection, the initial plan was for this to be implemented server-wide, but in the end it only affects tempdb (you will need to use the flag still if you want this behavior in user databases).
• Trace flag 1118's behavior is enabled - While contention can't ever be avoided completely (well, unless we had a tempdb not just per user database, which has been requested, but per session), you will now see the benefits of this trace flag without having to turn it on. In addition, improvements have been made to reduce page latch contention when a large number of temporary objects are being dropped (the only private hotfix I've ever received was for a problem involving the "temp tables for destruction" counter increasing exponentially).
• Setup now offers the ability to set the number of tempdb files - There is a new dropdown on the Data Directories tab that allows you to specify the number of tempdb data files to configure (for all editions other than Express). If you are installing from the command line, a new argument, /SQLTEMPDBFILECOUNT, is documented in Install SQL Server 2016 from the Command Prompt. Here is how the new dialog looks by default, on a machine with 4 logical cores (click to enlarge):

  New Number of Temp DB files option in setup

  You can, of course, reduce this back down to 1 if you wish. It will also allow you to enter a higher number, but this will not pass validation - you should not create more tempdb files than the number of logical cores accessible to the SQL Server instance.

  Unfortunately, while it's nice to encourage a more appropriate number of tempdb files, it still creates them all in the same folder, they're all still 8 MB, and they all still have 10% autogrowth:

  tempdb file properties dialog with default SQL Server 2016 settings

Ideally...

This is most certainly a step in the right direction, but I'd like to have a lot more flexibility during setup, so I don't have to go reconfigure tempdb after every installation. And perhaps tempdb deserves its own screen, rather than as part of a tab that is only seen when manually selected, so that there is less chance of it becoming an afterthought. Here's how I wish the screen looked:

How I'd like to see the initial tempdb configuration screen

You pick the number of data files, and the size and autogrow settings that apply to all - these should *not* be pre-populated, since 8 MB / 1 MB / 10% defaults seem to suggest that these are good defaults, and % should not be offered in the UI at all - it can still be supported in the syntax, but you should have to go out of your way to shoot yourself in the foot like that. Then the dialog populates that number of rows so you can dictate explicit paths (it should be *possible* for you to configure this poorly by allowing different sizes for different files, maybe to allow you to demo problems or to provide smaller files on faster, more expensive disks, but this should not be encouraged). This would eliminate the bad defaults, and allow you to override each file location and spread the files across multiple drives (if you have them). You can also change the location and specs for the log file, but would be prohibited from creating more than one - again, not changing the underlying syntax, just not encouraging it in any way from the UI.

As an added bonus, you could have an option to say, "I want tempdb to be X GB, spread across Y files," and the UI would distribute that number evenly across the files. But now (!) we're getting into pipe dream territory - that would involve maths. It would also be nice if the installer could detect whether instant file initialization is enabled, and warn you if not - creating 8 MB files is super fast no matter what, but if you pre-configure 50 GB data files, this step may take quite some time if the files have to be zeroed out. Note that IFI may have been explicitly configured to work for the SQL Server service account, but not for the user account who is installing SQL Server. (I honestly don't know the internals, and am not sure which user would actually be performing this task during setup.) It could also be that the service account is initially set up as a local account without those rights, to be configured later by a domain admin after SQL Server is installed and running.

In a perfect world, of course, you would be dedicating isolated drives to tempdb, and sizing them such that they fill the drive, just below your "free disk space alert" thresholds. After all, what is the point of making the files small, knowing that they will grow? It's not like you can lease out that space to someone else in the meantime, and evict them when tempdb grows. Just pre-allocate the files as large as possible, with enough room to give you time to react to unforeseen scenarios where even the large files grow. And yes, even if you only have one drive to allocate to tempdb, it can still be beneficial to use multiple data files to reduce contention - don't think that all of the overhead is from RAID, the controller, and a single, shared disk (and I would argue that a single SSD with 4 or 8 tempdb files will outperform 4 or 8 dedicated, spinny disks with a single file each).

And in a perfect world, also, all of these options would be configurable via command line, invokable via PowerShell, and enforceable via Desired State Configuration. (Shout out to Kendal (@SQLDBA)!)

I've filed a Connect item with essentially the above section verbatim; feel free to vote:

• Better tempdb guidance during setup

UPDATE SEPTEMBER 30, 2015

In SQL Server 2016, the dialog for configuring tempdb during installation has been vastly improved:

I'd be curious to hear your stories about tempdb. What kinds of things do you find lacking? What information about tempdb would you like to get out of SQL Server that is currently difficult or impossible?

Other tempdb Resources

Here are a few other articles and blog posts that should provide more background on tempdb configuration, and may help illustrate how important tempdb can become if it is not configured correctly - regardless of your version of SQL Server.

• KB #2154845 : Recommendations to reduce allocation contention in SQL Server tempdb database
  This article talks about latch contention in tempdb, trace flag 1118, and some general advice about starting with 8 tempdb data files or the number of logical cores, whichever is less, and not going beyond 8 unless you are still seeing contention (you can see this advice repeated here by Jonathan Kehayias, and this is commonly known to be advised by Microsoft's Bob Ward as well).
• KB #328551 : Concurrency enhancements for the tempdb database
  Some good information here on tempdb configuration, but the article illustrates an example where they suggest using 10 data files for tempdb - which wouldn't really make sense except in cases where you are limited to exactly 20 cores (e.g. grandfathered CAL licensing) - but even then I would still probably go with 8. It also suggests, contrary to prevailing wisdom today: "As a starting point, you can configure the tempdb to be at least equal to the number of processors assigned for SQL Server."
• Working with tempdb in SQL Server 2005
  A very thorough post from Microsoft on the inner workings of tempdb and how to optimize performance - it's written for 2005, obviously, but many of the concepts are still relevant in SQL Server 2016.
• Storage Engine Blog : Managing TempDB in SQL Server: TempDB Configuration
  Sunil Agarwal describes some high-level concepts around tempdb configuration.
• Storage Engine Blog : TempDB Monitoring and Troubleshooting: Allocation Bottleneck
  Sunil Agarwal explains allocation contention in tempdb, how to see it, and what to do (also some background here).
• CSS Blog : SQL Server TempDB – Number of Files – The Raw Truth
  Bob Dorr talks about trace flag 1118 and how it helps reduce contention.
• Sankar Reddy : SQL Server Tempdb Usage and Bottlenecks tracked with Extended Events
  In this tip, Sankar shows how to use Extended Events to monitor tempdb performance.
• Adam Machanic : Analyzing Tempdb Contention
  Adam demonstrates using his free procedure, sp_WhoIsActive, to monitor contention in tempdb.
• Matthias Sessler : SQL Server 2008 Trace Flag -T 1117
  This post gives an overview of how trace flag 1117 allows tempdb I/O to be distributed relatively evenly across the data files.
• Me : T-SQL Tuesday #56 : SQL Server Assumptions
  See item #4, "SQL Server's Defaults are Great" - I mention several bad defaults that haven't made sense in years but are still used by Microsoft in modern versions of SQL Server.
• Kendra Little : Trace Flags 1117, 1118, and Tempdb Configuration
  Kendra gives some sound advice on these trace flags, and asks for your vote to get them both documented in Microsoft's official trace flag list (1118 has been documented since her Connect item went up, but 1117 is still curiously absent).
• Paul Randal : A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core
  In this post Paul dismisses the myth that you should always have a tempdb data file per core, regardless of the number of cores.
• Paul Randal : Misconceptions around TF 1118
  Here Paul explains the ins and outs of trace flag 1118, proves that it can still be effective in modern versions of SQL Server, and once again advocates global usage: "Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on." Of course the flag is now a no-op in SQL Server 2016.
• CSS Blog : SQL Server (2005 and 2008) Trace Flag 1118 (-T1118) Usage
  Just another official nod of approval for using this trace flag.
• Paul Randal : The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention
  Paul explains a lot about tempdb contention, how to resolve it, and states multiple reasons you don't want to just start at 32, 64, or 128 tempdb data files, even if you have 32, 64, or 128 logical cores.
• Paul Randal : Tempdb configuration survey results and advice
  Here Paul surveyed his readers on number of tempdb files, and reminds us how to check for contention.