Using temp tables in SQL Server often seem like a great way to improve performance. But when everyone wants the same piece of land, you end up with contention or in the real world, very expensive houses.
TempDB contention example
To simulate a lot of sessions running at once, trying to use TempDB, I’m going to use the free SQLQueryStress tool written by Adam Machanic (Thanks Adam!).
Now I need a query that will consume a lot of TempDB space.
SELECT *
into #messages
FROM sys.messages
SELECT severity
FROM #messages
WHERE text = 'String or binary data would be truncated.'I’ll run this query with 200 threads because I want a lot of contention.
Once I kick off that process, here’s a snippet of sp_WhoIsActive.
What’s the issue?
Well, the issue is PAGELATCH_UP on the tempDB data files, in that screenshot it’s files 5 and 9. There’s a lot of writing on the internet about the best way to solve TempDB contention. If you only have one or two files in TempDB but you have lots of CPU cores, adding more TempDB files might help.
What’s the solution?
In this example, we could filter when we load into the temp table (or not even use a temp table at all). That’s one of my proposed solutions.
Once you have decided on the right number of tempDB files, you might still see this issue. That’s why I picked an example that does searching extremely inefficiently. I want to emphasize that tuning your queries will often provide much bigger benefits than trying to use administration to fix bad T-SQL.
Let’s do two things to the query from earlier. We don’t need to load into a temp table, and we don’t need to SELECT *, we just need the severity column. Both of these improvements will reduce the total amount of resources used.
SELECT severity
FROM sys.messages
WHERE text = 'String or binary data would be truncated.'Time to take another look at sp_WhoIsActive.
What’s happening now? They’re still running?
Yes, but instead of waiting for resources, they’re all executing simultaneously.
Thanks for reading, stay tuned!