I am having tempdb contention (I guess, at least) on one of the servers (SQL Server 2008 R2).
Both the blocker and the waiters (10 of them) has PAGELATCH_EX wait type on database tempdb and all the sessions' (blocker and the waiters) waitresource is "2:1:122".
I checked the SQL codes and saw that table variables are created in most of the sessions and I suspect that might be the culprit.
Any suggestions on how to diagnose further and alleviate this problem is appreciated.
Best Answer
PAGELATCH_XX - This indicates that SQL is waiting on access to a database page, but the page is not undergoing physical IO. This class of problem is caused by a large number of spids attempting to access the same physical page at the same time. The wait_resource is the page number (the format is dbid:file:pageno) that is being accessed. If diagnosing on a live server, the Performance Dashboard reports will run DBCC PAGE and display the output to tell you what object and type of page the contention is for (allocation, data, text, ...)
Since the pages that SQL is most frequently waiting on are in the tempdb database (page number in dbid 2), you may be facing tempdb allocation latch contention.
Tempdb allocation page latch contention can occur with workloads that create and destroy temp objects (including worktables/workfiles for sort or hash operations) hundreds or thousands of times per second.
Resolution -
1.Implement trace flag -T1118.
2.Increase the number of data files in tempdb to maximize disk bandwidth and to reduce contention in allocation structures. As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.