主页 / dba / 问题 / 175227
Accepted
Nick.McDermaid
Asked: 2017-06-02 17:57:08 +0800 CST

查询变成了吞噬 CPU 的怪物

  • 772

我有一个 SP，他的执行时间在四天内从 5 分钟 > 20 分钟 > 30 分钟 > 53 分钟

等待显示 CPU 增加和挂起状态

我隔离了一个与 CPU 挂钩的查询

UPDATE thing.table
SET YYYYMM = 
CASE 
  WHEN 
    DAY(SnapshotDate) = 1
    OR 
  SnapshotDate = (SELECT MAX(SnapshotDate) FROM thing.table) 
  THEN CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
  ELSE NULL
END

我再次运行它，WITH (RECOMPILE)在最后添加 - 没有区别

我跑了UPDATE STATISTICS thing.table- 没有区别

运行它并获得实际计划会很有趣，但我不想让 CPU 挂一个小时。我检查了sys.dm_exec_cached_plans，但似乎只有估计的计划，没有实际的计划

我重写了 usingCONVERT而不是FORMAT（因为我对新事物持怀疑态度）——没有区别

所以我这样重写并将执行时间缩短到几秒钟：

BEGIN TRAN;

UPDATE thing.table
SET YYYYMM = NULL;

UPDATE thing.table
SET YYYYMM = CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
WHERE 
(
DAY(SnapshotDate) = 1 
OR
SnapshotDate = (SELECT MAX(SnapshotDate) FROM thing.table) 
);

COMMIT TRAN;

该表中有大约 150,000 条记录。很可能它最近有更多的记录被倾倒在其中，扭曲统计数据，但为什么会WITH(RECOMPILE)而UPDATE STATISTICS不是修复它呢？它需要每日快照，并且可能由于月底而增加的记录数量。

所以问题是：

  1. 实际查询计划是否存储在任何地方？以节省我以交互方式运行它？
  2. 通常当查询突然永远耗时时，它会过时统计但这里似乎不是这种情况

这是我的 SQL Server 版本

Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Standard Edition (64-bit) o​​n Windows NT 6.3 (Build 9600: ) (Hypervisor)

以下是慢速和快速查询计划。毫不奇怪，它们是不同的，因为它们在做不同的事情：

慢计划：

慢查询计划

快速计划：

快速查询计划

我注意到 slowpoke 使用循环连接，而 fasty 使用哈希匹配。

我注意到循环连接的小腿有过滤器

[Expr1006]=DB.thing.table.[SnapshotDate]. 也许那不再那么小了？

sql-server sql-server-2014

2 个回答

  1. Best Answer

    第一个查询非常慢，因为它会thing.table对该表中的每一行进行全表扫描DAY(SnapshotDate) <> 1。因此，如果表中有 10 万行，在最坏的情况下，您将进行 10 万次扫描，这意味着读取 100 亿行。如果该表足够小，它将保留在内存中，因此您的并行查询似乎会耗尽 CPU。

    您可以通过仔细查看查询计划来判断。扫描位于嵌套循环连接的内侧。如果那不是您的菜，您可以尝试使用实时查询统计信息来查看查询的执行情况。这样您就可以从实际计划中获取一些信息而无需完成查询。如果不设置扩展事件，就无法节省旧的实际计划。

    第二个查询更快，因为由于缺少CASE表达式，查询优化器可以更自由地重新排列查询的元素。不是MAX(SnapshotDate)每行计算一次子查询，而是每个查询计算一次。

    您肯定希望以某种方式修复此查询，否则执行时间将继续随表中的行数成二次方增长。一种解决方法是向该SnapshotDate列添加索引。子查询仍将对每一行执行一次，但获取最大值将是一个非常便宜的操作。更好的方法是将子查询的值保存到局部变量并在UPDATE查询中使用它。除非你不得不担心不应该成为问题的并发性。

    如果需要，您也可以坚持使用找到的修复程序。在某些情况下（取决于表结构）可以提供帮助的一个建议是在您的第一个中添加一个 where 子句UPDATE：

    UPDATE thing.table
SET YYYYMM = NULL
WHERE YYYYMM IS NOT NULL;
    • 4

  2. 我建议按如下方式中断查询：

    --select MAX(SnapshotDate) only once
DECLARE @MAX_SnapshotDate datetime  --use the appropriate data type
SELECT @MAX_SnapshotDate=MAX(SnapshotDate) FROM thing.table

--Create computed column
ALTER TABLE thing.table
ADD DAY_SnapshotDate as (DAY(SnapshotDate)) PERSISTED

UPDATE thing.table
SET YYYYMM = NULL;

UPDATE thing.table
SET YYYYMM = CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
WHERE 
SnapshotDate = @MAX_SnapshotDate

UPDATE thing.table
SET YYYYMM = CAST(FORMAT(DATEADD(day,-1,snapshotdate),'yyyyMM') AS INT)
WHERE 
DAY_SnapshotDate = 1
    • 0

相关问题