如何确定SQL Azure中查询占用的IOPS数量

背景信息 #

我们有一个查询间歇性超时，当前仅聚焦于解读现有信息，不做查询优化：

• 超时发生时，通过Azure门户及sys.dm_db_resource_stats观察到Data IO Percentage达到100%。
• 通过sys.dm_user_db_resource_governance存储过程得知，primary_group_max_io为160，即系统IOPS（每秒输入/输出）上限为160。
• 基于sys.query_store_runtime_stats的查询显示，有问题的查询avg_physical_io_reads值为5000，远高于其他查询的0-500范围。
• 使用SSMS的Live Query Statistics，可查看查询计划各节点的「actual number of rows read」。

问题与解答 #

1. avg_physical_io_reads与IOPS有何关联？能否建立关联？ #

avg_physical_io_reads是单条查询执行时平均产生的物理IO读取次数，IOPS是系统每秒能处理的IO操作上限，两者可通过查询执行时长和并发数建立关联：

• 单查询每秒产生的IOPS = avg_physical_io_reads / (查询平均耗时，单位：秒)
• 若该查询有N个并发实例，总IOPS = (单查询每秒IOPS) * N
  当总IOPS接近或超过160时，就会触发IO资源瓶颈，导致查询超时。

2. 如何判断5000的avg_physical_io_reads相对于160 IOPS上限是否过高？ #

不能仅看avg_physical_io_reads的绝对值，核心要关注IO消耗速度和并发情况：

1. 计算单查询IOPS：用avg_physical_io_reads除以查询的avg_duration(ms)（转换为秒）。例如：
   • 若查询平均耗时10秒，5000次读取对应500 IOPS，远超160上限；
   • 若耗时30秒，对应约167 IOPS，接近上限，少量并发就会打满IO。
2. 对比正常查询：avg_physical_io_reads为500的查询运行正常，可能是因为执行时间极短（如0.1秒）但并发低，或执行时间长导致每秒IOPS很低，总消耗未触及上限。
3. 结合并发数：超时时段若该查询有多个实例同时执行，即使单查询IOPS未达上限，叠加后也会耗尽系统IO资源。

3. 「Live Query Statistics」中的读取行数与逻辑/物理IO读取有关联吗？ #

有间接关联，但并非直接对应：

• 「actual number of rows read」是查询计划节点实际读取的行数，逻辑/物理IO以**数据页（通常8KB）**为统计单位。
• 读取行数越多，通常需要访问的数据页越多，逻辑/物理IO次数越高，但比例受数据存储密度（每页行数）、过滤条件、缓存命中率影响。例如：1000行数据若集中在1个数据页，IO次数为1；若分散在100个数据页，IO次数为100。

4. 还有哪些诊断方法可判断特定查询对Azure IOPS上限的影响程度？ #

• 使用sys.dm_exec_query_stats：查看缓存中查询的累计物理/逻辑IO、执行次数、总耗时，计算单查询的平均每秒IOPS。
• Azure Query Performance Insight：直观展示超时时段内各查询的IO资源占比，定位高IO消耗的查询。
• 分析sys.dm_db_wait_stats：若超时期间PAGEIOLATCH_*类等待占比极高，说明查询因IO资源不足等待，确认其对IOPS的消耗。
• 实际执行计划分析：查看计划中的「Actual Physical Reads」「Actual Logical Reads」，结合执行时间计算IOPS消耗，同时检查是否存在表扫描、索引扫描等高IO操作。
• sys.dm_user_wait_stats（Azure SQL专用）：查看特定查询或会话的等待信息，确认是否因IO资源瓶颈导致等待。

用于sys.query_store_runtime_stats的查询语句 #

SELECT
qsrsi.start_time,
qsrsi.end_time,
qsq.query_id,
qsq.query_text_id,
qsp.plan_id,
qsq.last_execution_time,
count_executions,
qsq.count_compiles,
avg_duration/1000 as [Avg_Duration(ms)],
min_duration/1000 as [Min_Duration(ms)],
max_duration/1000 as [Max_Duration(ms)],
avg_logical_io_reads,
min_logical_io_reads,
max_logical_io_reads,
avg_physical_io_reads,
min_physical_io_reads,
max_physical_io_reads,
avg_rowcount,
min_rowcount,
max_rowcount,
avg_num_physical_io_reads,
min_num_physical_io_reads,
max_num_physical_io_reads,
qsqt.query_sql_text,
query_plan,
qsq.query_hash
from sys.query_store_query qsq
INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id
INNER JOIN sys.query_context_settings qcs ON qsq.context_settings_id = qcs.context_settings_id
INNER JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id
INNER JOIN sys.query_store_runtime_stats qsrs ON qsrs.plan_id = qsp.plan_id
INNER JOIN sys.query_store_runtime_stats_interval qsrsi ON qsrs.runtime_stats_interval_id = qsrsi.runtime_stats_interval_id
WHERE qsrsi.start_time >= @starttime and qsrsi.start_time <= @endtime
ORDER BY qsrsi.start_time

内容的提问来源于stack exchange，提问作者JohnB