Tempdb contention on sysmultiobjrefs (2:1:103) causes performance problems
The locks in tempdb were from type page latch on file 1 page 103 (2:1:103).
We can see it in our locks table.
Unfortunately this exact page is knows is problematic and also no complete solution currently exist.
Here are a few interesting links
Paul Randal, one of the leading SQL persons says that there is no solution:
That “the SQL Team knows about this. It’s a known issue. Hopefully something will be done about it in one of the future releases.”
But unlike SGAM or GAM contention, there’s absolutely nothing you can do about this to spread the contention around.
Microsoft are aware of it:
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?
Though there are ways to try and minimize this problem:
We know that we have trouble when there are queries that:
- are executed frequently
- create temp tables (either explicitly or by declaring table valued parameters).
- are not cached (Microsoft explains when temp tables are not cached. In my case, it was because they were ad-hoc queries)
- require a page latch on 2:1:103.
No comments:
Post a Comment