Blog

Unevenly sized tempdb files can cause uneven use - and contention on some critical pages

Unevenly sized tempdb files can cause uneven use – and contention on some critical pages

SQL Server has a few options for configuring tempdb. It’s great that Microsoft gives some advice for how to battle some kinds of contention that can impact tempdb in KB 2154845, but things can still be a little confusing.

Trace Flag 1118 – Full Extents Only

KB 2154845 advises that Trace Flag 1118 can help in some situations. That trace flag tells SQL Server that it should avoid “mixed extents” and use “full extents”. (Learn more about extents here.)

This means that each newly allocated object in every database on the instance gets its own private 64KB of data. Tempdb is usually the place where most objects are created, so it makes the most difference there.

Since this trace flag is advocated in KB 2154845, it’s clearly documented as safe to use. But for one reason or another it hasn’t made its way into the list of Trace Flags in SQL Server Books Online.

Trace Flag 1117 – Grow All Files in a FileGroup Equally

Trace flag 1117 changes the behavior of file growth: if one data file in a filegroup grows, it forces other files in that filegroup to ALSO grow. This can be useful for tempdb, which is commonly configured with multiple data files as KB 2154845 advises.

Not everyone likes to implement this trace flag, particularly because it impacts every database on the instance and not just tempdb. Personally, I prefer to pre-grow tempdb files so they fill the tempdb drive, just leaving room for any “free space monitoring” you have. Whenever I can avoid using a trace flag, I do it: just less room to hit an edge condition.

But Trace Flag 1117 is something that some folks like, and it’s recommended in the Fast Track Architecture Guide from Microsoft.

Trace Flag 1117 is also not in the list of Trace Flags in SQL Server Books Online.

Why Care About Trace Flag Documentation?

Imagine this: a critical database server starts throwing frequent stack dumps which are very hard to interpret. Every time it happens, the instance freezes up. You need to fix it fast. You start up a ticket with Microsoft Support. You want that ticket to move as quickly as it possibly can. And if your manager starts asking about your configuration, it’s really nice to have a single link showing you haven’t turned on anything too weird, right?

Your life is much easier if every trace flag you have enabled is clearly documented as safe to run. A blog post like this, or even a blog post on Technet, doesn’t prove that for you. (Technet’s great, but every blogger is fallible, and there’s some old content out there.)

Trace Flag 1118 is documented in the Tempdb KB, so it’s not all that controversial. But Trace Flag 1117 is more of a judgement call at this point. (It’s in the Fast Track Guide, sure, but are you running Fast Track?)

Clarify the Situation: Vote for My Connect Item!

Both of these trace flags have been around for a while. I suspect that they’re fine to use and that Books Online is just a little out of date. But wouldn’t it be nice to be sure?

Vote for my Connect item to update the Trace Flags page in Books Online and add TF 1117 and TF 1118 to the list of approved trace flags.

Kendra Little
My goal is for you to understand your SQL Server’s behavior– and learn how to change it. When I’m not figuring out the solutions to your database problems, you’ll find me at user group meetings in Portland, Oregon. I also love to draw.
Kendra Little on sabtwitterKendra Little on sablinkedin
↑ Back to top
  1. I voted for your item. Quick question, we had an MS engineer come out for a ‘RAP’ (the tech assessment kind, not the Kool Moe Dee kind). These trace flags came up as a ‘bad thing’, however having seen even the man himself Paul Randal promote their use I said we had intentionally done that for reasons. Now another thing came up, as you mentioned we are using the whole drive for tempdb (because that’s what the drive is for) but MS person indicated having the large log files (50GB, woah) was bad news. Is that bad craziness? If so is there a rule of thumb as it were for sizing (keeping in mind the answer to everything, ‘it depends’?) Just wondering what your thoughts were on this.

    Thanks as always for great and informative content.

    • Hi there,

      I can’t really speak for Microsoft, but I suspect that they basically mean that running with any trace flags can mean that if you run into any issues like corruption, escalating and diagnosing your issue to support can take longer. Essentially you’re on a non-default configuration, right? So the benefit of getting the trace flags as clearly documented as possible as I’m asking here is to try to make it more clear what non-default trace flags are considered “safe” by Microsoft.

      So my thought is that it’s really up to you to decide what the proper configuration is for your server, given the situation. None of those of us who write blogs can promise that there’ll never be a bug in the product specific to some trace flag and we can’t swear you won’t be burned by some configuration choice. You’ve got to weigh all the info and make your own decision.

      For the tempdb log file, it’s a little out of the scope of this comment for me to go into all the details, but the main issue is that you can get a big rush of IO when the tempdb log file is 70% full and starts a checkpoint. (This is specific to tempdb.) I’ve seen that IO rush overwhelm the storage in some cases and slow down the whole SQL Server for that time period. So if you don’t need the file to be that large, don’t just leave it. (Figuring out if you need it, etc is what’s out of scope, I can do a whole post on that in the future, though.)

      Hope this helps!
      kl

      • That does help a lot, and thanks for accomodating my wandering outside of scope somewhat 🙂

        • Oh, no worries at all! I love this idea for a blog post, too– I’ll go into more detail on how you can tell if you still need the space, etc.

  2. Closed and marked as fixed but the article has not been updated. I wonder if fixed means they plan to fix it in a future version or just update the document.

  3. Pingback: SQL Saturday #320 Raleigh in Review | SQL Hammer

  4. Pingback: SQL Saturday #320 Raleigh in Review - SQL Server - SQL Server - Toad World

  5. Pingback: What are trace flags? | SQL Studies

  6. Hello,
    Interesting article!! Thank you for writing it 🙂 Just wanted to ask if T1118 is still required on SQL 2012-2014? I’m seeing article that are for and against using it. Wanted to you know you opinion.
    Thanks

  7. Kendra – one comment.

    I noticed that since SQL 2014 release – trace flag 1118 is now in the Microsoft list of trace flags at http://technet.microsoft.com/en-us/library/ms188396.aspx

  8. According to this para:
    “`
    Not everyone likes to implement this trace flag, particularly because it impacts every database on the instance and not just tempdb. Personally, I prefer to pre-grow tempdb files so they fill the tempdb drive, just leaving room for any “free space monitoring” you have. Whenever I can avoid using a trace flag, I do it: just less room to hit an edge condition.
    “`
    what is `free space monitoring` here ?

  9. Excellent write , Thanks for taking the time to write it. I am a fan of Trace flag 1118

  10. I just found out that Trace flag 1117 and 1118 are deprecated in SQL Server 2016.

Leave a Reply

Your email address will not be published. Required fields are marked *

css.php