deterministic, application decisions including schema design can affect this behavior. The goal of this paper is to provide the reader with the following:  Background information on how latches are used by SQL Server.  Tools used to investigate latch contention.  How to determine if the amount of contention being observed is problematic. We will discuss some common scenarios and how best to handle them to alleviate contention. How does SQL Server Use Latches? A page in SQL Server is 8KB and can store multiple rows. To increase concurrency and performance, buffer latches are held only for the duration of the physical operation on the page, unlike locks which are held for the duration of the logical transaction. Latches are internal to the SQL engine and are used to provide memory consistency, whereas are used by SQL Server to provide logical transactional consistency. The following table compares latches to locks: Purpose by Performance cost Exposed by Guarantee of structures. SQL Server engine only. Performance cost is low. To allow for maximum concurrency and provide maximum latches are held only for the duration of the physical operation on the structure, unlike locks which are held for the duration of  - Provides information on PAGELATCH, PAGEIOLATCH and LATCH wait types (LATCH_EX, LATCH_SH is used to group all non-buffer latch waits).  – Provides detailed information about non-buffer latch waits.  - This DMV provides aggregated waits for each index, which is very useful for troubleshooting latch related performance issues. performance memory memory ,
the logical transaction. Lock Guarantee of Can be by user. Performance cost is high relative to latches as locks must be held for the duration of the transaction.   Note For more information about querying SQL Server to obtain information about transaction locks see SQL Server Latch Modes and Compatibility Some latch contention is to be expected as a normal part of the operation of the SQL Server engine. It is inevitable that multiple concurrent latch requests of varying compatibility will occur on a high concurrency system. SQL Server enforces latch compatibility by requiring the incompatible latch requests to wait in a queue until outstanding latch requests are completed. Latches are acquired in one of 5 different modes, which relate to level of access. SQL Server latch modes can be summarized as follows:  KP – Keep latch, ensures that the referenced structure cannot be destroyed. Used when a thread wants to look at a buffer structure. Because the KP latch is compatible with all latches except for the destroy (DT) latch, the KP latch is considered to be “lightweight”, meaning that the impact on when using it is minimal. Since the KP latch is incompatible with the DT latch, it will prevent any other thread from destroying the referenced structure, for example a KP latch will prevent the structure it references from being destroyed by the lazywriter process. For more information about how the lazywriter process is used when SQL Server writes to and frees up buffer pages see (http://go.microsoft.com/fwlink/p/?LinkId=223176).  SH – Shared latch, required to read a page structure.  UP – Update is compatible with SH (Shared latch) and KP, but no others and therefore will not allow an EX latch to write to the referenced structure.  EX – Exclusive latch, blocks other threads from writing to or reading from the referenced structure. One example of use would be to modify contents of a page for torn page protection.
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’.