DDL contention on TempDB

Question

I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type being PAGELATCH_EX).

The issue appears to happen sporadically when the server is under decent load. I've been monitoring "Temp Tables for Destruction" rate and it can jump to 5,000+ during times when we have issues of PAGELATCH_EX on 2:1:103. From what I've read this counter should be 0 the majority of the time, but ours seems to stay anywhere from 300-1100 the majority of the time. The counter only goes to 0 when there is very few users on the system.

How can I narrow down what is causing the DDL contention on tempdb without having to look for a needle in a hay stack?

Answer 1

I've seen this very issue and the hotfix that was ultimately released to fix it was actually a direct result of my case with Microsoft CSS. There is no public KB article for the fix. Please make sure you've applied Service Pack 4 and the most recent cumulative update to SQL Server (at the time of writing, that's Cumulative Update #3 (9.00.5259)).

Until the hotfix was released, Microsoft's suggestion was to simply stop creating #temp tables (much like KB #916086). Since this would have meant a substantial re-write of dozens and dozens of reporting procedures, the workaround in my case (regardless of trace flags or temp file layout) was to restart our cluster every other weekend. Yuck.

In order to track down tempdb usage, there are several scripts around that can help, e.g. see Adam Machanic's sp_whoIsActive, specifically:

• sp_whoisactive: Analyzing Tempdb Contention
• How to identify which query is filling up the tempdb transaction log?

And also this script (and ones in the comments) from @SQLSoldier:

• Breaking Down TempDB Contention

I would make sure all your cursors are using LOCAL STATIC READ_ONLY FORWARD_ONLY (see this and this), and see if there are any known expensive queries that make extensive use of #temp tables / @table variables, CTEs, or may contain unnecessary sorts or lead to hash joins... all of which can contribute to the problem (I doubt you'll find one golden cause). The easiest sweeping fix as a "bang-for-your-buck" starting point will be to use proper and inexpensive cursor options instead of the defaults.

In the meantime I would (a) install CU#3 and (b) call PSS. Tell them you are after a very specific fix that has already been confirmed as a bug and released to other users as a private hotfix: "VSTS #109112 - Temp table deferred drop doesn't scale for certain workloads." You may have to pay the case fee initially but, since it is a bug, the charge should be refunded.

Answer 2

You probably need trace flag 1118

See Paul Randal's myths about tempdb first, and his TF 1118 article too

The TF is described here in KB 328551

I have no direct experience of this but it sounds like what I've read

Answer 3

I presume that you've already split out your TempDB data-files to try to alleviate contention (via pre-production first obviously). If you're braver, consider the trace flag that Paul Randal authoratively refers to: http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-(1230)-tempdb-should-always-have-one-data-file-per-processor-core.aspx

In terms of what is causing the pain, you need to do some investigative work:

• has this just started to occur? what has changed?
• is the server under memory pressure, so sorts have to be done in TempDB?
• are there any DBA processes like CheckDB, or online re-indexing, running?
• are more exotic isolation levels used, or service broker? have a look at sys.databases

There's a nice query at the bottom of this Microsoft TempDB document to try to work out what is using tempdb: http://technet.microsoft.com/en-gb/library/cc966545.aspx

Answer 4

If you are still looking to track this down, I recently had a similarly strange performance issue with synchronous table drops. If you have large numbers of databases (> 100 or so) on a sql instance running SQL 2005 and you have lots of temp table create and drop statements you can get slow temp table drops. Checking row count returned from sys.dm_db_index_usage_stats can rule this out pretty much right away as the culprit.

KB article describes the problem. http://support.microsoft.com/kb/2003031

Query performance decreases when sys.dm_db_index_usage_stats has large number of rows

Consider the following scenario:

In Microsoft SQL Server 2005, you frequently perform DDL operation that involve dropping and recreation of lot of tables (especially temporary tables in tempdb database). You have a large number of entries (100,000 or more) in the sys.dm_db_index_usage_stats dynamic management view (DMV).

Taken from my accepted answer to this question. There's some more detail there as well. Slow temp table drops in sql 2005