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:

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 – Well, the paths aren't shown in the above screenshot, and 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.

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.

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 by default – 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 by default – 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 and configure SQL Server on Windows from the command prompt.
   
  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.

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.

You would 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.

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?