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?)
Want to learn more? Take my class.
My Fundamentals of TempDB class is for curious folks who want to learn:
- What uses TempDB, and how that affects performance: not just temp tables and table variables, but also triggers, cursors, sorting indexes, workspace spills, the version store, and more
- How to host TempDB depending on how you use it: understanding what goes in the log file, the data files, why the number of data files matter, and whether your particular workload should be on SAN, local storage, or ephemeral storage, and why
- How to monitor it: after you understand what uses it and how to host it, you need to keep tabs on how its performance changes over time using DMVs, Perfmon counters, and the First Responder Kit scripts
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.
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.
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.
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.
I believe an update to BOL for at least some of the flags is going through the process. Fingers crossed!
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
Some research indicates that after sql server 2008 the trace flag is not as important, however still a possible solution to problems caused by DML operations.
http://blogs.msdn.com/b/psssql/archive/2008/12/17/sql-server-2005-and-2008-trace-flag-1118-t1118-usage.aspx
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
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 monitoringhere ?And what is :
just less room to hit an edge condition
can you explain it more specificly ?
No, this is as far as we want to go in this blog post. Thanks!
Sometimes people have warnings whenever they hit 10% free space left on a drive.
Excellent write , Thanks for taking the time to write it. I am a fan of Trace flag 1118
I just found out that Trace flag 1117 and 1118 are deprecated in SQL Server 2016.
Yes! I also have just discovered this when I found these two lines in my SQL log file:
2016-08-29 03:36:49.62 Server Trace flag 1117 is discontinued. Use the options provided with ALTER DATABASE.
2016-08-29 03:36:49.62 Server Trace flag 1118 is discontinued. Use the options provided with ALTER DATABASE.
After having recently attending a MS course on “What’s new in SQL 2016”, I’m quite disappointed this wasn’t mentioned.
hi Bob,
Here is what I do about the problem:
http://dba.stackexchange.com/questions/146628/alter-database-modify-filegroup-myfilegroup-autogrow-all-files/146918#146918
Best regards,
Henrik
That’s weird. I just had a Microsoft tech tell me that I needed to enable Trace Flag 1118
okay….maybe you are talking about 2016
TempDb in 2016 automatically assumes the behaviour of these flags. https://blogs.msdn.microsoft.com/psssql/2016/03/15/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases/
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?
Kendra is blogging over here these days.
To answer your question: you can set the autogrowth larger than that after setup. It’s only the installer that’s limited to those numbers.
If you need additional help, head on over to http://dba.stackexchange.com/.
Thanks!
Thank you Erik.
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.
That’s every database, not just tempdb, until SQL Server 2016 where the TF has no effect. Or affect, just to be sure.
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?
Hey, no, it’s per database. Tempdb growing won’t cause autogrowth in another database.
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
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.
Thanks for getting back to me.
So an actual database data file doesn’t need any kind of “rebuilding” to move objects onto their own extents from potentially shared extents? Just wondering if there would be any reorganising needed or if it would just happen over time as the data was used,
Rich – for questions, head on over to https://dba.stackexchange.com.
[…] 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 […]
[…] https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/ […]
[…] Little escreveu um artigo muito interessante sobre a utilização destes Traces […]
Interesting to note that both of these flags are automatically implemented against tempdb in SQL 2016
Just for completeness as a client cited this thread:
https://blogs.msdn.microsoft.com/psssql/2016/03/15/sql-2016-it-just-runs-faster-t1117-and-t1118-changes-for-tempdb-and-user-databases/