Tools and methods for diagnosing latch contention
Logical file layout can affect the level of page latch contention caused by
allocation structures such as Page Free Space (PFS), Global Allocation Map (GAM),
Shared Global Allocation Map (SGAM), and Index Allocation Map (IAM) pages. For
more information, see
TempDB Monitoring and Troubleshooting: Allocation
Bottleneck.
Significant
waits indicate SQL Server is waiting on the I/O subsystem.
This section provides information for diagnosing SQL Server latch contention to determine if
it’s problematic to your environment.
The primary tools used to diagnose latch contention are:
Performance Monitor to monitor CPU utilization and wait times within SQL Server and
establish whether there’s a relationship between CPU utilization and latch wait times.
The SQL Server DMVs, which can be used to determine the specific type of latch that is
causing the issue and the affected resource.
In some cases memory dumps of the SQL Server process must be obtained and analyzed
with Windows debugging tools.
The technical process for diagnosing latch contention can be summarized in the following
steps:
-
Determine that there’s contention that might be latch-related.
-
Use the DMV views provided in
Appendix: SQL Server Latch Contention Scripts
to
determine the type of latch and resources affected.
７
Note
This level of advanced troubleshooting is typically only required if troubleshooting non-
buffer latch contention. You might wish to engage Microsoft Product Support Services for
this type of advanced troubleshooting.
PAGEIOLATCH