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?

推荐答案

来自 Microsoft 文档:

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)，或者一个SORTcode> 如果选择了 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屋！