JOIN sys.schemas s ON o.schema_id = s.schema_id order by wt.wait_duration_ms desc As shown below, we can see that the contention is on the table LATCHTEST and index name CIX_LATCHTEST. Note names have been changed to anonymize the workload. For a more advanced script which polls repeatedly and uses a temporary table to determine the total waiting time over a configurable period see in the Appendix. Alternative Technique to Isolate the Object Causing Latch Contention Sometimes it can be impractical to sys.dm_os_buffer_descriptors. As the memory in the system, and available to the buffer pool increases so does the time required to run this DMV. On a 256 GB system it may take up to 10 minutes or more for this DMV to run. An alternative technique is available and is broadly outlined as follows and is illustrated with a different workload which we ran in the lab: 1. Query current waiting tasks, using the Appendix script . 2. Identify the key page a convoy is observed, which happens when multiple threads are contending on the same page. In this example the threads performing the insert are contending on the trailing page in the B-tree and will wait until they can acquire an EX latch. This is indicated by the resource_description in the first query, in our case 8:1:111305. 3. Enable trace flag 3604 which exposes further information about the page via DBCC PAGE with the following syntax, substitute the value you obtained via the resource_description for the value in parentheses: --enable trace flag 3604 to enable console output dbcc traceon (3604) --examine the details of the page dbcc page (8,1, 111305, -1) 4. Examine the DBCC output. There should be an associated Metadata ObjectID, in our case ‘78623323’.
JOIN sys.schemas s ON o.schema_id = s.schema_id order by wt.wait_duration_ms desc As shown below, we can see that the contention is on the table LATCHTEST and index name CIX_LATCHTEST. Note names have been changed to anonymize the workload. For a more advanced script which polls repeatedly and uses a temporary table to determine the total waiting time over a configurable period see in the Appendix. Alternative Technique to Isolate the Object Causing Latch Contention Sometimes it can be impractical to sys.dm_os_buffer_descriptors. As the memory in the system, and available to the buffer pool increases so does the time required to run this DMV. On a 256 GB system it may take up to 10 minutes or more for this DMV to run. An alternative technique is available and is broadly outlined as follows and is illustrated with a different workload which we ran in the lab: 1. Query current waiting tasks, using the Appendix script . 2. Identify the key page a convoy is observed, which happens when multiple threads are contending on the same page. In this example the threads performing the insert are contending on the trailing page in the B-tree and will wait until they can acquire an EX latch. This is indicated by the resource_description in the first query, in our case 8:1:111305. 3. Enable trace flag 3604 which exposes further information about the page via DBCC PAGE with the following syntax, substitute the value you obtained via the resource_description for the value in parentheses: --enable trace flag 3604 to enable console output dbcc traceon (3604) --examine the details of the page dbcc page (8,1, 111305, -1) 4. Examine the DBCC output. There should be an associated Metadata ObjectID, in our case ‘78623323’.
SQLServerLatchContention.docx