SQL Server删除性能

Question

我在我们的.NET Web应用程序中有一个例行程序，允许平台上的用户清除他们的帐户（即删除所有数据）。这个程序运行在存储过程中，基本上循环遍历相关的数据表并清除他们创建的各种项目。

存储过程大致如下。

ALTER procedure [dbo].[spDeleteAccountData](
    @accountNumber varchar(30) ) 
AS
BEGIN
    SET ANSI_NULLS ON ;
    SET NOCOUNT ON;

    BEGIN TRAN  
    BEGIN TRY
        DELETE FROM myDataTable1 WHERE accountNumber = @accountNumber
        DELETE FROM myDataTable2 WHERE accountNumber = @accountNumber
        DELETE FROM myDataTable3 WHERE accountNumber = @accountNumber
        //Etc.........

    END TRY
    BEGIN CATCH
        //CATCH ERROR
    END CATCH

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION; 
SET ANSI_NULLS OFF;
SET NOCOUNT OFF;
END

问题在于有些情况下，我们的表格可能会有超过10,000行，而该过程可能需要3-5分钟。在此期间，数据库上的所有其他连接都会被限制，导致类似以下错误的超时错误：

System.Data.SqlClient.SqlException (0x80131904)：操作完成之前超时或服务器无响应。

有没有一般性的更改可以提高性能？我知道与我们数据库架构的设计相关的未知因素很多，但是一般的最佳实践建议将受到欢迎！我考虑安排这个任务在凌晨运行以最小化影响，但这远非理想，因为用户在此任务完成之前将无法重新访问其帐户。

附加信息:

SQL Server 2008 R2标准版 所有表格都有聚集索引 没有触发器与任何相关表的删除命令相关联 外键引用存在于多个表上，但删除顺序已经考虑到了这一点。

编辑：16:52 GMT

删除proc影响约20个表格。最大的表格大约有500万条记录。其他表格没有超过200,000条记录，其中一些只包含1000-2000条记录。

- QFDev

---

所有的表都有一个聚集索引，太棒了。但是它在哪一列上？这些表有多少其他索引？它们有多大？你有没有一个回滚点没有显示出来？如果myDataTable2的删除失败，你真的需要回滚到myDataTable1吗？你预计会找到哪些条件会导致操作失败，考虑到你已经按照适当的FK依赖顺序构造了删除语句？ - Aaron Bertrand

每个表都有一个 bigint identity 列作为主键。accountNumber 列没有设置索引，所以我猜这就是我的问题所在！ - QFDev

删除过程会影响到大约 20 张表。最大的表大约有 500 万条记录。其他表格的记录数不超过 20 万，其中一些只包含 1000-2000 条记录。关于回滚的好处是什么！如果一个删除语句失败了，没有必要回滚，但用户的账户会被损坏，所以如果发生这种情况，他们需要被弹出并锁定其账户。回滚选项会带来很大的性能惩罚吗？ - QFDev

如果您在所有删除操作中保持事务处于打开状态，则整个事务的全部时间内所有表都可能被阻塞。因此，有一些选择-我喜欢@marc_s提出的想法，即标记帐户号以进行删除，但直到稍后才实际删除。 “被驱逐”的部分是基于标志而不是基于行的存在/不存在使用的逻辑。您还可以考虑为从这些表中读取的查询使用“读取提交的快照隔离” - 对tempdb性能有影响，但读者不会被删除操作所阻塞。 - Aaron Bertrand

Answer 1

你所有的表中 accountNumber 是否都有索引？

鉴于你使用该列的 WHERE 子句进行删除，这可能会有所帮助。

另一个选择（也许是更好的解决方案）是在晚上安排删除操作，例如，当用户选择删除其帐户时，您只需设置一个标志，然后在晚上运行删除作业来实际删除那些被标记为删除的帐户。

- marc_s

---

我同意索引评论。我只是会将其重构为陈述句而不是问题——例如，“如果您没有索引，那么您应该有一个！” 10K 记录并不算大，所以它花费这么长时间似乎是因为有很多未索引的数据和/或很多约束和 FK 检查正在进行。 - Eli Gassert

谢谢您的建议！这个删除过程很可能只会在账户的生命周期中执行一次，并且通常处理的数据量很小。因此，90%的删除情况可以在3-5秒内执行。问题出在大型账户上，因此我们可以选择性地安排这些情况在非高峰期进行删除。我担心在所有这些表上添加一个额外的“accountNumber”索引会导致更深层次的性能问题。 - QFDev

Answer 2

你可以尝试的一种方法如下：

1. 创建一个SP。
2. 对于每个表，按一定大小的小批量删除行（例如每批10行）。
3. 将每个批量删除操作放在事务内，并在每个事务之间添加自定义延迟。

示例：

    DECLARE @DeletedRowsCount INT = 1, @BatchSize INT = 300;
    WHILE (@DeletedRowsCount> 0) BEGIN
        BEGIN TRANSACTION  
            DELETE TOP (@BatchSize) dbo.Table
            FROM dbo.Table
            WHERE Id = @PortalId;
            SET @DeletedRowsCount = @@ROWCOUNT;
        COMMIT;

        WAITFOR DELAY '00:00:05';
    END

我猜你也可以不用SP来完成同样的事情。实际上，这样可能更好。

- user2173353

Answer 3

如果您在accountNumber字段上有一个索引，那么我猜删除所需的长时间是由锁（由其他进程生成）或受到相应表影响的外键引起的。

1. 如果是由于锁定，则应查看是否可以使用nolock来减少它们。
2. 如果存在外键问题...好吧，你必须等待...如果你不想等待，但你的应用逻辑不依赖于强制执行FK（比如向应用程序发送FK违规错误，并对其进行测试），或者你觉得你的应用程序是完美的，那么在删除之前可以使用ALTER TABLE xxx NOCHECK CONSTRAINT all禁用相关FK，然后重新启用它。

当然，纯粹主义者会责备我后者，但当需要时我经常使用它。

- Dumitrescu Bogdan

---

谢谢您。尽管我在删除的 WHERE 子句上有索引，但我删除了 15k 行中的 100 行却花费了将近 2 分钟，非常缓慢。禁用/启用外键使其在不到一秒钟的时间内运行。 - Vincent V.

Answer 4

将数据库切换到“读取提交的快照”模式可能是值得的。这将对性能产生影响，具体取决于您的应用程序。

在“读取提交的快照”模式下，写入者和读取者不再相互阻塞，尽管写入者仍然会阻塞其他写入者。您没有说明删除操作所阻止的表上的活动类型，因此很难说这是否有所帮助？

http://msdn.microsoft.com/en-us/library/ms188277(v=sql.105).aspx

说了这么多，对于拥有约10k行的表进行删除需要3-5分钟似乎非常慢。您提到了外键，这些外键是否被索引了？如果没有，删除可能会导致在另一端进行表扫描，以确保您不会破坏RI，因此可能首先要检查这个问题。这些删除查询的读/写操作在SQL Server Profiler中显示了什么？

- Meff

---

即使键已经被索引，当存在大量外键时，表的删除也可能非常缓慢。 - Dumitrescu Bogdan

Answer 5

SqlCommand.CommandTimeout是简短的答案。增加它的值。

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx

请注意，连接超时和命令超时不是同一件事情。

...

你的每个表上都有“accountNumber”索引吗？

你可以在表的代理键上拥有聚集键，但不是“accountNumber”。

...

基本上，你需要查看执行计划（或在此处发布执行计划）。

但是这里有一些“入门代码”可以尝试在该列上创建索引。

if exists (select * from dbo.sysindexes where name = N'IX_myDataTable1_accountNumber' and id = object_id(N'[dbo].[myDataTable1]'))
    DROP INDEX [dbo].[myDataTable1].[IX_myDataTable1_accountNumber]
GO

CREATE INDEX [IX_myDataTable1_accountNumber] ON [dbo].[myDataTable1]([accountNumber]) 
GO

- granadaCoder

---

你希望用户在能够使用该应用程序之前等待3-5分钟吗？ - Eli Gassert

这将防止错误，但真正的问题是删除操作需要足够长的时间才能达到超时阈值。将超时时间增加到5或10分钟仍意味着用户将被阻止使用5或10分钟，直到查询完成。 - Aaron Bertrand

不，用户不应该在网页应用上等待3-5分钟。但那是我的主观意见，不一定是他的。 - granadaCoder

我有一个建议。将SqlCommand.CommandTimeout增加到33（或略高于33）秒。因此，可能会有一个地方可以使用我的原始答案。 - granadaCoder

33秒仍然太长了。增加查询的超时时间本来就不应该花费这么长时间，就像给一坨屎穿上燕尾服一样。你的经验可能会有所不同。 - Aaron Bertrand