SQL Server中的PAGEIOLATCH_SH等待类型是什么? [英] What is PAGEIOLATCH_SH wait type in SQL Server?

问题描述

我有一个查询，该查询在事务处理中花费很长时间.当我得到进程的wait_type时，它是PAGEIOLATCH_SH.

I have a query that is taking a long time in the middle of a transaction. When I get the wait_type of the process it is PAGEIOLATCH_SH.

此等待类型是什么意思，如何解决?

What does this wait type mean and how can this be resolved?

推荐答案

来自 strong> MSDN :

PAGEIOLATCH_SH

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

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.

实际上，这几乎总是由于对大表的大扫描而发生的.在高效使用索引的查询中几乎不会发生这种情况.

In practice, this almost always happens due to large scans over big tables. It almost never happens in queries that use indexes efficiently.

如果您的查询是这样的:

If your query is like this:

Select * from <table> where <col1> = <value> order by <PrimaryKey>

，请检查您在(col1, col_primary_key)上是否具有复合索引.

, check that you have a composite index on (col1, col_primary_key).

如果没有，则需要完整的INDEX SCAN(如果选择了PRIMARY KEY)，或者需要SORT(如果选择了col1上的索引).

If you don't have one, then you'll need either a full INDEX SCAN if the PRIMARY KEY is chosen, or a SORT if an index on col1 is chosen.

这两个表在大型表上都非常消耗磁盘I/O.

Both of them are very disk I/O consuming operations on large tables.

这篇关于SQL Server中的PAGEIOLATCH_SH等待类型是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！