Sql-server – TempDB contention on sysmultiobjrefs SQL 2005

sql serversql-server-2005tempdb

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.

Best Answer

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.

Related Topic