为何使用非聚集索引查找仍等待TRN_PT_TESTS_HEAD聚集索引？

即使执行计划显示使用了TRN_PT_TESTS_HEAD的非聚集索引查找，仍可能等待其聚集索引页，核心原因可归纳为以下几点：

• 非聚集索引依赖聚集索引键
  SQL Server中，非聚集索引的叶子节点默认存储表的聚集索引键（此处为PTH_ID）。你的查询需要通过PTH_ID关联TRN_PT_TESTS_DET表，虽然非聚集索引已包含PTH_ID，但如果该聚集索引页（60:1:1971533）被其他会话持有锁（如排他锁），查询在尝试读取该页上的PTH_ID相关数据时，就会进入等待状态。

• 隐性键查找操作
  若你的非聚集索引仅包含PTH_ENC_ID，即使SELECT列表未直接引用TRN_PT_TESTS_HEAD的其他列，也可能因查询优化器的隐性需求（如数据存在性验证、统计信息过期导致的执行计划偏差）触发键查找——即通过非聚集索引找到PTH_ID后，回表到聚集索引获取额外数据，此时必然会访问聚集索引页。

• 页级锁竞争
  该聚集索引页可能被并发操作占用：

  • 其他会话正在对该页执行INSERT/UPDATE/DELETE，持有排他锁（X锁）；
  • 插入新行导致聚集索引页满，触发页分裂操作，此时该页会被短暂锁定；
  • 大量并发读操作累积的共享锁（S锁），也可能引发等待。

• 执行计划实际执行偏差
  预估执行计划显示使用非聚集索引查找，但实际执行时可能因参数嗅探（如@P0取值范围远超预估，导致查询优化器临时调整执行路径）、统计信息过期等原因，实际执行了需要扫描聚集索引的逻辑，进而访问该锁定页。

验证与优化建议 #

1. 检查并优化非聚集索引：确保针对TRN_PT_TESTS_HEAD的非聚集索引为CREATE NONCLUSTERED INDEX IX_TRN_PT_TESTS_HEAD_PTH_ENC_ID ON TRN_PT_TESTS_HEAD(PTH_ENC_ID) INCLUDE(PTH_ID)，显式包含聚集索引键可避免潜在的键查找；
2. 查询sys.dm_tran_locks查看该页的锁持有情况，定位占用锁的会话及操作；
3. 更新TRN_PT_TESTS_HEAD表的统计信息，避免执行计划偏差；
4. 若为页锁竞争，可考虑启用行版本控制（如READ_COMMITTED_SNAPSHOT）或优化并发写操作的逻辑，降低锁冲突概率。

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