Pam Lahoud has some advice for those with tempdb-heavy workloads:
TL;DR – Update to the latest CU, create multiple tempdb files, if you’re on SQL 2014 or earlier enable TF 1117 and 1118, if you’re on SQL 2016 enable TF 3427.
And now it’s time for everyone’s favorite SQL Server topic – tempdb! In this article, I’d like to cover some recent changes that you may not be aware of that can help alleviate some common performance issues for systems that have a very heavy tempdb workload. We’re going to cover three different scenarios here:
-
-
-
Auditing overhead (even if you don’t use auditing)
There’s some good information in here so don’t just say tl;dr.