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