the logical transaction. Lock Guarantee Can be Performance  cost is high of by relative to user. latches as  locks must be held for the duration Note of the For more information about querying transaction. 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 meaning that “lightweight”, 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.
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.
SQLServerLatchContention.docx