9

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?

  • What is SELECT @@VERSION;? As per my answer my first suggestion will be to make sure you are on SP4 and the most recent cumulative update. – Aaron Bertrand Sep 26 '11 at 22:10
  • It is SP4 (9.00.5000) – David George Sep 27 '11 at 13:15
14

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:

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

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.

5

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

  • unfortunately TF1118 has provided no help – David George Sep 26 '11 at 20:21
5

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

  • The associated info on TF1118 is probably more important I reckon – gbn Sep 26 '11 at 19:46
  • @gbn It started a few months ago and there were no server changes. We have tried TF1118 with no luck as that doesn't really help with the issue we are having (serialized access to that system meta data table creating locks on 2:1:103). Stemming from a ton of temp tables needing to be destroyed. No DBA task are running during this time. No service broker and no exotic isolation levels. – David George Sep 26 '11 at 20:16
  • No server changes, but were there any application code changes? Is memory ok - page life expectancy, query run times etc? – Peter Schofield Sep 26 '11 at 21:29
  • I would give the multiple TempDB files a try - via pre-prod first to ensure there's nothing unexpected. It's an innocuous change which does work. Incidentally, have you checked your disc IO latencies, especially for TempDB? – Peter Schofield Sep 26 '11 at 21:36
  • I've tested all checked all that out and IO latency is not an issue. TempDB has been configured in several different multiple file configuration with no relief. It is a 24-core system so we have been running the 8 tempdev files, but have tried different configurations all the way to 24 files. Memory is okay, page life expectancy is also good. Query run times are up and down, but nothing crazy, or new. – David George Sep 27 '11 at 13:00
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

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.