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/

 

 

 

About these ads
This entry was posted in DB Design, DMV, Internal, Lock/Blocking, Performance Tuning, TempDB, Troubleshooting. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s