Stressing tempdb and Observing Contention In SQL Server

Pssst!


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:

Strikey


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.

sp_WhoIsActive
hello… you

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.

SQL Server Query Results
cheesey plate

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!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount 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.



2 thoughts on “Stressing tempdb and Observing Contention In SQL Server

  1. Do not worry, just give an access to a 25 years old people to the SQL Server source code, …and it will be kidda solved, don’t worry… it’s the nature…
    But, by the way, I don’t understand why a “DESCRIPTION” column will be a NVARCHAR(MAX) just for the purpose of searching on it…. My answer?
    ….just give an access to a 25 years old people to the SQL Server source code…
    I am so Italian, … just give…
    I lost my poetry… sorry(!)(?)

Comments are closed.