Analysis of pagelatch_x and pageiolatch_x in SQL Server

Document directory

• 1.1 What is latch?
• 1.2 What is pagelatch?
• 1.3 What is pageiolatch?
• Process Analysis

0. References

Chapter 2 Microsoft SQL Server enterprise-level platform management practices

Analysis of buffer latch timeout

What are pagelatch and pageiolatch?

1. Introduction to pagelatch_x and pageiolatch_x

When analyzing SQL server performance, you may often see pagelatch and pageiolatch. For example, execute the following tsql statement

Select * from sys.dm_os_wait_stats

The output result contains information about latch, as shown in:

1.1 What is latch?

Latch is a data structure used internally by SQL Server to synchronize resource access. It is similar to the critical section or readerwriterlock of the operating system. Latch protects the resources to be protected so that the access is synchronized and ordered. For example, when a thread obtains the exclusively used latch of a resource, other threads must wait if they also need to access the latch. There are new questions. What is the difference between latch and lock? The main difference is that the usage is different from the purpose. Latch is used to protect the physical access to some resources (such as pages) in SQL Server. It can be considered as a synchronization object. Lock emphasizes logical access. For example, a table is a logical concept. A physical table consists of many pages. When accessing a table's record, you may first need to obtain the table's share lock, then obtain the latch of a page, and then you can read the records of this page. Lock is global and managed by a unified lock manager. Latch does not have unified Manager Management.

1.2 What is pagelatch?

Pagelatch_x latch is a type of latch that SQL Server often adds to the data pages in the buffer pool. It is used to synchronously access data pages in the buff pool. Each page in the buff pool of SQL server has a corresponding latch. To access a page, you must first obtain the latch of the page. There are many types of pagelatch, such as the shared pagelatch_sh and exclusive pagelatch_ex. Exclusive access means exclusive access. Only one thread can access the service. Generally, update, insert, and delete operations are performed. Sharing means that multiple threads can obtain the latch at the same time. The following describes pagelatch by inserting input data in the same data page. Refer to the blog for Parsing Buffer latch timeout.

The data page of each SQL Server is roughly divided into three parts: the page header, the offset at the end of the page, and the data storage part. Suppose there is a table with the following structure:

CREATE TABLE test (    a int,     b int)

It stores data on the page. The page structure is roughly as follows:

In the page header, page attributes, including page numbers, are recorded,It also records the starting position of the idle part of the current page (m_freedata). In this way, SQL Server can quickly locate the start position when inserting new data. The offset at the end of the page records the starting position of each data row. In this way, SQL Server can quickly find each record without mixing the previous record with the next record. In the middle page: 100, there are now two records, (1,100) and (2,200 ). The starting position of the first record is 96, and the starting position of the second record is 111. It is a free space from 126. This page shows that the length of a column is 15. When the data rows on the page change, SQL Server not only needs to modify the data itself, but also the offset value, to ensure that SQL server can continue to accurately manage each row on the data page.

Now we assume there are two users At the same timeTo insert data into this table, one person inserts (3,300) and the other one inserts (4,400 ). Then the two users will read the same (M_freedata = 126). If there is no latch, these two insert statements can be run simultaneously. In the transaction logic, the two statements insert two irrelevant records, so they should not block each other. This is correct. A process inserts the following data on page 100: insert values (3,300). The result is as follows:

At this time, another process needs to insert the following data on page 100: insert values (4,400). Because there is no latch lock, the previous data will be overwritten. This causes data insertion problems. As shown in:

Logically, inserting two pieces of data does not interfere with each other, but there is a problem in the physical storage layer. The idle space where two pieces of data to be inserted are read m_freedata = 126. All the data must be inserted to this position. Therefore, one piece of data will be overwritten by another piece of data, leading to insertion errors. To solve this conflict, you must define a sequence. To solve this problem, SQL Server introducesLatch: pagelatch. When a task needs to modify the page, it must first apply for an ex latch. The content on the page can be modified only when this latch is obtained. Therefore, the two insert tasks not only apply for the lock on the page, but also apply for exclusive latch on the page. Suppose (3,300) the insert task is applied for first, then (4,400) the task will be blocked. Therefore, the (3,300) record can be inserted first, as shown in.

After (3,300) is inserted, the requested latch is released and the m_freedata data is updated. At this time (4,400), we can get the latch resource. This is to read m_freedata and then insert data to m_freedata. (4,400) is inserted after (3,300. In this way, both inserts are completed correctly, as shown in.

Since the modification of the data page is completed in the memory, the time for each modification should be very short and almost negligible. Pagelatch only appears during modification, so the lifecycle of pagelatch should be very short. If this resource becomes a resource that SQL Server often waits for, the following problems can be explained.

1. SQL Server does not have obvious memory and disk bottlenecks (congratulations !).
2. Applications send a large number of concurrent statements to modify the records in the same table. The table Architecture Design and user business logic make these modifications concentrated on the same page, or a few small pages. These pages are also calledHot page. Such a bottleneck usually only occurs in a typical OLTP system with a large number of concurrent users.
3. This bottleneck cannot be solved by improving the hardware configuration. Only by modifying the table design or business logic to distribute modifications to as many pages as possible can the concurrency performance be improved.
4. To modify the table design and introduce the partition concept, You can partition the pressure on the hot page to solve the above problem. For specific solutions, refer to another blog: parsing filegroup and partition in SQL Server.

1.3 What is pageiolatch?

When the data page cached in the memory buffer pool area interacts with the data page in the data file on the disk (that is, the data page is not in the memory ), to prevent multiple users from simultaneously reading/modifying data pages in the memory, SQL Server locks pages in the memory just like the data in the table, to synchronize concurrent processing by multiple users. The difference is that SQL Server adds latch (lightweight lock) instead of lock.

For example, when SQL Server reads data pages from data files into the memory, to prevent other users from accessing the same data page in the memory, SQL Server adds an exclusive latch to the memory data page. When a task needs to read pages cached in the memory, a shared latch will be applied. Like lock, latch may also be blocked. Depending on different waiting resources, the waiting status in SQL Server will be:

PAGEIOLATCH_DT : Destroy buffer page I/O latchPAGEIOLATCH_EX : Exclusive buffer page I/O latchPAGEIOLATCH_KP : Keep buffer page I/O latchPAGEIOLATCH_NL : Null buffer page I/O latchPAGEIOLATCH_SH : Shared buffer page I/O latchPAGEIOLATCH_UP : Update buffer page I/O latch

Here we will give you the most easily-occurring "pageiolatch_sh" and use it as an example to see how such a wait occurs. As shown in:

Process Analysis

1. There is a user request that reads the entire x table and is executed by worker x.
2. Worker x finds that it needs to read the data page at during table scan. The data size of a table may be larger or smaller than that of a page. If you perform table scan, you need to read all the pages that contain the table data.
3. SQL Server finds that the page is not in the memory data cache.
4. SQL Server finds a page space in the buffer pool and applies for an ex latch to prevent other users from reading or modifying the page before the data is read from the disk. Locking a long page is equivalent to reserving a space in the memory for storing the page that needs to be read from the disk physical.
5. Worker x initiates an asynchronous (asynchronous) I/O Request, requiring that the page be read from the database file.
6. Since it is an asynchronous I/O, worker x can continue to do the following. The following is to read the page in the memory. You need to apply for a sh latch for the read operation.
7. Since worker x has applied for an ex latch for this page1: 100 and has not been released, this sh latch will be blocked. Worker X is blocked by itself, and the waiting resource is pageiolatch_sh.
8. When the asynchronous I/O ends, the system will notify worker X that the data you want has been written into the memory, as shown in:

9. At this time, the ex latch will be released. Then worker x obtains the sh latch it applied.
10. The data page is finally read by worker x. After reading the data, worker x can continue the following operations.

As you can see, when a pageiolatch wait occurs, SQL Server must be waiting for the completion of an I/O action. Therefore, if an SQL Server often waits for this type of data,This indicates that the disk speed cannot meet the requirements of SQL Server.It has become a bottleneck of SQL Server.

It should be emphasized that the most common types of pageiolatch_x waiting are pageiolatch_sh and pageiolatch_ex. Pageiolatch_sh often occurs when users want to access a data page, while SQL Server wants to read the page from the disk to the memory. If this page is frequently accessed by users,After all, the problem is that the memory is not large enough to cache data pages in the memory.. Therefore, it is often because of memory pressure that triggers SQL Server to read a lot of pages, leading to the bottleneck of disk read. The disk bottleneck here is often a by-product of the memory bottleneck.

However, pageiolatch_ex often occurs when the user modifies the data page. When SQL Server wants to write back to the disk, it basically means that the write speed of the disk is obviously insufficient. There is no direct connection with memory bottlenecks.

Another wait state related to the disk is writelog, indicating that the task is currently waiting to write the log records to the log file. This wait status also means that the write speed of the disk is obviously insufficient.

Comparison between 1.4pagelatch and pageiolatch

1. Pagelatch aims to protect the correct page read and write operations in the buff pool. For example, if two threads want to insert data to a page at the same time, if there is no latch control, there is a conflict in the insertion position. Therefore, pagelatch_ex is introduced. Only one thread can get pagelatch_ex at a time, and only the pagelatch_ex thread can modify the page.
2. Pageiolatch is used for asynchronous data access. For example, if we want to read a page but it is not in memory, SQL Server will first empty a space for the page in memory and add ex_latch, then, before the page is actually read from the disk to the memory, other threads cannot operate on this memory. Because of asynchronous operations, this thread will access this page. At this time, sh_latch is applied, but with the previous ex_latch, it will eventually cause itself to be blocked. This isPageiolatch_sh.