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 Performance Exposed by by cost Guarantee SQL Performance  Server cost is low. - Provides information on of engine To allow for PAGELATCH, PAGEIOLATCH and LATCH memory only. maximum wait types (LATCH_EX, LATCH_SH is used structures. concurrency to group all non-buffer latch waits). and provide  maximum performance Provides detailed information about – , latches are non-buffer latch waits. held only for  the duration of the - This DMV provides aggregated waits physical for each index, which is very useful for operation on troubleshooting latch related performance the issues. memory structure, unlike locks which are held for the duration of
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 ,
SQLServerLatchContention.docx