处理过多SQL Server PAGEIOLATCH_SH等待类型

One of the most common wait type seen on SQL Server and definitely one that causes a lot of troubles to less experienced database administrators is the PAGEIOLATCH_SH wait type. This is one of those wait types that clearly indicates one thing, but which background and potential causes are much subtler and may lead to erroneous conclusions and worse, incorrect solutions

PAGEIOLATCH_SH等待类型是SQL Server上最常见的等待类型之一，并且肯定给经验不足的数据库管理员造成很多麻烦的一种。 这是一种清楚地表明一件事的等待类型，但是哪些背景和潜在原因很微妙，并可能导致错误的结论和更糟糕，不正确的解决方案

The Microsoft definition of this wait type is:

Microsoft对这种等待类型的定义是：

Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.

当任务在闩锁上等待I / O请求中的缓冲区时发生。 闩锁请求处于共享模式。 长时间等待可能表明磁盘子系统有问题。

To make this simple to understand, lets explain this in an example. When the data pages are requested from the buffer cache by one or multiple sessions, but those data pages are not available in the buffer cache, SQL Server will have to allocate a buffer page for each one in the buffer and it will create the PAGEIOLATCH_SH on the buffer. At the same time, while the page is moved from the physical disk to a buffer cache (a physical I/O operation) SQL Server will create PAGEIOLATCH_EX wait type. Once the page is moved to a buffer cache, since the issued PAGEIOLATCH_SH is still active and the pages can be read from the buffer cache

为了使这一点易于理解，让我们在一个示例中进行解释。 当一个或多个会话从缓冲区高速缓存中请求数据页，但这些数据页在缓冲区高速缓存中不可用时，SQL Server将必须为缓冲区中的每个页面分配一个缓冲区页面，并在上创建PAGEIOLATCH_SH缓冲区。 同时，在将页面从物理磁盘移动到缓冲区缓存（物理I / O操作）时，SQL Server将创建PAGEIOLATCH_EX等待类型。 将页面移至缓冲区高速缓存后，由于发出的PAGEIOLATCH_SH仍处于活动状态，因此可以从缓冲区高速缓存中读取页面

Page latches are actually light locks that are not configurable, placed by SQL Server internal processes as a way of managing access to the memory buffer. Pahe latches are placed every time that SQL Server has to physically read data from the memory buffer to a hard drive or from hard drive to the memory buffer and the thread must wait until this completes causing the PAGEIOLATCH_XX waits. The moment the requested data pages became available after the I/O reading completes, the thread will get requested data and will continue with execution. So obviously, it is normal to encounter some PAGEIOLATCH_SH waits

页锁实际上是不可配置的轻锁，由SQL Server内部进程放置，以管理对内存缓冲区的访问。 每当SQL Server必须从内存缓冲区向硬盘驱动器或从硬盘驱动器向内存缓冲区物理读取数据时，都会放置Pahe闩锁，并且线程必须等待直到完成，从而导致PAGEIOLATCH_XX等待。 I / O读取完成后，请求的数据页可用时，线程将获取请求的数据并继续执行。 所以很明显，遇到一些PAGEIOLATCH_SH等待是正常的

So it is clear that the PAGE IOLATCH_SH is directly related to the I/O subsystem, but does this actually mean that in case of excessive PAGE IOLATCH _SH, the I/O subsystem is always the primary/only root cause of the trouble?

因此，很明显PAGE IOLATCH_SH与I / O子系统直接相关，但这是否真的意味着在发生过多PAGE IOLATCH _SH的情况下，I / O子系统始终是问题的主要/唯一根源？

In short, the answer is no. The high PAGEIOLATCH_SH, even though it indicates the pressure is on the I/O subsystem, doesn’t necessary mean that I/O subsystem is a bottleneck per se, but it could also mean that I/O subsystem cannot cope with the excessive I/O imposed to it.

简而言之，答案是否定的。 PAGEIOLATCH_SH较高，即使它表示I / O子系统承受着压力，也不一定意味着I / O子系统本身就是一个瓶颈，但这也可能意味着I / O子系统无法应对过多的I / O强加给它。

To understand this better, let’s dive deeper into the causes of high PAGEIOLATCH_SH which will allow better understanding of this wait type, but will also allow better handling of situations when PAGEIOLATCH_SH is prevalent wait type in SQL Server

为了更好地理解这一点，让我们更深入地研究导致PAGEIOLATCH_SH高的原因，这将有助于更好地了解此等待类型，但也可以更好地处理PAGEIOLATCH_SH是SQL Server中普遍的等待类型的情况。

• I/O subsystem has a problem or is misconfigured

  I / O子系统有问题或配置错误

• Logical/physical drive misconception

  逻辑/物理驱动器误解

• Network issues/network latency

  网络问题/网络延迟

• Overloaded I/O subsystem by another processes that are producing the high I/O activity

  I / O子系统重载了另一个正在产生高I / O活动的进程

• Memory pressure

  记忆压力

• Synchronous Mirroring and AlwaysOn AG

  同步镜像和AlwaysOn AG

• Bad index management

  不良索引管理

I / O子系统有问题或配置错误 (I/O subsystem has a problem or is misconfigured)

The PAGEIOLATCH_SH could indicate a problem with the I/O subsystem, i.e. problem with the disk. It is often possible that faulty disk does not trigger the monitoring system and in fact the disc issues could be very tricky, as disk could experience various issues which are not black and white (work/doesn’t work). Also the drives that are part of the RAID system could be even more trickier to detect considering the RAID own ability to deal with errors. In such cases, checking the S.M.A.R.T. (Self-Monitoring, Analysis and Reporting Technology) log should be the first step. In situations when the SMART log indicates a possible error or even an imminent drive failure, this could be