Brent Ozar shows us how to generate PAGELATCH_UP
and PAGELATCH_EX
waits:
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.
Read the whole thing.