Trace Flags 1117, 1118, and Tempdb Configuration

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.

Previous Post
Announcing the SQL Server Setup Checklist: Free eBook Download
Next Post
How to Pick a Monitoring Tool

37 Comments. Leave new

  • 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.

    Reply
    • Kendra Little
      June 29, 2014 4:26 pm

      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

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

        Reply
        • Kendra Little
          June 30, 2014 10:27 am

          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.

          Reply
      • Of course, even documented trace flags are considered mildly suspect. Trace flags which experts frequently recommend as “standard” really should just be SQL settings rather than trace flags. Happily, not only did Microsoft document the connect item, but also on SQL 2016+ 1117 and 1118 are disabled in favor of new ALTER DATABASE settings.

        Reply
  • 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.

    Reply
    • Kendra Little
      August 18, 2014 9:34 am

      I believe an update to BOL for at least some of the flags is going through the process. Fingers crossed!

      Reply
  • 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

    Reply
  • 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

    Reply
  • 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 ?

    Reply
  • Omran Abdurrahman
    May 21, 2016 12:25 pm

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

    Reply
  • Henrik Staun Poulsen
    August 11, 2016 3:14 am

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

    Reply
  • Hello Kendra, thanks for this article. I was just going to put the tempdb data files on a separate drive and to avoid any autogrow I wanted to make them use the space available in advance. You said: “I prefer to pre-grow tempdb files” – that is exactly what I wanted. But unfortunately Setup of Sql Server 2016 does not allow a datafile configuration larger than 1024 MB. Why? What to do about this? How to pre-grow it, if it’s not allowed?

    Reply
  • Ted Henderson
    April 14, 2017 11:16 am

    Hi Brent, I know this is an old post, but I have a quick question that I wanted to ask to clarify my understanding of Trace flag 1117. If every file in a file group grows when one file grows, is that limited only to the one database or does that makes the files grows in every database on the instance if a file grows and has trace flag -T1117 enabled.

    Reply
    • Erik Darling
      April 14, 2017 2:15 pm

      That’s every database, not just tempdb, until SQL Server 2016 where the TF has no effect. Or affect, just to be sure.

      Reply
  • Ted Henderson
    April 14, 2017 3:10 pm

    Thank you for the quick response. I’m not sure I asked the question specifically enough though. I just want be make sure I understand. If any data file in Tempdb needs to grow, or any data file in any database has to grow, then the data files in all of the other user databases will grow at the same time (ie.. if Tempdb grows, the data files in all my other 10 databases on the instance will grow their databases as the same time – OR – Does it mean that whether the database is Tempdb or any user database needs to grow a file then all that data files just with-in that one database will grow when a data file with-in that one database needs to grow?

    Reply
  • Just a quick question. If a database has already allocated space to objects on shared extents then do you need to do anything to change this once the T1118 is enabled? Is there a change process?

    Thanks
    Rich

    Reply
    • Rich – generally, you want to restart after applying trace flags like 1117/1118, or reconfiguring your number/location of TempDB files. It’s not urgent, but you just wanna do it at your next convenient time to even out load between the files.

      Reply
  • […] this instance is required to have trace flag 1117 enabled so that all files in a file group grow equally, you can set the trace flag you expect to be running using […]

    Reply
  • Reply
  • […] Little escreveu um artigo muito interessante sobre a utilização destes Traces […]

    Reply
  • Dirk and The Mac
    February 26, 2019 2:16 am

    Interesting to note that both of these flags are automatically implemented against tempdb in SQL 2016

    Reply

Leave a Reply

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

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}