主页 / dba / 问题 / 273353
Accepted
Newbie-DBA
Asked: 2020-08-07 11:30:37 +0800 CST

使用哈希流不同运算符查询调整更新

  • 772

UPDATE需要一些帮助来理解以下语句之一的缓慢性：-

UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc.ENID,abc.Cdate
FROM dbo.table1 xyz WITH (UPDLOCK, READPAST)
INNER JOIN dbo.table2 abc WITH (NOLOCK)
on xyz.id=abc.id
WHERE xyz.flag = 0

表1有大约。50 万行，表 2 大约有 50 万行。500 万行

慢计划

Hash Match distinct flow operator 显示黄色警报，消息为：

操作员使用 Tempdb 溢出数据以执行溢出级别 4 和 1 个溢出线程"

构建残差：

database.dbo.table2.id as abc.id = database.dbo.table2.id as abc.id

我截图了。不幸的是，由于安全原因，我无法提供更多信息，甚至没有匿名计划。从我的工作站我无法访问互联网，所以我无法让计划资源管理器在那里运行。

在此处输入图像描述

通常对于较小的行子集，它低于 sec，就像我们刚刚匹配 10K 行或其他东西一样。但是随着数据量的增加，这似乎是一个临界点，应用程序无法承受 1 分钟的运行时间。从 SSMS 我得到 30 秒，但从应用程序我们有 avg。约 50 秒 RCSI 处于测试阶段。

我的好计划没有显示 Hash Match Flow Distinct 运算符，如我的屏幕截图所示，而其余计划保持不变。好的一个在 3 秒左右完成。正如所见，该运算符花费了近 16 秒。我们可以通过适当的索引或查询重写来消除它吗？

表架构

CREATE TABLE dbo.table1
(
    Recid  VARCHAR(128) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    Cdate DATETIME NULL,
    flag BIT   NULL DEFAULT (0),
    Rcode INT NULL,
    EDR VARCHAR(255) COLLATE SQL_Latin1_general_CP1_CI_AS NULL,
    id BIGINT NULL
);

CREATE TABLE dbo.table2
(
    ENID BIGINT IDENTITY(1,1) NOT NULL,
    EID VARCHAR(50) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    CID VARCHAR(350) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    CDate DATETIME NOT NULL DEFAULT(getdate()),
    id BIGINT NOT NULL,

    CONSTRAINT PK_ENID PRIMARY KEY (ENID ASC, EID ASC),
);

-- table1
CREATE INDEX ix_Cdate on dbo.table1 (Cdate) WITH (FILLFACTOR=100);
CREATE CLUSTERED INDEX ix_Recid on dbo.table1 (Recid) WITH (FILLFACTOR=80);

-- table2
CREATE INDEX ix_ENID_id on dbo.table2 (ENID,id) WITH (FILLFACTOR=100);

变化

我所做的更改和一些数字：

  • 添加提示OPTION (QUERYTRACEON 4138)- 平均。执行比原来的 50 秒减少了 7 秒，但应用程序团队似乎无权在代码中执行此操作。需要进一步检查这一点。

  • OPTION (ORDER GROUP)给出了相同的平均结果。50秒，所以那里没有改善。

  • 按照建议添加索引：

CREATE INDEX i ON dbo.table2 (id) INCLUDE (CID, CDate);

那里没有太大的改进。平均 45 秒，计划与此问题中所附的类似（顶部计划）。

在每次测试之前和之后，我确保计划不是从以前的缓存计划中生成的。

快速计划

对于两个表中相同数量的行，附加更快且数据或查询没有任何更改的计划仍然很快。应用程序团队全天不断提交上述查询，以通过完成前 100 名来完成批次。有一个基于一些小费数字的计划更改，以下是好的计划的外观：

在此处输入图像描述

编辑： - 一切都没有改变，没有代码更改或添加任何索引，正如我尝试添加提示（FORCESEEK）时所建议的那样，它给了我以下错误

由于此查询中定义的提示，查询处理器无法生成查询计划。在不指定任何提示且不使用 SET FORCEPLAN 的情况下重新提交查询。

sql-server query-performance

1 个回答

  1. Best Answer

    你有三个主要问题：

    1. 没有有用的索引来支持加入id。
    2. TOP (100)引入了一个行目标，因此估计可能太低。
    3. UPDATE是非确定性的。

    来自 table2 的多行可以匹配 on id，因此不清楚应该使用来自 table2 的哪个匹配行来为OUTPUT子句提供值。聚合用于对 table2 进行分组id并为其他列选择ANY匹配值。由于行目标，聚合是Flow Distinct。

    需要非常小心ANY非确定性UPDATE语句中的聚合，因为您可能会得到不正确的结果。

    问题中没有足够的细节来提出高质量的建议，但是：

    1. 添加一个索引，如CREATE INDEX i ON dbo.table2 (id) INCLUDE (CID, CDate);
    2. 用于OPTION (QUERYTRACEON 4138)禁用行目标，或OPTION (ORDER GROUP)使用Stream Aggregate而不是 Hash。
    3. 如何修复不确定性UPDATE取决于数据关系。关键点是从源中识别出与每个目标行匹配的最多一行。通常，这将涉及唯一索引或约束，或使用ROW_NUMBERor TOP (1)。

    第 2 步可能需要也可能不需要。我添加它是为了完整性。

    您可能会发现通过以这种形式编写查询更容易可视化问题和调整查询：

    UPDATE TOP (100) 
    xyz WITH (UPDLOCK, READPAST)
SET xyz.flag = 1
OUTPUT 
    inserted.Rcode, inserted.EDR, inserted.id, 
    abc.EID, abc.CID, abc.ENID, abc.Cdate
FROM dbo.table1 AS xyz
CROSS APPLY
(
    -- At most one source row per target row
    SELECT TOP (1) 
        abc.* 
    FROM dbo.table2 AS abc
    WHERE
        abc.id = xyz.id
    -- ORDER BY something to choose the one row
) AS abc
WHERE 
    xyz.flag = 0;

    执行计划：

    预期的计划形状

    我可能不会为 table1 上的过滤索引而烦恼，但如果您确实想尝试一下，这似乎是合适的：

    CREATE INDEX i 
ON dbo.table1 (Recid) 
INCLUDE (id, flag) 
WHERE flag = 0;

    如果您想继续使用问题中给出的更新语法而不正确解决所有潜在问题，您可能会发现这更快：

    UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc.ENID,abc.Cdate
FROM dbo.table1 xyz WITH (UPDLOCK, READPAST)
INNER JOIN dbo.table2 abc WITH (NOLOCK, FORCESEEK)
on xyz.id=abc.id
WHERE xyz.flag = 0;
    • 8

相关问题