首页 文章

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

提问于
浏览 次
7

我有一个大约9GB的Azure Sql数据库 . 它提供一个Web应用程序，每小时处理大约135K请求 . 大多数数据都是短暂的，它会在几分钟到五天内存放在数据库中并被删除 . 每天大约10GB通过数据库移动 .

我试图在表上运行删除查询，从350,000条记录中删除约250,000条记录 . 大约10％的记录有一个或两个nvarchar（max）值足够大，可以存储在LOB存储中 .

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

然后我尝试了另一种方法 . 这个批处理在晚上运行，当时Web应用程序每小时处理大约100K请求 . 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分钟 - 不错 .

然后从日志条目中花费1小时55分钟从tblJobs中删除11,000条记录，此时从远程计算机调用的作业超时 .

为什么需要这么长时间？我该怎么做才能加快速度？

tsql azure azure-sql-database

3 回答

  • 0

    您的许多表现将与您使用的预订大小相关联（如前面的答案中所述） . 但是，您根本不需要在代码中执行表变量来实现您想要的功能 . 实际上，当完全涉及到连接时，你几乎从不使用它们，因为它们没有关于它们的统计信息（因此当优化器有复杂的选择时，可能会有糟糕的计划选择） . 你可以在这里阅读官方指导：table variables documentation .

    所以，如果你退一步看看你想要做的事情的核心，你可以这样做：删除top（1000）dbo.TblJobs where（datediff（day，SchedDate，getDate（））> 60）
    或（datediff（day，ModifiedDate，getDate（））> 3和ToBeRemoved = 1）

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

    • 您正在使用析取（OR），这使得优化器很难找到单个访问路径来快速检索结果 .

    • 你使用guid作为你的密钥（我认为） - 在可能的guids空间中随机有效地生成id

    • 将谓词放在内部函数的输出上会使优化器难以确定如何对索引进行更智能的扫描，因为索引可以在列上设置范围 .

    当您执行扫描时，您可能会遇到锁定问题，因为您的工作负载在表上同时运行 . 因此，如果某个其他请求正在执行select语句，则可能会在扫描表时阻止更新查询 . （发布查询计划确实有助于讨论扩展/并发问题，顺便说一句） .

    此外，假设你有一个循环，你从表中取出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”可以为跟踪查询的物理成本提供良好的指标 . “set statistics profile on”更适合查看每个查询运算符的算法成本（针对该N ^ 2问题） .

    迟到总比没有好，但我希望这可以帮助您（和其他人）了解如何在未来遇到类似情况时如何提高SQL Azure性能 .

    回复于
  • 0

    IT取决于数据库的DTU（性能层） . 在查询执行期间检查数据库的资源消耗，以查看是否达到了任何资源限制 . 此外，将来在发出删除时将查询分解为多个事务 . 这有助于您的事务必须回滚（例如升级到SQL DB）或从您的端连接到db的瞬态网络故障

    回复于
  • 1

    作为一个快速的解决方法/黑客，在SSMS中我右键单击数据库，然后选择Generate Scripts，在高级选项中，我选择创建DROP ONLY脚本 . 从那里把它放在一个新的查询窗口，我做了一个查找和替换，以将 DROP TABLE 更改为 DELETE FROM . 它仍然存在一些问题，即将它们置于错误的外键依赖关系中，但经过一些调整后，我很快就能删除所有表 .

    回复于

相关问题