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.

  • 2
    How many tempdb data files you have how many cores does SQl Server machine has. – Shanky Feb 1 '16 at 12:41
  • One mdf and one ldf for tempdb on 2 physical CPU machine 4 core each (not quite sure about hyperthreading.) 16 logical CPU count total. – Stackoverflowuser Feb 1 '16 at 13:39
  • I guess you will advise to increase the number of tempdb data files but isn't that practice for solving pfs,sgam..etc contention? – Stackoverflowuser Feb 1 '16 at 13:58
  • 1
    @Stackoverflowuser you for sure know that its some contention in tempdb .. have you enabled TF 1117 and 1118 ? Before doing that, make sure that the tempdb datafiles are all equally sized. Also, did you see if the contention was due to blocking ? You need to find out what exact queries are being used, get their query plans and see if you can improve them. – Kin Shah Feb 1 '16 at 16:10
  • 1
    @Stackoverflowuser you can use this script. It will print the create statements based on the number of processors. Adjust the initial size as per your environment. – Kin Shah Feb 4 '16 at 19:30

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.