I had a nice production problem today that slowed everything down drastically. I’ll spare you the details of the user processes, but when looking in sys.sysprocesses, I noticed that the waitresource was ‘2:%’. I also correlated this with the wait_type column in dm_os_waiting_tasks and saw a lot of PAGELATCH_UP types. So the first thing I did was pull up the page# in dbcc page, and noticed it was page type 11.
In my case, here’s what I typed:
DBCC traceon(3604)
DBCC Page(2, 1, 186204, 3)
And I might add that there were a lot of them backed up. I had something like 30 blocked processes and they were all waiting on this same page in tempdb. Page type 11 is a PFS page so this meant I was having contention in tempdb.
And since I always like the low-hanging fruit, I chose to add more files instead of using -T1118.
So I added 6 files to the 16 that were already there and the problem cleared up almost instantly.
You don’t have to use DBCC Page though. As it turns out, I was just surfing around afterwards to see what was out there on this issue , and I found a great blog by MCM Robert Davis that has a lovely query that’ll tell you right away whether you have tempdb contention. I was gonna paste the query in here, but go read it for yourself.
Thanks for the mention! There is an updated version of the query available here: http://www.sqlsoldier.com/wp/sqlserver/breakingdowntempdbcontentionpart2