SQL Server Performance Tuning resource Waits pageiolatch_x

I. Concept

Before introducing resources to wait for pageiolatch_x, let's look at the DMV view sys.dm_os_wait_stats for the various resources that are being analyzed from the instance level. It is the information that returns all the waits that are encountered by the executing thread, which is an analysis of the various waits from an actual level, which includes more than 200 types of waits, including the waiting time for pageiolatch disk I/O read and write, the wait time for lck_xx locks, Log Writelog write waits, latch pagelatch wait on the page, and others.

1. The following observations are based on the total time-consuming sequence, and the waiting wait_type analyzed here do not include the following

SELECTWait_type, Waiting_tasks_count, Signal_wait_time_ms, Wait_time_ms, max_wait_time_ms fromsys.dm_os_wait_statsWHEREWait_time_ms> 0         andWait_type not inch('Clr_semaphore','clr_auto_event',                               'Lazywriter_sleep','Resource_queue',                               'Sleep_task','Sleep_systemtask',                               'Sqltrace_buffer_flush','WAITFOR',                               'Logmgr_queue','Checkpoint_queue',                               'Request_for_deadlock_search','xe_timer_event',                               'Broker_to_flush','Broker_task_stop',                               'clr_manual_event',                               'Dispatcher_queue_semaphore',                               'ft_ifts_scheduler_idle_wait',                               'xe_dispatcher_wait','Xe_dispatcher_join',                               'Sqltrace_incremental_flush_sleep' )ORDER  bySignal_wait_time_msDESC

The above query will find the pageiolatch_x type of resource wait, because it is an instance level of statistics, want to get meaningful data, you need to look at the time interval of interest. If you want the interval to be parsed and you do not need to restart the service, you can reset it with the following command

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);  

Wait_type: Wait Type
Waiting_tasks_count: Number of waits for this wait type
Wait_time_ms: Total wait time for the wait type (including the total time spent in a process suspension state (Suspend) and the operational status (Runnable))
Max_wait_time_ms: Maximum wait time for this wait type
Signal_wait_time_ms: The time difference between a waiting thread from a signaled notification to its beginning to run (a process can run in a state (Runnable) for the total duration)
IO wait Time ==wait_time_ms-signal_wait_time_ms

Two. pageiolatch_x

2.1 What is latch

In SQL Server latch is a lightweight lock, unlike lock. Latch is used to synchronize the internal objects of SQL Server (synchronous resource access), and lock is used to synchronize user objects including (tables, rows, indexes, etc.) with a simple summary: Latch is used to protect some resources inside SQL Server, such as page ) physical access, which can be considered a synchronization object. Lock, however, emphasizes logical access. A table, for example, is a logical concept. The lock lock is described in more detail in "SQL Server lock and transaction dispel".

2.2 What is Pageiolatch

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.
When the queried data page is found in the buffer pool, there is no wait. Otherwise, an asynchronous IO operation is issued, and the page is read into buffer pool, and the connection remains in pageiolatch_ex (write) or pageiolatch_sh (read) Wait state until it finishes, which is the wait between the buffer pool and the disk. It reflects the waiting time for querying disk I/O reads and writes.
When SQL Server reads the data page from the data file into memory, in order to prevent other users from accessing the same data page in memory, SQL Server adds an exclusive lock latch to the data page of the memory, and when the task reads a page that is cached in memory, it requests a shared lock. Like lock, latch will also appear blocked, depending on the waiting resource, the wait status is as follows: Pageiolatch_dt,pageiolatch_ex,pageiolatch_kp,pageiolatch_sh,pageiolatch _up. Focus on PAGEIOLATCH_EX (write) and pageiolatch_sh (read) Two kinds of waiting .

2.1 Ageiolatch Flowchart

Sometimes when we analyze the current active user state, an interesting phenomenon is that sometimes you find that a SPID is blocked by itself, waiting for the 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): By a user request, get scan x table, performed by Worker X.

(2): During the scanning process found it needed data page with 1:100.

(3): Dough page 1:100 is not in the in-memory data cache.

(4): SQL Server found in the buffer pool can be stored in the page space, the above with the ex latch lock, prevent the data from the disk read out, others also to read or modify this page.

(5): Worker X initiates an asynchronous I/O request that requires reading page 1:100 from the data file.

(6): Because it is asynchronous I/O (can be understood as a task sub-thread), worker X can then do what it wants to do, is to read out in-memory page 1:100, read the action needs to apply for an sh latch.

(7): Because the worker x previously applied for an ex's latch lock has not been released, so the SH latch will be blocked, worker X is blocked by itself, waiting for the resources is pageiolatch_sh.

Finally, when the asynchronous I/O ends, the system notifies worker x that the data you want is already written to memory. Then the ex's latch Lock is released, and the worker x applies for the latch lock of the sh.

Summary: First, the worker is an execution unit, and there are multiple tasks below that relate to the smallest task unit running on the worker, so that the worker produces the first X task, and the 5th step to initiate an asynchronous I/O request is the second task. Two tasks belonging to a Worker,worker x were blocked by themselves. About task scheduling learn to view SQL Server Task Scheduler with CPU.

2.2 Specific analysis

By understanding that if the disk speed does not meet the needs of SQL Server, it will become a bottleneck, usually pageiolatch_sh from disk read data to memory, if the memory is not large enough, memory pressure when it will be released, the data page will not be in the memory of the data cache, This causes the memory problem to be the bottleneck of the disk. PAGEIOLATCH_EX is write data, this is generally the disk write speed obviously keep up, and memory is not directly related.

Here is the resource wait time for query pageiolatch_x:

Select Wait_type,waiting_tasks_count,wait_time_ms, Max_wait_time_ms,signal_wait_time_ms  from sys.dm_os_wait_stats where  like ' pageiolatch% ' Order  by Wait_type

Through the above SQL query, you can periodically empty the data to do statistical data analysis, when found to be abnormal, to complete the business to analyze memory and disk. About Memory View "SQL Server memory Primary" Disk View "SQL Server I/O hard disk interaction" after the analysis from the Windows System Performance Monitor aspect.

　　

　　

SQL Server Performance Tuning resource Waits pageiolatch_x