tsql - 为什么我的 Azure SQL 数据库删除查询性能如此之慢？

Question

我有一个大约 9GB 的 Azure Sql 数据库。它提供一个 Web 应用程序，每小时处理大约 135,000 个请求。大多数数据是暂时的，它在数据库中存在几分钟到五天，然后被删除。每天大约有 10GB 的数据通过数据库。

我尝试在表上运行删除查询以删除总共 350,000 条记录中的大约 250,000 条记录。大约 10% 的记录具有一两个足以存储在 LOB 存储中的 nvarchar(max) 值。

上周末，我试图一次将它们全部删除。在我取消查询之前它运行了四个小时，然后它又回滚了 8 个小时 - 糟糕的举动。我真的没想到会这么糟糕。

然后我尝试了另一种方法。当 Web 应用程序每小时处理大约 10 万个请求时，该批次在晚上运行。 tblJobs Id 字段是作为主键的唯一标识符。

insert @tableIds select Id from dbo.tblJobs with(nolock) 
where (datediff(day, SchedDate, getDate()) > 60)  
   or (datediff(day, ModifiedDate, getDate()) > 3 and ToBeRemoved = 1)

set @maintLogStr = 'uspMaintenance [tblJobs] Obsolete J records count @tableIds: ' + convert(nvarchar(12), (select count(1) from @tableIds))
insert dbo.admin_MaintenanceLog(LogEntry) values(@maintLogStr)

set @maintLogId = newid()
set @maintLogStr = 'uspMaintenance [tblJobs] Obsolete J records beginning loop...'
insert dbo.admin_MaintenanceLog(Id, LogEntry) values(@maintLogId, @maintLogStr)

while exists(select * from @tableIds)
begin
    delete @tableIdsTmp
    begin transaction
        insert @tableIdsTmp select top 1000 id from @tableIds
        delete p from @tableIdsTmp i join dbo.tblJobs p on i.id = p.Id
        delete x from @tableIdsTmp t join @tableIds x on t.id = x.id
        set @maintLogStr = 'uspMaintenance [tblJobs] Obsolete J records remaining count @tableIds: ' + convert(nvarchar(12), (select count(1) from @tableIds))
        update dbo.admin_MaintenanceLog set LogEntry = @maintLogStr, RecordCreated = getdate() where Id = @maintLogId
    commit transaction
    if @dowaits = 1 WAITFOR DELAY '00:00:01.000'
end

SchedDate、ModifiedDate 和 ToBeRemoved 未编入索引，因此在 @tableIds 中收集 Id 需要大约 3 分钟 - 不错。

然后从日志条目来看，从 tblJobs 中删除 11,000 条记录需要 1 小时 55 分钟，此时从远程机器调用的作业超时。

为什么需要这么长时间？我能做些什么来加快速度？

Answer 1

您的许多性能将与您使用的预留大小相关(如之前的答案中所述)。但是，您根本不需要在代码中执行表变量来实现您想要的。事实上，当涉及到连接时，您几乎不应该使用它们，因为它们没有关于它们的统计信息(因此当优化器做出复杂的选择时，可能会有糟糕的计划选择)。你可以在这里阅读官方指南:table variables documentation .

所以，如果你退后一步，看看你想要做的事情的核心，你可以这样做:
删除顶部(1000)dbo.TblJobs
其中 (datediff(day, SchedDate, getDate()) > 60)
或 (datediff(day, ModifiedDate, getDate()) > 3 and ToBeRemoved = 1)

您可能会从此查询中获得表扫描，因为:

您正在使用析取 (OR)，这使得优化器很难找到单个访问路径来快速检索您的结果。

您已使用 guid 作为 key (我认为)- 在可能的 guid 空间中有效地随机生成 id

将谓词放在内部函数的输出上使得优化器很难(呃)确定如何对索引进行更智能的扫描，从而可以在列上设置范围。

当您进行扫描时，您可能会遇到锁定问题，因为您有一个在表上并发运行的工作负载。因此，如果某个其他请求正在执行选择语句，您可能会在更新查询扫描表时阻止它。 (发布查询计划对于讨论扩展/并发问题真的很有帮助，顺便说一句)。

此外，鉴于您有一个循环，您从表中取出 1000 行，将它们复制到一个表变量中，然后最终将它们复制到另一个并在删除时与原始表连接，您正在解决一个问题O(N) 变成 O(N^2)。从算法上讲，使用这种方法添加到表中的行越多，您的查询可能会变得越来越慢。

您可以采取一些措施来改进此查询(可能):

完全删除表变量并使用带有@@rowcount 的循环来确定您是否更新了任何内容

从同一个数据库中删除日志(它竞争 IO 而你已经被限制在那里)

将查询谓词拆分为两个查询(其中分离的每个部分都在一个单独的查询中)。如果您碰巧在 scheddate 或 modifieddate 上有一个索引，这会给您一个更好的机会来扫描索引。

我不一定建议在这两个字段上添加索引(因为这样做存在潜在的并发问题)，但是如果您可以安全地这样做而不影响您的生产工作负载，您可以将其作为实验进行尝试。

完成将查询拆分为 2 个查询的更改后，请考虑将 datediff 的计算更改为查询之外 - 计算一次并传入一个值作为参数(col < @param)。如果您有索引，这将使优化器匹配索引

如果您对对象的生命周期有所了解，您可能会切换到使用 newsequentialid 而不是 newid(或只是移动到 bigint)来摆脱创建 id 字段时的随机性。这将减少插入路径上的 b 树碎片，并可能在删除路径中打开更多机会(因为如果您在 id 上有聚集索引并且它们没有被另一个访问，则扫描旧值可能会更容易用户，因为他们很可能正在接触较新的数据)。

您可以使用 readpast 选项跳过被其他用户锁定的行 - 对于这种模式，您会很乐意这样做，除非它们都被锁定，因为您可能会提前结束循环，但如果您定期运行此清理应该没问题。您可以在此处阅读有关该提示的信息:readpast hint docs

了解每个操作的成本有助于大多数性能调整和分析。使用“set statistics time on”和“set statistics io on”为您提供了很好的指标来跟踪查询的物理成本。 “设置统计配置文件”更适合查看每个查询运算符的算法成本(对于 N^2 问题)。

迟到总比不到好，但我希望这可以帮助您(和其他人)了解如果您将来遇到类似情况，如何提高 SQL Azure 性能。