(I was reluctant to make the change because I didn't have a high volume non-production environment to do real world testing.

Plus, I knew the #TempTable sometimes held as many as a thousand rows...even though the guidance for choosing between a #TempTable and a @TableVariable has changed a bit over the last couple of major releases.)

/fin

Somewhat desperate, I decided to replace the #TempTable with a @TableVariable.

Boom! Problem solved. 😃

Yet here we were with the same problem once again. This time around, Pam Lahoud's #TempTable guidance was already in place within the stored proc code.

The server has 128 vCPU and 8 tempdb data files. Maybe I needed to add some more data files?

Well, I added two more files, but there was no appreciable improvement in performance. Bummer!

Still, we had this one query near the top of the shit list: a CREATE #TempTable operation.

We'd had this once in the past, and I eventually realized it was a very high volume stored procedure causing tempdb metadata contention. Pam Lahoud explains the issue very well in this post: https://techcommunity.microsoft.com/blog/sqlserver/tempdb---files-and-trace-flags-and-updates-oh-my/385937