Problem:
When you are performing many DML operations and makes tempdb contesnion on it, You may get wait_resource of 2.1.103
check perfomance counter:
- Temp Tables Creation Rate
- Temp Tables For Destruction
When you are performing many DML operations and makes tempdb contesnion on it, You may get wait_resource of 2.1.1 and 2.1.3
Sys,Dm_os_waiting_task – resource like (2:1:%) where % 1:PFS and 3 GAMs contention issue and 103 is DDL contension on Create/Drop temp table on sysmultiobjrefs objects.
Solution:
for 2.1.1 and 2.1.3:
>> check tempdb is on seperate LUN (drives)
>>Enable the trace flag -T1118
>>check the Sort and DML queries on temp objects and try to tune it.
for 2.1.103:
>> enable trace flag -T1118
>> try not us create temp objects.
>> try to include indexes on the temp table.
>> apply hotfix
Must read :
http://michaeljswart.com/2013/01/pagelatch-contention-on-21103/
