Tempdb can be a major bottleneck on a busy SQL Server instance. A common source of this is PFS, GAM, or SGAM page contention. Lately I’ve seen on more than one SQL 2016 instance where a different page became a major bottleneck (if you are on SQL 2017 this may apply to you as well). The page was related to sysobjvalues in tempdb.
How would I see this? If you query sys.dm_exec_requests you will see a lot of sessions in PAGELATCH_* wait_type. If you look at the wait_resource column it will give you the database:file:page where the PAGELATCH contention is occurring.
select er.session_id, er.wait_type, er.wait_resource
from sys.dm_exec_requests er
inner join sys.dm_exec_sessions es on er.session_id = es.session_id
where es.is_user_process = 1
If you run the above query while you are having this issue, you will see output that looks similar to below. You will see a lot of sessions waiting on PAGELATCH_* waits. They will also be waiting on the same page in tempdb. In this case 2:7:384885.
The first value in the sequence 2:7:384885 is the database id. 2 always points to tempdb. The file is the second value 2:7:384885. The page in the file is the third value 2:7:384885. These are going to vary on your system.
To determine what table is associated with this database:file:page you can perform a dbcc page to retrieve the object id and subsequently retrieve the object name. The parameters passed are database id, file, and page. The 0 indicates indicates to just dump the header. the dbcc traceon causes the output to display to the messages tab.
dbcc traceon;
dbcc page (2, 7, 384885, 0);
Below is the output showing the object id.
Now that we have the object id we can look up what object this page is associated with.
USE tempdb
SELECT * FROM sys.objects WHERE OBJECT_ID = 60
In this scenario this points to sysobjvalues. With a little bit of googling…
Based on the link above, it appears this issue exists in 2017 as well (although I have not seen it yet). Applying the appropriate CU’s fixes this problem:
Cumulative Update 5 for SQL Server 2017
Cumulative Update 8 for SQL Server 2016 SP1
Caveats
You need to actively be monitoring sessions as this scenario occurs to see this. You can use a performance monitoring tool (such as SQLGrease free) to capture this, or you can attempt to roll your own solution which stores the output of executing sessions.
Conclusion
If you have a server with a busy tempdb and migrate to 2016 or 2017 be sure to apply the appropriate CU listed above.
Watch us diagnose a query timeout with SQLGrease:
Free you say? How can that be?