SQL Server 2016 : Getting tempdb a little more right

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 settingstempdb file properties dialog with default SQL Server 2014 settings

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

  • Logical Namestempdev? 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):

    tempdb_config_14(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.

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):

    tempdb_config_16New 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 settingstempdb 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:

tempdb-idealHow 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:

UPDATE SEPTEMBER 30, 2015

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

tempdb 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?

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.

Comments ( 7 )

    • Bob says:

      Another Curio: SQL FCI w/o shared storage? Fine. Install 2016 on such an environment? Surprise: You can't actively select *your* choice of UNC path for tempdb data (user db or temp log no problem) – because they used the outdated XP-era browse-for-folder dialog. So far normal fare. Now create a symlink on C:\ pointing to your desired destination, and pick that in setup. Think setup will use that path? No – it will resolve the symlink and use your UNC destination. You can even remove the symlink right away. A type of surprise I haven't seen in my Microsoft experience so far.

      Now consider generating an ini file and using that for an unattended setup – yes of course it will respect your choice of UNC location without the symlink hassle, but the end result will differ from what would have happened had you finished the setup you got that ini from – it will unnecessarily move a random shared storage volume from the free pool into your new cluster group if one exists (simply because deselecting all such volumes will create an ini without any reference to them and the next run will fall back to defaults)…

      Lastly, the thing with the broken ODBC driver killing SQL Agent, 2012 SSAS cubes on the same nodes no longer processing (add the port number to the source sql connection to fix) and the CEIP tool erroring out due to pre-existing SSAS not having (or really needing) a path…

      Pure Adventure! Sorry for 90% of the adventure being off-topic ;)

    • Bob says:

      Late, but I had a nice refreshing experience these days:

      Combine the improved 2016 tempdb setup page with the no-shared-storage-cluster hype, meaning system db's on SMB. Setup breaks this a little – you can't choose *your* share for the tempdb data (log no problem) because the browse-for-folder dialog is the ancient NT4 one (okay, I exaggerate – but it was the dreaded Vista that first offered a better one – a decade ago). Command line or ini file work – but what if you already have the UI running and don't want to waste time?

      Take a seat… Create a symlink somewhere on C:, select that in the crappy browse-for-folder dialog, and lo and behold, setup picks up not the symlink path, but the correct destination you wanted all along! You probably can even drop the symlink before finalizing your setup…

    • tobi says:

      In an even more ideal world alloc page contention would be fixed in the engine so that the file count default could and should be 1.

      Also ideally, autogrowth would be eager and non-blocking. The engine could always keep 5% freespace and extend files ahead of the workload. That would allow all autogrowth increment settings to be practically meaningless. The files would transparently resize just in time. Also, the default file sizes could then be 1MB.

      For the log file VLF fragmentation needs to be fixed in the engine which seems rather simple to do. Simply coalesce and VLFs when too many or too few of them exist. There's a Connect ticket for that proposal.

      With these 3 changes in the engine all of these settings are moot in 99% of installations. That would have been the proper fix in my mind.

    • Orson Weston says:

      Its a step in the right direction at least.

    • Glenn Berry says:

      Very nice post, Aaron. It looks like Microsoft is using logical cores (which equate to all the cores that SQL Server can see), when they make the calculation on eight vs. the number of cores, whichever is lower.

      Unless someone is installing on a laptop, a low-end desktop or small VM, probably most people are going to get eight tempdb data files by default.

    • John says:

      Don't suppose you are/have logged the suggested TempDb installer page as a suggestion on connect for us to vote on?

    Leave A Comment

    Your email address will not be published.