Demoing Latch Waits with Stupid Tricks

Say you’ve got PAGELATCH_UP or PAGELATCH_EX waits, and you’re wondering what might be causing ’em.

I’m going to keep the brutally simple approach of building a stored procedure to simply dump hard-coded values into a table at high speed. I don’t want to select from other tables since they might introduce some other types of waits, especially when I’m demoing concurrency quickly.

So let’s create a stored procedure that does an insert into a table:

And then use SQLQueryStress to run 200 threads with it:

SQLQueryStress, stressin’ the queries

When we run sp_BlitzFirst @ExpertMode = 1 to take a live sample of wait stats, the results aren’t all that impressive:

Small fries

Because frankly, our code isn’t all that impressive. We’re only inserting 3 rows. What if we insert a lot more? Let’s build SQL dynamically:

That gives me a nice long list of values:

Valuable values

That I can dump into my stored procedure:

I could go on and on

And now, when I run it from 200 threads of SQLQueryStress, the waits for inserting 1,000 hard coded values at a time – each of which needs a page for its whopper CHAR(8000) field – look a little different:

Like cowboy up, but different

That’s what it looks like when your system is under heavy contention due to a lot of queries partying in table variables: in each second, each core on this system is spending 48 seconds waiting on PAGELATCH_UP. That’s awesome.

What about temp tables instead, you ask? Same symptoms: PAGELATCH_UP.

How about PAGELATCH_EX?

You don’t see exclusive locking on temp tables and table variables because they’re per-session. Global temp tables, however, that’s another story:

Again, use your system-generated list of values to throw in 1,000 rows each time. Don’t bother selecting anything from the temp table or deleting the rows – that’d only slow us down. The result: exclusivity:

All my PAGELATCH_EXes live in Texas

Now, everybody’s fighting over the right to insert into a single shared object, the global temp table. The same thing happens if you use a user table:

You end up in a brutal fistfight for exclusive rights to insert rows into an existing page:

PAGELATCH_EX waits hitting user tables

You can change the severity of the waits by adding or removing fields on the tables involved. Want to simulate more contention on a single page? Remove the CHAR(8000) field. Want to throw big fields in the mix? Toss in some off-row NVARCHAR(MAX) – but like we generated a hard coded list of IDs ahead of time, do something similar with your other fields, too, lest you end up locked in contention for the source table.

When I’m facing unusual waits like these in production, I love demoing them with the simplest queries possible. This way, we can quickly show how hardware helps (or doesn’t help) a particular bottleneck – especially as opposed to just changing the code or indexes.

Previous Post
How Table Variables Mess With Parallelism
Next Post
Announcing a Very Special #SQLFamily Webcast with Dr. David DeWitt

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}