[Turn] what is Pagelatch and Pageiolatch

You may often see Pagelatch and pageiolatch When analyzing SQL Server performance. Say

Select * from sys.dm_os_wait_stats

In the output, there is latch information.

So what are Pagelatch and Pageiolatch?

Say what latch first. Latch is a data structure used internally by SQL Server to synchronize resource access, similar to the operating system's critical section or ReaderWriterLock. Latch protects the resources that they want to protect, making access synchronized and orderly. For example, when a thread obtains the exclusive use of the latch of a resource, other threads must wait if they need to access the latch.

So what is Pagelatch?

Very simply, the latch that is used to synchronize access to the Database page is Pagelatch. Each database page (8kb page) in SQL Server's buffpool has a corresponding latch. To access a page, you must first obtain the page's latch. There are many kinds of pagelatch, such as shared pagelatch_sh, exclusive pagelatch_ex and so on. Exclusive means exclusive access. Sharing means that multiple threads can get this latch at the same time.

So what is Pageiolatch?

This is when the database page is not in memory and must be read from disk, then the latch type is pageiolatch. Other aspects are the same as Pagelatch.

An interesting phenomenon is that sometimes you find that a SPID is blocked by itself, and the waiting latch is pageiolatch_sh. Why would you wait for yourself? This starts with the process of reading the page from SQL Server. The process by which SQL Server reads a page from 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) the page is not in memory, SQL Server requests and gets the latch of the ex exclusive type of the page.
2. Issues the I/O request to the read the page from disk emits I/O to read it to Bufferpool.
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).

Some people may have doubts, what is the difference between latch and lock? The main use is not the same place and purpose. Latch is used to protect physical access to some resources within SQL Server, such as page, which can be considered a synchronization object. Lock, however, emphasizes logical access. A table, for example, is a logical concept, with many pages in the physical. When you access a table's records, you may first need to obtain a shared lock on the table, then get the latch of a page, and then you can read the page's records. Lock is global and managed by a unified lock manager. and latch does not have a unified manager management.

Original address:

Http://blogs.msdn.com/b/apgcdsd/archive/2011/11/29/pagelatch-pageiolatch.aspx

[Turn] what is Pagelatch and Pageiolatch