A New tempdb Trace Flag Appears!

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!

Previous Post
Building SQL ConstantCare®: What Cloud Hosting Costs
Next Post
A Quirk In Parallel Hash Join Plans

19 Comments. Leave new

  • Geoff Patterson
    July 17, 2018 11:00 am

    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.

  • Thanks Brent for sharing these important updates!! It helps immensely.

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

  • Leonard Barnhill
    July 18, 2018 7:50 am

    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

  • 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

  • Pam Lahoud (@SQLGoddess)
    January 4, 2019 3:02 pm

    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 😛

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