Tempdb Contention That Can’t be Soothed By Adding Files

I’ve talked a lot in the past about tempdb contention and how to monitor for and identify it in blog posts, a whitepaper, webcasts, and live presentations. Okay, so I tend to get a little preachy about certain things and configuring tempdb to prevent tempdb contention is one of my top pet projects. But there’s some common types of tempdb contention that can’t be fixed by adding data files. i got an email this morning about one of these examples.

The email said they were getting contention in tempdb for resource 2:1:103. I recognized the page number as being a system table well-known for experiencing contention. If you didn’t know what this page was though, it would be easy to figure out using DBCC PAGE and the OBJECT_NAME() function. The resource description is in the format of :: and the syntax for the DBCC PAGE command is DBCC PAGE (, , , ). We’re going to be looking at just the header of the page, so it doesn’t matter which dump style we use. My go-to dump style is style 3 (most comprehensive) and that’s what I used here.

-- Redirect DBCC Page output to console
DBCC TraceOn (3604);

-- Output contents of page 103
DBCC Page (2, 1, 103, 3);

The header section of the output should look similar to the below image. find the line that says “Metadata: ObjectId =” and use the Object ID listed to query for the name of the object using the OBJECT_NAME() function. In this case, it would return the system table sysmultiobjrefs.

This system table, sys.sysmultiobjrefs, is described in Books Online as containing a row for each N to N reference in the database. This would encompass things like views and SELECT INTO statements. If your workload contains a lot of SELECT INTO statements, you may see this particular contention. since this contention is not related to the allocation pages (GAM, SGAM, and PFS), it cannot be alleviated by adding data files to tempdb because there is only 1 sys.sysmultiobjrefs no matter how many files there are.

This contention is caused by too many transactions trying to use the table at the same time. To sooth this particular pain, you need to review the code and try to lighten the load on this table by removing statements like SELECT INTO.

Want to see a demo?

While writing this blog post, I was thinking of how best to demo it. Then I remembered that i had already seen someone else demo this type of contention. After a little searching, I found the demo. It was a video by Paul Randal (blog|@PaulRandal) for one of their SQLSkills newsletters. You can follow the link on that page to the video. you can also sign up to receive future newsletters while you are there.