主页 / dba / 问题 / 205389
Accepted
Marian
Asked: 2018-05-01 11:36:48 +0800 CST

永无止境的查询商店搜索

  • 772

我会从一开始就说我的问题/问题看起来与前一个问题相似，但由于我不确定原因或起始信息是否相同，所以我决定发布我的问题并提供更多细节。

手头的问题：

  • 在一个奇怪的时间（接近工作日结束），生产实例开始出现异常：
    • 实例的高 CPU（从约 30% 的基线增加到大约两倍，并且仍在增长）
    • 每秒事务数增加（尽管应用程序负载没有任何变化）
    • 空闲会话数增加
    • 从未显示此行为的会话之间发生奇怪的阻塞事件（即使读取未提交的会话也会导致阻塞）
    • top 等待时间间隔的非页面锁存器排在第 1 位，锁排在第 2 位

初步调查：

  • 使用 sp_whoIsActive 我们看到由我们的监控工具执行的查询决定运行速度极慢并占用大量 CPU，这是以前没有发生过的；
  • 它的隔离级别被读取为未提交；
  • 我们查看了我们看到古怪数字的计划：StatementEstRows="3.86846e+010" 大约要返回 150 TB 的估计数据
  • 我们怀疑监控工具的查询监控功能是原因，所以我们禁用了该功能（我们还向我们的供应商开了一张票，以检查他们是否知道任何问题）
  • 从第一个事件开始，它又发生了几次，每次我们终止会话时，一切都会恢复正常；
  • 我们意识到该查询与MS 在 BOL 中用于查询存储监控的查询之一极为相似- 最近性能下降的查询（比较不同的时间点）
  • 我们手动运行相同的查询并看到相同的行为（CPU 使用不断增加，增加闩锁等待，意外锁......等）

有罪查询：

Select qt.query_sql_text, 
    q.query_id, 
    qt.query_text_id, 
    rs1.runtime_stats_id AS runtime_stats_id_1,
    interval_1 = DateAdd(minute, -(DateDiff(minute, getdate(), getutcdate())), rsi1.start_time), 
    p1.plan_id AS plan_1, 
    rs1.avg_duration AS avg_duration_1, 
    rs2.avg_duration AS avg_duration_2,
    p2.plan_id AS plan_2, 
    interval_2 = DateAdd(minute, -(DateDiff(minute, getdate(), getutcdate())), rsi2.start_time), 
    rs2.runtime_stats_id AS runtime_stats_id_2
From sys.query_store_query_text AS qt 
Inner Join sys.query_store_query AS q 
    ON qt.query_text_id = q.query_text_id 
Inner Join sys.query_store_plan AS p1 
    ON q.query_id = p1.query_id 
Inner Join sys.query_store_runtime_stats AS rs1 
    ON p1.plan_id = rs1.plan_id 
Inner Join sys.query_store_runtime_stats_interval AS rsi1 
    ON rsi1.runtime_stats_interval_id = rs1.runtime_stats_interval_id 
 Inner Join sys.query_store_plan AS p2 
    ON q.query_id = p2.query_id 
Inner Join sys.query_store_runtime_stats AS rs2 
    ON p2.plan_id = rs2.plan_id 
Inner Join sys.query_store_runtime_stats_interval AS rsi2 
    ON rsi2.runtime_stats_interval_id = rs2.runtime_stats_interval_id
Where rsi1.start_time > DATEADD(hour, -48, GETUTCDATE()) 
    AND rsi2.start_time > rsi1.start_time 
    AND p1.plan_id <> p2.plan_id
    AND rs2.avg_duration > rs1.avg_duration * 2
Order By q.query_id, rsi1.start_time, rsi2.start_time

设置和信息：

  • Windows Server 2012R2 群集上的 SQL Server 2016 SP1 CU4 Enterprise
  • 查询存储已启用并配置为默认值（未更改设置）
  • 从 SQL 2005 实例导入的数据库（仍处于兼容级别 100）

经验观察：

  • 由于极其古怪的统计信息，我们获取了在错误估计计划中使用的所有 *plan_persist** 对象（还没有实际计划，导致查询从未完成）并检查统计信息，计划中使用的一些索引没有任何统计信息（DBCC SHOWSTATISTICS 没有返回任何内容，从 sys.stats 中选择显示某些索引的 NULL stats_date() 函数

快速而肮脏的解决方案：

  • 手动创建与查询存储相关的系统对象的缺失统计信息或
  • 使用新的 CE (traceflag) 强制查询运行 - 这也将创建/更新必要的统计信息或
  • 将数据库的兼容级别更改为 130（因此默认情况下将使用新的 CE）

所以，我真正的问题是：

为什么查询存储上的查询会导致整个实例出现性能问题？我们是否处于 Query Store 的错误领域？

PS：稍后我会上传一些文件（打印屏幕、IO 统计数据和计划）。

在Dropbox上添加的文件。

计划 1 - 生产中的初始古怪估计计划

计划 2 - 实际计划，旧 CE，在测试环境中（相同的行为，相同的古怪统计数据）

计划 3 - 实际计划，新 CE，在测试环境中

sql-server-2016 statistics

2 个回答

  1. Best Answer

    正如我在答案中所说，经验测试表明 sys.plan_persisted* 系统对象上有索引，而没有在它们之上创建任何（无）统计信息。我怀疑这是因为数据库是从 SQL 2005 实例迁移的，并在兼容级别 100 下保持了一段时间，因此无法使用新的 CE。

    行数检查：

    Select count(1) from NoNameDB.sys.plan_persist_runtime_stats with (nolock) --60362   
Select count(1) from NoNameDB.sys.plan_persist_plan with (nolock) --1853    
Select count(1) from NoNameDB.sys.plan_persist_runtime_stats_interval with (nolock) --671    
Select count(1) from NoNameDB.sys.plan_persist_query with (nolock) --1091    
Select count(1) from NoNameDB.sys.plan_persist_query_text with (nolock) --911

    这表明最初的估计是错误的。完成 DAC 连接，否则无法查询表。

    统计检查：

    DBCC SHOW_STATISTICS ('sys.plan_persist_runtime_stats_interval', plan_persist_runtime_stats_interval_cidx);    
DBCC SHOW_STATISTICS ('sys.plan_persist_runtime_stats', plan_persist_runtime_stats_idx1);    
DBCC SHOW_STATISTICS ('sys.plan_persist_runtime_stats', plan_persist_runtime_stats_cidx);    
DBCC SHOW_STATISTICS ('sys.plan_persist_plan', plan_persist_plan_cidx);    
DBCC SHOW_STATISTICS ('sys.plan_persist_plan', plan_persist_plan_idx1);    
DBCC SHOW_STATISTICS ('sys.plan_persist_query', plan_persist_query_cidx)    
DBCC SHOW_STATISTICS ('sys.plan_persist_query_text', plan_persist_query_text_cidx);

    这表明某些索引的统计信息为空（缺失、无、零）。

    初步修复：

    UPDATE STATISTICS sys.plan_persist_runtime_stats WITH fullscan;
UPDATE STATISTICS sys.plan_persist_plan WITH fullscan;
UPDATE STATISTICS sys.plan_persist_runtime_stats_interval WITH fullscan;
UPDATE STATISTICS sys.plan_persist_query WITH fullscan;
UPDATE STATISTICS sys.plan_persist_query_text WITH fullscan;

    这种修复了统计数据并使查询在 10-12 秒内完成。

    第二个修复：

    （仅在测试环境中验证）并且很可能是正确的，因为它显示了查询的最佳统计信息，是将数据库的兼容性级别更改为 130。最终结果是查询在大约 10-12 秒内结束正常数字统计信息（10k 行）。

    中间修复：

    DBCC TRACEON (2312) -- new CE

    有关系统隐藏表的统计信息的一些相关帮助。

    • 9

  2. 如果您在 SSMS 中打开实际计划并查看 CPU 使用情况（或检查 XML），则可以看到根本问题是节点 32，即 TVF。查询存储速度慢的罪魁祸首是重复访问内存中的 TVF。

    TVF成本

    从这些 TVF 返回多少行并不重要，重要的是它们被访问的次数。修复将是您可以做的任何事情，以使您的计划远离多次阅读。

    基于我有限的调试（技能和时间花费），我的假设是每次 TVF 执行都会扫描分配给查询存储数据的特定内存组件的整个内存。我无法使用sp_query_store_flush_db或来影响此内存分配DBCC FREESYSTEMCACHE。

    到目前为止，成功的解决方法包括计划指南、提示（OPTION(HASH JOIN, LOOP JOIN)到目前为止对我来说已经足够好了），以及在 AG 的只读节点上运行查询存储查询。

    • 5

相关问题