Tempdb contention on sysmultiobjrefs (2:1:103) causes performance problems

Tempdb contention on sysmultiobjrefs (2:1:103) causes performance problems by Michael J. Swart

Closed

as Fixed

Type:	Bug
ID:	777072
Opened:	1/21/2013 10:46:34 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

We have experienced tempdb contention (PAGELATCH_EX contention) which caused performance problems. The resource is always (2:1:103) and it is seen when many temp tables are created often.

I understand that this is a know issue, but I'm opening this issue to enable feedback/voting from others.

Details (expand)

English

SQL Server 2008

SQL Engine

Windows Server 2008 (all editions)

US English

See video of issue reproduced at http://www.sqlskills.com/insidercontent/201106/june2011insidertip.wmv

Queries are slow, and the reported wait resource is 2:1:103

No contention on resource 2:1:103

X64

File Name	Submitted By	Submitted On	File Size
june2011insidertip.wmv (restricted)	Michael J. Swart	1/21/2013	-

Posted by Microsoft on 2/20/2013 at 2:49 PM

Hi Michael. As you mentioned, this is a known issue. We did do some updates in this area that may address some (but unfortunately not all) of these scenarios. These should be in the latest release of SQL Server 2008 R2 and SQL Server 2012. If possible, can you please try out the latest updates and see if they help? Again, this won't fix every scenario. I am going to close this Connect item as fixed but if you are still seeing issues with specific scenarios, can you please open new Connect issues to track those?

Posted by Microsoft on 2/15/2013 at 8:41 AM

Hi Michael, thanks for your feedback. We are currently investigating to see what can be done here.

Posted by Michael J. Swart on 1/23/2013 at 5:40 AM

More info at http://michaeljswart.com/2013/01/pagelatch-contention-on-21103/

Posted by Michael J. Swart on 1/21/2013 at 10:54 AM

Avoid queries that create temp tables (or declare table variables) that can't be cached.