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?