SharePoint - TempDB and contention

  • Article

https://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/11/tempdb-monitoring-and-troubleshooting-allocation-bottleneck.aspx

 

One key point is that the contention mentioned below and in the article can be identified by running the following query against the SQL instance in question:

SELECT session_id, wait_duration_ms, resource_description

      FROM    sys.dm_os_waiting_tasks

      WHERE   wait_type like 'PAGE%LATCH_%' AND

      resource_description like '2:%'