SQL Server Pageiolatch and Pagelatch

Latch is a lightweight lock, which is a data structure used internally by SQL Server to synchronize access to resources, which allows data access to be synchronized in order, which means that when a thread obtains the exclusive use of the resource R's latch, if other threads also want to access the latch, Then it must wait for the exclusive use of the latch to be released.

One, Latch introduction

Latch is divided into two main types: Buffer latch,i/o Latch, which are used to protect data pages in memory and data pages on hard disks. The type of latch is controlled by a bit of buffer,bit:buf_io, which indicates whether latch is currently doing an IO operation in buffer. When SQL Server reads a page from the hard disk, a buffer (page in memory) is reserved in buffer pool, and the bit buf_io for that buffer is set to 1. If the page reads from the hard disk and writes to the cache, the bit is set to 0. When Buf_io=1, the latch of the page request is Pageiolatch, and when buf_io=0, the latch for the page request is pagelatch.

Let's start with a brief introduction to I/O Latch. When SQL Server reads a page from a hard disk, it reserves the space for the page in memory. And a bit buf_io in the reserved space is set to 1. If the data is read and written from the hard disk, the bit is set to 0. During the reading of the page from the hard disk, the other threads that also need access to the page are, of course, waiting for the type pageiolatch_sh until read-write is complete and buf_io is set to 0. Another Latch is called Buffer Latch, which is used to protect the data structure in memory, and when a process needs to read a data page in memory, the process first gets the buffer Latch on that data page.

1,pageiolatch

If the data page does not exist in buffer pool, SQL Server must load the data into memory from the hard disk, at which point SQL Server needs to reserve a buffer in buffer pool and request a pageiolatch_ex for that buffer. Indicates that the data page or index leaf (index page) is being loaded into memory, and PAGEIOLATCH_EX is released if the data page is already loaded into memory. During a read from the hard disk to the cache, all threads that need access to the data page need to wait, and the wait type is: pageiolatch_sh, if we see a lot of pageiolatch_sh waiting, then it is basically possible to conclude that the problem is on disk performance.

PAGEIOLATCH_EX and Pageiolatch_sh are incompatible until the disk's read process is complete, PAGEIOLATCH_EX is released, and the thread requests to pageiolatch_sh, indicating that the data page already exists in buffer pool. If disk IO is slow, a session may apply PAGEIOLATCH_EX and pageiolatch_sh to the same page at the same time, blocking itself (block), after all, the purpose of requesting data writing is to read the data.

The process by which SQL Server reads a page from the hard disk is as follows:

1. Acquires an ex latch on the page this does not exist in the buffer pool (EX mode:blocking Others, and no duplicate same I O'll be issued) data page is not in memory, SQL Server requests and gets latch of the ex exclusive type of the page.
2. Issues the I/O request to the read the page from disk makes an I/O demand, and reads the page from disk into buffer pool.
3. Tries to acquire another latch that have the shared (SH) latch mode on the same page. Because an EX latch have already been acquired, the SH latch request is blocked, and the SPID are suspended, so temporarily It seems the same SPID was blocking itself tried to get another share type latch on the page. Because the latch ex of the page has been obtained, and the ex and SH are incompatible, SH must wait. It looks like you're waiting for yourself.
4. When the I/O request finishes, the ex latch on the page was released when the page reads finished, EX latch released.
5. Release of the ex latch gives the SH latch to the same thread because latch EX was released and latch SH was successfully obtained.
6. The thread can now read the page, and it has successfully obtained latch SH, which is available to read (the page is already in memory).

2,pagelatch

When accessing a database page (data page or index page), if the corresponding buffer already exists in buffer pool, SQL Server first obtains the latch of buffer, this latch is Pagelatch, The data in the buffer is then read.

Pagelatch is buffer Latch, which is used to protect the data structure in memory, and when a process needs to read a data page in memory, the process first gets the buffer Latch on that data page.

3, Pagelatch and Pageiolatch are all added to the memory page latch

If you want to read data from a data page in a database, the data page must exist in memory. If it does not exist in memory, SQL Server issues an IO request, loads the page into memory, and then reads the data from the data page in memory. In the process of data reading, SQL Server creates a latch structure that makes the process run smoothly. Before accessing a data page, you must first obtain the latch of the page. If the page is not in memory, get pageiolatch, or if the page exists in memory, get pagelatch.

PAGEIOLATCH_XX: When the data page is not in memory, SQL Server first reserves a page in memory, then reads from disk, loads to the memory page, at which point the SQL Server requests and obtains the latch type Pageiolatch, Pageiolatch indicates an IO operation is in progress. PAGEIOLATCH_EX indicates that the data page in disk is being loaded into memory, and PAGEIOLATCH_SH indicates that the process of loading the data page is not completed and is in the loading state when it attempts to read the data page in memory while loading the data page into memory. If pageiolatch_sh occurs frequently, it indicates that the loading data page is too long, and IO bottleneck may occur.

PAGELATCH_XX: The data page already exists in memory, and the latch that is added to the Memory data page is Pagelatch. At this time from disk loading data page operation has completed, in the loaded state, a database page both in memory and disk, before accessing the memory page, add latch, is pagelatch.

---- the parsing of Buffer Latch timeout

IO latch indicates that SQL Server is loading the data page in disk into buffer Pool, and once the data page is loaded, IO latch is freed. When loading the data page to buffer pool, SQL Server engine first reserves a page space in memory and sets the bit buf_io to 1. The page in memory is called buffer. When Buf_io=1, the latch of the page plus is pageiolatch. When the data load is complete, the page's bit buf_io is set to 0. When Buf_io is set to 0 o'clock, the latch for the page is Pagelatch. IO latch and Buffer latch are both latch of the in-memory page addition, except that Iolatch is the loading bit 1 o'clock in the data buf_io, and the data is added to the memory page. The load is completed, Buf_io is 0 o'clock, plus the latch on the memory page.

When SQL Server reads a page from the hard disk, the space for the page is reserved in memory, and the bit buf_io for that reservation is set to 1. If the data is read and written from the hard disk, the bit is set to 0. During the reading of the page from the hard disk, the other threads that also need access to the page are, of course, waiting for the type pageiolatch_sh until read-write is complete and buf_io is set to 0. So if we see a lot of pageiolatch_sh waiting, we can basically conclude that the problem is on disk performance.

Second, latch and performance

1. IO operation of data

Any page that SQL Server accesses must exist in memory, and if it does not exist in memory, SQL Server issues a disk IO request, reads the data page from disk into memory, and then SQL Server reads the page's contents from memory. Before accessing any of the memory page, you must request and obtain the latch on the page.

In the process of data reading, SQL Server first reserves a page space in memory, sets the page's bit buf_io=1, and issues a disk IO request, at which point the latch added on the page is PAGEIOLATCH_EX. Indicates that the data page is being read from disk to memory.

During the loading of the data page, any one reading the thread of the page, plus the latch on the page, is pageiolatch_sh, which means that when SQL Server engine reads the data page from disk, the thread attempts to read that page , because Pageiolatch_sh and PAGEIOLATCH_EX are incompatible, the thread that reads the page is block until the page is read into memory.

Once the data page is written to memory, PAGEIOLATCH_EX is immediately released and the bit buf_io=0 of the page is set. Because the data page exists in memory, the latch requested for the page is Pagelatch.

If a pageiolatch type of wait occurs, SQL Server must be waiting for the disk I/O operation to complete. If such a wait occurs frequently, the disk speed does not meet the requirements and has become a bottleneck for SQL Server. Pageiolatch_xx is divided into two major categories: Pageiolatch_sh and PAGEIOLATCH_EX.

Pageiolatch_sh occurs when a user accesses a data page while SQL Server is writing data pages from disk to memory, often pageiolatch_sh waits, indicating that the memory is not large enough to cause SQL Server to do a lot of page read operations. Disk IO is a side effect of memory pressure.

PAGEIOLATCH_EX: Occurs when reading from disk page to memory buffer, often occurs PAGEIOLATCH_EX wait, indicating disk read slow, which is not directly related to memory.

2, data page becomes hotpage

When a task modifies a page, it must first apply for an EX latch, or PAGELATCH_EX. You can modify the contents of a page only if you get to latch. Since the modification of the data page is done in memory, the time should be very short and negligible. The PAGELATCH_EX is only in the process of modification, so the life cycle should be very short, if it appears, note: 1, SQL Server does not have significant memory and disk bottlenecks. 2, the application issued a large number of concurrent statements to modify the same table, and the modification operation is concentrated on the same page, or a few pages, a hot page. 3, this bottleneck can not be solved by improving the hardware configuration, only modify the table storage, so that the changes scattered on more page, improve the ability of concurrent modification.

How to mitigate Hot page:

(1), replace a data column to build a clustered index, instead of the identity field, the same time the insertion has the opportunity to spread to different pages.

(2), if you must build a clustered index on the identity field, it is recommended that you create several partitions on one of the other columns.

(3), add file for filegroup, try to distribute files on different physical disk.

3, in tempdb, the system page becomes hot page

SQL Server not only adds latch on the data page, but also adds latch on the system page of the data file, such as Sgam, PFS, and GAM pages. System page can sometimes become a system bottleneck.

When you create a new table, SQL Server needs to allocate space. Allocating storage space requires modifying the SGAM, PFS, and GAM pages at the same time, setting the identity bit, because a system page's flag bit manages many data pages, when the table structure is frequently created and deleted, and the data pages occur on the same system page, or a few systems page, will cause the system page to overheat and become hot page. In tempdb, the creation and deletion of a table structure can occur concurrently, repeatedly, causing the system page to overheat and generate hot pages. The system page is overheated and can be mitigated by adjusting the table design. Workaround for this:

• Create the same tempdb file as the number of CPUs, keeping file size the same, so that each file assumes a relative average IO pressure
• Prevent tempdb files from growing automatically, avoiding an average growth of individual files

Reference Q&a on latches in the SQL Server Engine:

Anytime you talk about latching and database pages, think in terms of BUF (buffer). So-read a page from disk and put the "into cache, the worker thread would take out a EX (Exclusive) latch on the BUF" Ociated with the page. Then the read was issues for the page. Then a SH latch are taken out by this same thread on this BUF structure. Since These is not compatible, the thread would wait. Makes sense since we need-to-wait for the page-to-be-read-from-disk before trying to-look at it. Any and thread needing to read this page would also wait since you need a SH latch on a BUF to read the page in cache. When the read have completed, the EX latch is released, and now the SH latch is acquired and the thread (s) can read the Pag E in cache.

When a thread had to wait on a BUF latch, the engine would look at the BUF structure to see if a special bit was turned on C Alled Buf_io. This was set when a page associated with the BUF is currently being read from or written to disk. This is what the engine knows that if your thread was waiting on a latch, which is it was waiting on an "IO latch". The wait_type would therefore look something like pageiolatch_sh as opposed to pagelatch_sh.

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 Provid e re-entrancy protection for concurrency-sensitive code lines. These latches can is used for a variety of things, but they is not used to synchronize access to buffer pages.
• Buffer (BUF) latch:the buffer latches is used to synchronize access to BUF structures and their associated database page S. 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 is not held for the duration of a transaction. These is indicated in the master.dbo.sysprocesses table by the Pagelatch waittypes.
• Io latch:the io latches is a subset of BUF latches that is used when the buffer and associated data page or the index p The middle of an IO operation. Pageiolatch waittypes is used for disk-to-memory transfers and a significant waittime for these waittypes suggests disk I /O subsystem issues.

Reference Documentation:

Resolution of Buffer Latch timeout

What are Pagelatch and Pageiolatch?

Q&a on latches in the SQL Server Engine ....

SQL Server Pageiolatch and Pagelatch