Tempdb contention and this time it’s not on PFS, GAM, SGAM

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…

https://support.microsoft.com/en-us/help/4058174/heavy-tempdb-contention-occurs-in-sql-server-2016-or-2017

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?

Leave a Reply

Your email address will not be published. Required fields are marked *