Dave MasonI'm on a flight to Kentucky, so... how about another episode of SQL Server story time?
We've been having some recent performance issues. The SQL landscape where I work includes a server with about 200 databases comprising 10-20 TB of data (I forget the exact number).
At any given time, when you run spWho, there's close to 20k rows returned. (That equates to 20k simultaneous connections, although much of that can be attributed to connection pooling--it's not 20k unique logins.)
Because of that high volume of connections, there are a lot of different execution plans. So many in fact, that we hit the 160k procedure cache limit and had to enable a Trace Flag to expand that number to 640k.
Ok, now that you have an idea of the size & scope of the environment, on to the problem...
Our monitoring software (Solar Winds Database Performance Advisor) is great at showing the 'worst of the worst' queries. But sometimes poor performance is more attributable to 'death by a thousand cuts' than one problem child (or a handful).
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
I guess it's too late to be brief, but long story short, when we had this problem before, I modified the stored proc to declare the #TempTable in a single statement (including all constraints, indexes, and compression) along with *not* dropping the #TempTable anywhere within the stored proc code.
This should allow SQL Server to 'reuse' the objects instead of having to allocate/deallocate them tens of thousands of times per minute.
After deploying that change, ka-ching! Performance improved dramatically.
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!
Somewhat desperate, I decided to replace the #TempTable with a @TableVariable.
Boom! Problem solved. 😃
(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
John BurnsThat feels a whole lot like declaring dependencies outside of 'loop code'... then use them and clean up after the loop is done.
??
🤔
HistorySheWrote@DaveMasonDotMe That's quite a story. Congratulations on finding a solution (twice).
*Virtual CPU (central processing unit)