主页 / dba / 问题 / 255215
Accepted
WadeH
Asked: 2019-12-10 11:05:21 +0800 CST

在更新统计信息之前，查询执行计划很糟糕

  • 772

我希望你们能在这里帮助我。我们的应用程序每 3 秒轮询一次消息表以查找要发送的通知。这适用于我们所有的客户（单租户数据库），除了一个。他们一天 23 小时没有活动，然后一次加载数千条消息 (3000+)。在其他情况下，这个卷什么都不是，我们可以轻松处理它，除了在这种情况下，下面的 SQL 查询大约需要 30 秒才能运行，并且随着队列在更新时备份，需要排他锁和因此阻止所有其他查询，因此问题会导致各种破坏。这都是由于一个糟糕的查询计划。

我们每天早上 5 点运行每日重新索引（重组 < 30%，重建 > 30%，忽略 <5%）以及更新统计信息。这些都来自 Ola Hallengren 维护解决方案。我们也在 SQL Server 2016 上并且完全是最新的 (13.0.5492.2)

我手头没有这 2 个计划，但基本上糟糕的计划是对 MessagesSent 表（3.5m 行）进行全表扫描。

我的理论是，因为查询一整天都没有返回任何内容，某些部分没有执行，因此错误查询是 SQL 最有效的查询。

这将在刷新查询计划后继续，因为它只是生成相同的计划，但是当我在 MessagesSent 表上更新统计信息时，会创建好的计划并且一切正常，查询在大约 10-30 毫秒内执行。

有谁知道我如何微调它以始终使用更好的计划，即使查询返回的数据不存在？作为一个修补程序，我们在应用程序中添加了重新编译选项，但我认为这不是每 3 秒执行一次查询的理想解决方案。

这是查询：

WITH TopMessage
    AS
    (
        SELECT TOP 1 ID, BatchID FROM MessagesSent 
        JOIN Units ON Unit = idUnit 
        WHERE   MessageDate <= GETDATE() 
          AND         Active = 'True' 
          AND         Status = 'Queued' 
          AND NOT(DialString = 'null') 
          AND           Unit = ('29') 
          AND System in ('SystemName', 'Q1', '') 
        ORDER BY 
            CASE 
                WHEN QPriority IS NULL 
                    THEN 
                        CASE 
                            WHEN DefaultPriority IS NULL 
                                THEN 999999 
                                ELSE DefaultPriority 
                        END
                 ELSE QPriority 
            END ASC,
            Retries ASC, 
            MessageDate ASC, 
            ID ASC
    ),
    BatchCalls
    AS
    (
        SELECT * FROM MessagesSent 
        WHERE (
                 (LEN(BatchID) > 0 
                  AND BatchID = (SELECT TOP 1 BatchID FROM TopMessage)
                 ) 
        OR ID = (SELECT TOP 1 ID FROM TopMessage)
        )
        AND Status = 'Queued' AND Active = 'True'
    )

    UPDATE BatchCalls
    SET LastUpdated = @dtNow
    OUTPUT INSERTED.*
    WHERE Status = 'Queued'

非常感谢您的时间和寻找。

sql-server sql-server-2016

2 个回答

  1. Best Answer

    您可以使用手动计划指南来执行所需的计划。

    或者，您可以使用Query Store通过 GUI强制执行计划指南。

    此外，您可以在大量消息加载后运行更新统计作业。我在我的博客上写了一篇文章，展示了一种简单的方法。

    • 10

  2. 由于您在这些查询之间有一些共同的过滤，您可以通过在“活动”和“状态”列上添加过滤索引来加快处理速度并减少锁定/阻塞的数量。

    如果没有架构，很难分辨哪些列属于哪些表，但索引看起来像这样：

    CREATE NONCLUSTERED INDEX IX_BatchID_Filtered
ON dbo.MessagesSent (BatchID, Active, Status)
WHERE Active = 'True' AND Status = 'Queued';

    注意：可能有比 BatchID 更好的前导列，并且您可能希望包含表中的其他列（如 DialString、System、QPriority 等） - 我只是不知道哪些列属于哪些表，以及它们的数据是什么类型是等

    该索引将仅包括（希望很小的）符合这些标准的行子集，并在面对有些过时的统计数据时提供更可预测的性能。

    • 6

相关问题