Coming Around
Microsoft has been doing a good job of taking stuff that used to be Wizard Hat and making it part of out of the box functionality.
Starting with SQL Server 2016, you didn’t need to keep these in your setup checklist:
- Trace flag 1118 to reduce SGAM contention
- Trace flag 1117 so when a single file in a database filegroup needs to grow, they all grow together
- Trace flag 2371 so statistics are recomputed less rarely as tables grow larger
- Trace flag 8048 enables SOFT NUMA when SQL’s NUMA-awareness code doesn’t work on larger systems
These became defaults. This was especially helpful to me as a consultant because I could focus in on other problems.
Creature Feature
That changed the other day, when Pam Lahoud from Microsoft published this post: TEMPDB – Files and Trace Flags and Updates, Oh My!
I’ll let you head over there to read the juicy details, but gist of it is this:
There are some cases where having multiple files alone does not completely address PFS contention. For these cases, we have implemented a fix where we not only round robin between the files, we also round robin between the PFS pages within the files, allowing us to spread the object allocations across all the files and within the files themselves.
The fix that Pam talks about is Trace Flag 3427. The KB for it is over here.
Bottom Line
If you’re on SQL Server 2016 or 2017, and you’re noticing tempdb contention, you might need to enable this Trace Flag.
I’m assuming a little bit here — namely that you’ve already got multiple tempdb files — since that’s part of the setup process for SQL Server now.
If you don’t, start by adding them. There’s a good set of instructions at the link.
Thanks for reading!
19 Comments. Leave new
Thank you for sharing this , Erik!
One minor point: You write “If you’re on SQL Server 2016 or 2017, and you’re noticing tempdb contention, you might need to enable this Trace Flag.” Based on the trace flag documentation you linked to, I think the behavior that the trace flag controls will be automatically enabled in SQL Server 2017. The way I read it, the trace flag is therefore only applicable in SQL Server 2016.
Hi Geoff,
Yeah, I’m not sure about that one. One of the KB articles listed is FIX: Heavy tempdb contention occurs in SQL Server 2016 or 2017.
I’ve left a comment on the post asking for clarification.
Thanks!
Thanks Brent for sharing these important updates!! It helps immensely.
Thanks Erik 🙂
Great summary of the trace flag, Erik. I appreciate you writing this post. We moved to 2017 a few months ago so this is a good one to keep in mind.
My reading and interpretation of the KB is that for SQL Server 2017, one should disable CCC since Trace Flag 3427 is not available in this release.
hi
I have SQL SERVER 2016 SP2 CU3 applied now ccc currently disabled do you think we should enable traceflag 3427 to control the high cpu as currently we experiencing will this help
yas — For questions, use https://dba.stackexchange.com/
Hey Erik,
Wouldn’t you disable TF 3427 for 2016 SP2 CU3?
“To enable the fix Trace Flag (TF) 3427 must be enabled *only* from Cumulative Update 2 for SQL Server 2016 SP1 *to Cumulative Update 2 for SQL Server 2016 SP2*.” (emphasis added)
– https://support.microsoft.com/en-us/help/3216543/workloads-that-utilize-many-frequent-short-transactions-in-sql-server#section-1
Kind regards
That’s why he says “There’s a good set of instructions at the link.” We can’t cover every single piece of instructions here – if we did that, we’d just be copy/pasting Books Online. If you want that…just read Books Online. 😉
Thanks Brent. Yeah, for sure. I just want to make sure there isn’t anything misleading, as I’m looking at it currently. Apologies for cluttering your comment section.
Again, thanks for all your contributions to the community :-).
No worries at all!
Nevermind – my mistake. I thought you were saying “yas” to be a cool way of saying “yes,” but I now realize you were addressing the person by name. Sorry about that.
Thanks for all you do for the community :-).
Hey Erik!
Thanks for the write up, as usual!
Maybe I’m misunderstanding, but in the “Creature Feature” section you seem to suggest that the fix Pam is talking about can be enabled with TF 3427, but she mentions that TF 3427 is to bypass CCC overhead (from https://blogs.msdn.microsoft.com/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my/#auditing-overhead)
“In SQL Server 2016 you can enable trace flag 3427 to bypass this overhead starting with SP1 CU2.”
The fix she links to is https://support.microsoft.com/en-us/help/4099472/pfs-page-round-robin-algorithm-improvement-in-sql-server, which is part of 2016 SP2 CU1.
Not sure if I understood properly but figured I’d comment on it.
Thanks!
Brian
Brian — I’m not sure what you need clarified. About the only thing in that section is a quote from the blog post?
Hey Erik,
Thanks for the response. I was saying that section seems to suggest that the fix she is referring to in the quote is TF 3427 / KB 3216543, but she links to KB 4099472 (so they (seem to be) not the same fix). In other words, I was asking for clarification about whether TF 3427 changes the tempdb round robin allocation algorithm (to be PFS pages instead of just files) or the CCC overhead behavior (bypassing it even when CCC is disabled). This is particularly relevant for, say, 2016 CU2 SP2 (where KB 4099472 has already been fixed but KB 3216543 has not).
Thanks!
Brian
Hi Folks! I just stumbled across this post while I was searching for something else and noticed there may be some confusion about TF 3427. As Brent mentions in his intro, we are trying to make trace flag behavior the default where we can, and we are working hard to prevent introducing new trace flags where possible. This TF 3427 is one that kind of slipped through the cracks, so we remedied the situation by making this the default behavior when CCC is disabled (which it is by default). This means in SQL Server 2017 and starting with SQL Server 2016 SP2 CU3, you don’t need to worry about this trace flag, you just need to make sure CCC remains disabled. Sorry about the confusion, kinda wish I could go back and erase the internet 😛
Howdy ma’am! Good to see ya, hope you’re doing well.
Thank you Pam! Hope your new year is going well :-D.