Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

4 Answers 4

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

share|improve this answer
    
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

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

share|improve this answer
    
unfortunately TF1118 has provided no help –  David George Sep 26 '11 at 20:21

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.

EDIT adding this as part of the answer instead of a comment where it may get lost:

In order to track down tempdb usage, there are several scripts around that can help, e.g. see Adam Machanic's sp_whoIsActive (specifically http://sqlblog.com/blogs/adam_machanic/archive/2011/04/21/analyzing-tempdb-contention-a-month-of-activity-monitoring-part-21-of-30.aspx) and also this script (and ones in the comments) from @SQLSoldier: http://www.sqlservercentral.com/blogs/robert_davis/archive/2010/03/05/Breaking-Down-TempDB-Contention.aspx. I would make sure all your cursors are using LOCAL STATIC READ_ONLY FORWARD_ONLY, 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.

share|improve this answer
2  
@Nick FAST_FORWARD allegedly implies both READ_ONLY and FORWARD_ONLY, but not LOCAL or STATIC. I list the 4 options out explicitly - see this post for more details, instead of blindly believing Microsoft. :-) sqlblog.com/blogs/hugo_kornelis/archive/2007/11/21/… –  Aaron Bertrand Sep 27 '11 at 16:02
1  
The article by Microsoft is what's unfair... it implies that just using FAST_FORWARD on its own will give you blinding performance, when the key in most cases I've tested have been LOCAL and STATIC. If you want to use FAST_FORWARD instead of FORWARD_ONLY and READ_ONLY then please feel free; I still do so out of habit (in fact I used to append FOR READ_ONLY to the query instead of the DECLARE CURSOR). My reply was mostly to ensure that future readers don't believe that FAST_FORWARD magically replaces all of the options I listed. –  Aaron Bertrand Sep 27 '11 at 16:23
2  
@Nick: MSDN does mislead when it comes to cursor options. And explicit is needed to allow for things like the CURSOR_DEFAULT option on a database which could mean multiple processes can't have the same cursor name –  gbn Sep 27 '11 at 18:18
1  
So did they tell you why your destruction counter is still going through the roof? –  Aaron Bertrand Sep 29 '11 at 15:36
1  
@Nick one other thing that came up when I was giving a presentation on bad T-SQL habits today in Liverpool is that in order to use LOCAL and STATIC, you can't use FAST_FORWARD (you get an error message about conflicting cursor options) but you can use FORWARD_ONLY READ_ONLY... even though the documentation implies that they are interchangeable, they're really not... –  Aaron Bertrand Oct 1 '11 at 21:27

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.