If you landed here from Brent’s weekly links, use this link to get my training for 90% off.
The access is for life, but this coupon code isn’t! Get it while it lasts.
Discount applies at checkout, and you have to purchase everything for it to apply.
As Recently As Now
Even on SQL Server 2019, with in-memory tempdb metadata enabled, and an appropriate number of evenly sized data files, you can experience certain types of contention in tempdb.
It’s better. It’s definitely and totally better, but it’s still there. With that in mind, I wrote a stored procedure that you can stick in your favorite stress tool, to see how tempdb handles different numbers of concurrent sessions. You can download it here, on GitHub.
If you need a tool to run a bunch of concurrent sessions against SQL Server, my favorite two free ones are:
If you need tools to help you identify tempdb contention, the ones I use are:
While I’m running one of those stress tools, this is how I use the stored procedures above to look for contention.
For sp_WhoIsActive, it’s really simple:
EXEC sp_WhoIsActive @get_task_info = 2;
The results will start to look like this when contention heats up. Again, things are a lot better now, but it can still happen.
For sp_HumanEvents, it’s still pretty simple:
EXEC dbo.sp_HumanEvents @event_type = 'waits', @seconds_sample = 10, @wait_type = N'PAGELATCH_UP, PAGELATCH_EX, PAGELATCH_SH';
Since I want to specifically look for waits that indicate tempdb is mixing it up, I’ll put the most common PAGELATCH waits in.
The output here is admittedly a bit truncated, because of limitations with the ring buffer extended event. But, you know, I think we can see enough.
If You See Contention
Check the basics first.
- You might need more data files
- Data files might be unevenly sized
- If you’re on < SQL Server 2016, you might need trace flags 1117 and 1118
- You might have a bunch of other stuff hemming up tempdb, too
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- SQL Server Community Tools: The Wrap Up And Combined Link
- SQL Server Community Tools: Changing The Output Of sp_WhoIsActive In Different Ways
- Temporary Object Concurrency With In-Memory tempdb Metadata In SQL Server 2019
- When Should You Use Table Variables In SQL Server? When Queries Execute Thousands Of Times A Minute