Pagelatch_x Waiting--Reprint

Turn from Source:http://www.cnblogs.com/xwdreamer/archive/2012/08/30/2663232.html0. References

Microsoft SQL Server Enterprise Platform Management practices Chapter 11th

Resolution of Buffer Latch timeout

What are Pagelatch and Pageiolatch?

1.pagelatch_x and pageiolatch_x Introduction

You may often see Pagelatch and pageiolatch When analyzing SQL Server performance. For example, execute the following TSQL statement

Select * from Sys.dm_os_wait_stats

It outputs the results with latch information, as shown in:

1.1 What is latch?

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. then there are new questions, 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, isa 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.

1.2 What is Pagelatch?

The pagelatch_x type of latch is a class of latch that SQL Server frequently adds to the data page in the buffer pool. It is used to synchronize access to data pages in the Buff pool. Each page in the buff pool in SQL Server 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, only one thread can access, general user Update,insert and delete operations. Sharing means that multiple threads can get this latch at the same time. The following inserts the input data into the same data page to illustrate the Pagelatch. You can refer to the blog: Buffer Latch timeout parsing.

the data page for each SQL Server is roughly divided into 3 parts: page header, footer offset, and data store section. Suppose that there is now a table with the structure:

CREATE TABLE Test (    a int,     b int)

It stores the data on the 1:100 page. So this page structure is roughly as follows:

In the header section, page properties, including page numbering, are recorded, as well as where the starting position of the current page's idle section (M_freedata) is recorded. This allows SQL Server to quickly find the location to start inserting when it is inserting new data. The offset at the end of the page records the starting position of each row of data. When SQL Server finds every record, it can find it quickly, and not confuse the previous record with the latter. In the page:100 there are now two records, (1,100) and (2,200). The first record starts at 96, and the second entry starts at 111. Starting from 126, it is free space. This page indicates that the length of a column is 15. When the data rows in the page change, SQL Server not only modifies the data itself, but also modifies the values of those offsets to ensure that SQL Server continues to accurately manage each row in the data page.

Now suppose there are two users alsoTo insert data into this table, one person inserts (3,300) and the other inserts (4,400). Then these two users will read the same (m_freedata=126)。 If there is no latch, these two INSERT statements can be run at the same time. In transaction logic, these two statements insert two unrelated records, so it should not be blocked from each other, so the processing is correct. A process on page 100, insert the following data: Insert VALUES (3, 300), the result is as follows:

At this point, another process to insert the following data on page 100: Insert VALUES (4, 400), because there is no latch lock, so the previous data will be overwritten. Cause data insertion problems. As shown in the following:

At the logical level, inserting two data does not interfere with each other, but at the physical storage level, there is a problem. The free space where the two data to be inserted is m_freedata=126, all of which are inserted into this position, then there is always one piece of data that is overwritten by another data, resulting in an insertion error. To find a way to resolve this conflict, be sure to define a sequence of priorities. In order to solve these problems, SQL Server introduces latch:pagelatchon another type of page. When a task is to modify a page, it must first apply for an ex latch. Only by getting this latch can you modify the contents of the page. So here are two insert tasks, not only to apply for the lock on the page, but also to apply for exclusive latch on the page. Suppose (3,300) the insertion task was first applied, and (4,400) the task would be blocked. So, (3,300) This record can be inserted first, as shown in.

When (3,300) the insert is completed, the latch it applies for is released, and the M_freedata data is updated. At this point (4,400) can get latch resources, this is to read the M_freedata, and then to the M_freedata location to insert data. (4,400) is inserted in the back of (3,300). In this way, two inserts are completed correctly, as shown in.

Since the modification of the data page is done in memory, the time for each modification should be very short and almost negligible. and Pagelatch only in the process of modification will appear, so pagelatch life cycle should also be very short. If this resource becomes a resource that SQL Server waits for frequently, the following issues can be described.

1. SQL Server does not have significant memory and disk bottlenecks (congratulations!). ）。
2. The application sends a large number of concurrent statements to modify the records in the same table, and the table schema design and user business logic make these changes on the same page, or on a few pages. These pages are sometimes referred to as Hot page. Such bottlenecks typically occur only on typical OLTP systems that are more concurrent users.
3. This bottleneck cannot be solved by improving the hardware configuration, but only by modifying the table design or the business logic to spread the changes across as many pages as possible to improve concurrency performance.
4. In order to modify the design of the table, so as to introduce the concept of partition, to solve the above problems, can be partitioned to share the hot page above the pressure. Specific workaround refer to another blog: SQL Server filegroup and partition parsing.

"Latch Application Mode"

Latch has the following modes when applying,

• Kp–keep Latch guarantees that the referenced structure cannot be destroyed
• Sh–shared Latch, you need to read the data page
• Up–update Latch You need to change the data page
• Ex–exclusive Latch Exclusive mode, mainly used to write data pages when the need
• Dt–destroy Latch is required to destroy the referenced data structure.

1.3 What is Pageiolatch?

When the data page in the buffer pool area is cached, and the data page in the data file on disk is interacting (that is, it is not in memory), in order to ensure that no multiple users can read/modify the data page in memory at the same time, SQL Server will be like the data in the table. The mechanism of locking the pages in memory to synchronize multi-user concurrent processing. The difference is that, here, SQL Server adds latch (lightweight lock), not lock.

For example, when SQL Server reads a data page from a data file into memory, SQL Server adds an exclusive latch to the data page of the memory in order to prevent other users from accessing the same data page in memory. When there is a task to read a page cached in memory, a shared latch is requested. Like lock, Latch also appears to be blocked. Depending on the waiting resource, the status of waiting in SQL Server is:

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:up Date buffer page I/O latch

Here's an easy-to-happen wait for "pageiolatch_sh", and take it as an example to see how this kind of wait is happening. As shown in the following:

Process Analysis

1. There is a user request that the entire X table be read and executed by Worker x.
2. worker x found it to read data page 1:100 while performing a table scan. The amount of data in a table may be larger than a page, or it may be smaller than a page. If you do a table scan, you need to read all the page that contains the table data.
3. sql Server Discovery page 1:100 is not in the in-memory data cache.
4. sql server finds a page space in the buffer pool, applies an ex's latch on it, and prevents the data from being read from the disk before anyone else reads or modifies the page. A long page lock is equivalent to reserving a space in memory for a page that needs to be physical read from disk.
5. worker x initiates an asynchronous (asynchronous) I/O request that requires page 1:100 to be read from the database file.
6. because it's an asynchronous I/o,worker x, you can go ahead and do what it's going to do. And the next thing to do is to read the in-memory page 1:100. The read action needs to apply for an sh latch.
7. this sh latch will be blocked because worker X has already applied an ex latch to the page1:100 before it has been released. Worker x is blocked by itself, and the waiting resource is pageiolatch_sh.
8. when the asynchronous I/O ends, the system notifies worker X that the data you want is already written to memory, as shown in:
   

9. this time ex latch was released. Then worker x gets the sh latch it applies to.
10. data page 1:100 finally read by worker X, the read work is finished, worker X can continue the following operation.

As a result, SQL Server must be waiting for the completion of an I/O action when a wait of type pageiolatch occurs. So if a SQL Server often waits in this category, it means that the speed of the disk does not meet the needs of SQL Server , which has become a bottleneck for SQL Server.

It is emphasized that the pageiolatch_x type waits for the most common of the two major classes, Pageiolatch_sh and PAGEIOLATCH_EX. Pageiolatch_sh often occurs when a user is trying to access a data page, while SQL Server reads the page from disk to memory. If this page is often accessible to users, then ultimately the problem is that the memory is not large enough to keep the data page cached in memory . Therefore, it is often the memory pressure, triggering SQL Server to do a lot of reading the page work, only to raise the disk read bottleneck. The disk bottlenecks here are often a byproduct of memory bottlenecks.

And pageiolatch_ex often occurs when the user makes changes to the data page, and when SQL Server writes back to the disk, it basically means that the disk's write speed is obviously not keeping up. There is no direct connection to the memory bottleneck.

Another disk-related wait state is Writelog, which indicates that the task is currently waiting to write log records to the log file. This wait state also means that the disk's write speed is obviously not keeping up.

1.4pagelatch and Pageiolatch Contrast

1. Pagelatch is to protect the correct read and write of the page in the buff pool, for example, there are two threads that want to insert data into a page at the same time, and if there is no latch control, there is a conflict in the insertion position. So the introduction of PAGELATCH_EX, only one thread at a time can get pagelatch_ex, only get pagelatch_ex line friend can modify the page.
2. The Pageiolatch is for asynchronous access to data. For example, if we want to read a page, but it is not in memory, then SQL Server will first empty a space for this page in memory, and add Ex_latch, and then the other thread cannot manipulate this memory until the page actually reads from disk into memory. Because of the asynchronous operation, so this thread will go to the page, at this time to apply for Sh_latch, but with the previous ex_latch, eventually caused themselves to be blocked. This is pageiolatch_sh.

Pagelatch_x Waiting--Reprint