[轉]LATCH waittypes

標籤：

LATCH waittypes

A latch is a short-term lightweight synchronization object. The   following list describes the different types of latches:

• Non-buffer (Non-BUF) latch: The non-buffer latches provide synchronization services to in-memory data structures or provide re-entrancy protection for concurrency-sensitive code lines. These latches can be used for a variety of things, but they are not used to synchronize access to buffer pages.
• Buffer (BUF) latch: The buffer latches are used to synchronize access to BUF structures and their associated database pages. The typical buffer latching occurs during operations that require serialization on a buffer page, (during a page split or during the allocation of a new page, for example). These latches are not held for the duration of a transaction. These are indicated in the master.dbo.sysprocesses table by the PAGELATCH waittypes.
• IO latch: The IO latches are a subset of BUF latches that are used when the buffer and associated data page or the index page is in the middle of an IO operation. PAGEIOLATCH waittypes are used for disk-to-memory transfers and a significant waittime for these waittypes suggests disk I/O subsystem issues.

 

Latch_X用於內部資源的同步

PageIOLatch_X用於將disk中的資料頁同步到記憶體

PageLatch_X 用於記憶體中資料頁的訪問同步。

 

The following table lists the different latch waittypes that   you may notice in the master.dbo.sysprocesses system table in SQL Server 2000 and SQL Server 2005.

Lastwaittype	Waittype	Description
LATCH_NL	0x400	Null latch
LATCH_KP	0x401	Keep latch
LATCH_SH	0x402	Shared latch
LATCH_UP	0x403	Update latch
LATCH_EX	0x404	Exclusive latch
LATCH_DT	0x405	Destroy latch
PAGELATCH_NL	0x410	Null buffer page   latch
PAGELATCH_KP	0x411	Keep buffer page   latch
PAGELATCH_SH	0x412	Shared buffer page   latch
PAGELATCH_UP	0x413	Update buffer page   latch
PAGELATCH_EX	0x414	Exclusive buffer page   latch
PAGELATCH_DT	0x415	Destroy buffer page   latch
PAGEIOLATCH_NL	0x420	Null buffer page I/O   latch
PAGEIOLATCH_KP	0x421	Keep buffer page I/O   latch
PAGEIOLATCH_SH	0x422	Shared buffer page I/O   latch
PAGEIOLATCH_UP	0x423	Update buffer page I/O   latch
PAGEIOLATCH_EX	0x424	Exclusive buffer page   I/O latch
PAGEIOLATCH_DT	0x425	Destroy buffer page I/O   latch

原文：https://support.microsoft.com/en-us/kb/822101

[轉]LATCH waittypes