Michael J. Swart

Takeaway: Tempdb contention is a common DBA problem. I want to share three lesser known surprises about everyone’s favorite bottleneck.

If my professional life was made into a series, tempdb contention would have a recurring role as the antagonist. It provides challenges on a regular basis and I finally believe I understand it’s behavior well enough to have contention licked for good. But even recently, tempdb still holds some surprises for me.

You Can Still Get SGAM Contention After TF1118

You may know what trace flag TF1118 does. It affects the allocation of pages in all databases. But it’s most often used for its effect in tempdb. The idea is that TF1118 will cause SQL Server to store all table data in dedicated extents rather than mixed extents. Without mixed extents, the need to access SGAM pages is reduced and that leads to less contention as described in this KB article.

But the use of SGAM isn’t reduced to zero. What?! How can that be? Who’s still using mixed extents? It turns out that IAM pages are always stored on mixed extents. Every time any object is created (or destroyed) that will require at least one latch on an SGAM page. On an extremely busy system, you can still see contention here.

Equally Sized Data Files Can Become Unbalanced After Autogrow

That’s not the surprising thing. The surprising thing is that it can impact the effectiveness of having multiple data files and it needs to be kept in mind. Once again from Microsoft:

If data files [have] unequal sizes, the proportional fill algorithm tries to use the largest file more for GAM allocations instead of spreading the allocations between all the files.

This makes multiple data files less effective than it could be when tackling contention. But don’t disable auto-grow, just size your tempdb data files large enough (Tip: google “instant file initialization”)

Before we move on, if your tempdb data files have auto-grown, do you know what the initial sizes were?

Tempdb Contention Can Be Sudden and Catastrophic

I hadn’t seen this before. Most contention I’m familiar with looks like a “sluggish” database. The response times on particular queries just aren’t what they ought to be – getting gradually worse until the impact is felt hard. But just recently I have seen tempdb contention show up all-of-a-sudden making the database nearly useless. Especially after excessive blocking kicks in. Here’s how that can happen:

• Imagine a super-frequent procedure creates a temp table.
• Several of these temp tables are cached. (There can be several execution contexts for the procedure and so several temp tables).
• A table used in the procedure has enough modified rows to have it’s statistics auto-updated.
• The procedure’s query plan is invalidated and is dropped from the cache.
• The cached temp tables that were used for the procedure are marked to be destroyed
• The procedure is recompiled and new temp tables are created
• A system process destroys the marked temp tables that need to be cleaned up

With a very busy system, these things can happen in only a few seconds. In fact those two last items describe actions where hundreds of threads can get into a fight over allocation pages.

The following query may give a list of query plans for frequently called procedures as described:

with frequentSprocs as 
(
    select top 10 count(1) as [memory objects], cp.plan_handle from sys.dm_exec_cached_plans cp
    cross apply sys.dm_exec_cached_plan_dependent_objects(cp.plan_handle) do
    join sys.dm_os_memory_objects mo
        on do.memory_object_address = mo.memory_object_address
    where objtype = 'Proc'
    group by cp.plan_handle
    order by 1 desc
)
select fs.*, qp.query_plan
from frequentSprocs fs
cross apply sys.dm_exec_query_plan(fs.plan_handle) qp
option (recompile)

What Next?

I’m lucky to work with software that runs on systems that are crazy powerful and crazy busy. It’s unlikely that you’ll run into tempdb problems in the exact same way that we did. So the typical advice for PFS/GAM/SGAM contention is still valid:

• Enable trace flag TF 1118
• Create multiple (equally sized) data files for tempdb
• Reduce how frequently you use temp tables and table variables