We are having problems on SQL Server 2012 with contention on tempdb page 2:1:128, which seems to be related with system table sysobjvalues.
We have about 400 sessions waiting on this resourse 2:1:128 with wait type PAGELATCH_EX.
Nothing has changed in our application or on database, so we don't know the reason for this. We've tried to identify the offending query (if there is one) without success.
The problem starts to occur all of a sudden and a couple of hours later it goes away. There are no backups running except for the transaction log backup every 15 minutes taking 1 to 2 minutes.
tempdb is using 8 files, on a 2 CPU x 4 cores machine. Anyway, the latch seems to be unrelated to IO problems.
We have 256Gb of RAM.
CPU is normal on the machine for the load it has (80%), and IO is normal also. The number of transations processed is also normal.
Here is the result of SELECT @@version:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) Dec 28 2012 20:23:12 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 (Build 9200: )
Any ideas on what is happening or where should we look to find the problem?