长时间运行的Azure SQL查询在一段时间后挂起 [英] Long running azure SQL query hangs after a time

问题描述

因此，我正在对复制的数据库进行一些实验(以免使我们的服务崩溃).

我正在尝试开发一个维护例程，该例程将从具有数百万个条目的数据库表中删除多余的行.

我们尝试执行删除前(n)个WHERE"类型的查询，但该查询导致DTU激增，该查询最终陷入僵局.

其他人似乎建议创建一个重复表，复制我们要保留的行，并截断旧表.

这似乎有所帮助，但是DTU飙升至100％，然后查询停顿而没有错误(只是无休止的挂起).通过查询，我们获得了约71％的支持，但是它只是停止了工作.有没有一种方法可以确保Azure SQL数据库将完成工作 在这里吗?

So I'm doing some experimentation on a replicated database (so as to not crash our services).

I'm trying to develop a maintenance routine that deletes superfluous rows from a database table with several million entries.

We tried doing a 'Delete Top (n) WHERE' type query, but that one caused DTUs to spike, and the query stalled out eventually.

Other people seem to recommend creating a duplicate table, copying the rows we want to keep over, and truncating the old table.

This seems to help, but the DTUs spike to 100%, and then the query stalls out with no error (just an endless hang). We got ~71% through the query, and it simply stopped working. Is there a way I can ensure that the Azure SQL database will complete the work here?

推荐答案

你好，

Hello,

Could you please try the batching techniques explained on the following article?

https://docs.microsoft.com/zh-CN/azure/sql-database/sql-database-use-batching-to-provprove-performance

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-use-batching-to-improve-performance

Regards,

Alberto Morillo
SQLCoffee.com

这篇关于长时间运行的Azure SQL查询在一段时间后挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！