TempDB Contention 2.1.%

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://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

http://www.mattwrock.com/post/2011/09/10/Latch-waits-on-21103-You-are-probably-creating-too-many-temp-tables-in-Sql-Server.aspx

http://michaeljswart.com/2013/01/pagelatch-contention-on-21103/