SQL Server – Impact of High Worktables/sec and Workfiles/sec on tempdb Contention

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.

Trace flag 1118 forces uniform extent allocations instead of mixed page allocations. The trace flag is commonly used to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points. If you have SQL Server 2008 or SQL Server 2005 and the fix applied and are still encountering TEMPDB contention consider trace flag 1118 for further assistance in resolving the contention.