Home / server / Questions / 23489
In Process
Rob
Asked: 2009-06-11 08:57:41 +0800 CST

TempDB contention on sysmultiobjrefs SQL 2005

  • 772

We've been having trouble caused by what we believe is contention within tempDB.

Whenever we are having problems, our system is always waiting on one particular resource: 2:1:103, which when we look it up (using DBCC PAGE(2,1,103)) tracks back to object_id 75, which is the system table sysmultiobjrefs.

To solve this problem, we can sometimes get away with killing hanging spids waiting on that resource... in the worse cases, we have to actually stop SQL and start it back up.

Any ideas on how to alleviate this?

We're running SQL 2005 SP3 x64 on a quad/quad server with 128GB of RAM. Disks are also on a SAN with log/tempdb/data each on its own RAID 1/0 drive.

TempDB has 16 data files (one for each core) and one log file.

Thanks in advance.

sql-server sql-server-2005 tempdb

5 Answers

  1. Do you have a lot of SELECT INTO statements in your SQL Code? This will cause locking on several tempdb system objects until the SELECT INTO statement has completed.

    • 2

  2. Rob,

    In our environment we have been dealing with his 2:1:103 issue for about a month. One of the ways to prevent this issue has been to restart the SQL Service periodically , if that is an option. There has been no clear answer in many of the forums for this particular issue. T1118 flag has not been termed effective in arguments put forward by Linci Shea(MVP) and a couple of others in their blogs.

    One production scenario where i personally saw the issue happen and go away had been when SQL server got an opportunity to increase the memory from 24 Gb to 27GB. At 24gb, there were approximately 40 processes hung on 2:1:103 while a unrelated task job was running on the db server. I killed that task and SQL started taking more memory from the available 30 GB, the Tempdb contention gradually disappeared at 27GB in about a minute or so after it got 27GB. That is one area you can try to test it yourself. Reduce the footprints of other services on the DB server and increase the maximum available memory for SQL.

    Let me know if you find any other solution for the same.

    Singh.

    • 2

  3. I realize I'm late to the party here but my team had a run in with 2:1:103 this week. Essentially, contention on this resource indicates contention with DDL operations in tempdb and is caused by creating/destroying too many temp tables or temp table variables. I blogged about this at http://www.mattwrock.com/post/2011/09/10/Latch-waits-on-21103-You-are-probably-creating-too-many-temp-tables-in-Sql-Server.aspx Contention here is not going to be alleviated by trace flag T1118 or adding files to tempDb. The key is to either reduce the use of temp tables and temp table variables or to evaluate the context of their use to see if they are being cached. See http://technet.microsoft.com/en-us/library/cc966545.aspx for good details here.

    • 2

  4. Have you checked to make sure you aren't just getting deadlocks from transactions stepping on each other's work? Have you checked running/locked SQL queries when the locking occurs?

    • 1

  5. Have you tried to enable trace flag T1118?

    KB artikle from MS

    Quote from the article:

    Note Trace flag -T1118 is also available and supported in Microsoft SQL Server 2005 and SQL Server 2008. However, if you are running SQL Server 2005 or SQL Server 2008, you do not have to apply any hotfix.

    Increase the number of tempdb data files to be at least equal to the number of processors. Also, create the files with equal sizing. For more information, see the "More Information" section.¨

    There used to be an performance issue with traceflag T1118 in SP2, but Ms released a hotfix, and it should be fixed in SP3, like in your case.

    I agree with mrdenny about how the temp tables are created, you should never create a temptable with SELECT * INTO #x FROM TableA unless you have a WHERE clause like this :

    WHERE 1=2

    But my recommendation is to use CREATE TABLE #x syntax. Why? SQL will place a lot of locks in system tables as long as your query tries to fetch your data to insert into your temp table. If you use a where clause that obvious will not return any rows or the create table syntax, the locks will be held for a short period of time.

    /Håkan Winther

    • 1