主页 / dba / 问题 / 243432
Accepted
vikrant rana
Asked: 2019-07-23 01:12:05 +0800 CST

调整 sql server 表上的巨大删除操作

  • 772

我正在根据下面讨论的查询对非常大的 sql server 表执行删除操作。

delete db.st_table_1
where service_date between(select min(service_date) from stg_table)
                   and (select max(service_date) from stg_table);

stg_table 和 stg_table_1 在 service_date 上没有索引。

这两个表都加载了数百万行数据，删除操作需要大量时间。请求您提出改进此查询性能的建议。

我提到了下面问题中描述的策略，但不明白如何实施它。

如何在不丢失数据的情况下删除sql server中的大量数据？

请求您对此提出建议。

更新：

select * into db.temp_stg_table_1
from db.stg_table_1
where service_date not between( select min(service_date) from db.stg_table)
                             and (select max(service_date) from db.stg_table);

exec sp_rename 'stg_table_1' , 'stg_table_1_old'

exec sp_rename 'temp_stg_table_1' , 'test_table_1'

drop table stg_table_1_old

如果按照上述逻辑删除数百万条记录如何。任何优点和缺点。

sql-server delete

3 个回答

  1. Best Answer

    根据您的评论进行测试

    在 SQL Server 2014 SP3 上测试

    stg_table 和 stg_table_1 在 service_date 上没有索引。

    这两个表都加载了数百万行数据，删除操作需要大量时间。

    DDL

    CREATE TABLE dbo.st_table_1( stg_table_1_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                             service_date datetime2,
                            val  int)
CREATE TABLE dbo.stg_table (stg_table_ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                            service_date datetime2,
                            val  int)

    PK's + 身份字段的聚集索引。

    DML

    INSERT INTO dbo.stg_table WITH(TABLOCK)
(
service_date,val) 
SELECT -- 1M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(1000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

INSERT INTO dbo.st_table_1 WITH(TABLOCK)
(
service_date,val) 
SELECT -- 2.5M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(2500000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

INSERT INTO dbo.stg_table WITH(TABLOCK)
(
service_date,val) 
SELECT -- 4M
 DATEADD(S,rownum,GETDATE()),rownum
 FROM
 (SELECT TOP(4000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) as rownum
FROM master.dbo.spt_values spt1
CROSS APPLY master.dbo.spt_values spt2) as sptvalues

    2.5M 行dbo.st_table_1和 5M 行dbo.stg_table （几乎）所有这些 2.5M 行都将被查询删除，这比你的少 10 倍以上。

    运行您的查询

    基本删除语句的实际执行计划

    正如预期dbo.stg_table的那样，访问两次以获取流聚合的最大值和最小值。CPU时间和经过/执行时间：

      CPU time = 4906 ms,  elapsed time = 4919 ms.

    执行计划中添加了缺失的索引提示：

    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[st_table_1] ([service_date])
INCLUDE ([stg_table_1_ID])

    但是，当我们添加索引时，会出现额外的排序以从这个新添加的索引中删除行：

    在此处输入图像描述

    计划

    并且cpu时间/经过时间增加：

       CPU time = 11156 ms,  elapsed time = 11332 ms.

    YMMV，但在我的示例中，根据您对数据的评论，它并没有改进查询。

    在上创建索引 [dbo].[stg_table] 

    CREATE NONCLUSTERED INDEX IX_service_date
ON [dbo].[stg_table] ([service_date]);

    因此，MAX()andMIN()可以利用新创建的索引只返回一行而不是完整的聚集索引扫描：

    在此处输入图像描述

    随着执行时间的改进：

     SQL Server Execution Times:
   CPU time = 2609 ms,  elapsed time = 4028 ms.

    以及执行计划

    但这仅基于索引和我自己的示例。继续需要您自担风险。

    额外说明

    您应该考虑将该删除分成单独的批次，这样它就不会填满日志文件并且没有一大块 failed / succeeded delete 。

    您也可以考虑使用(TABLOCK)这样整个表从一开始就被锁定。

    SET STATISTICS IO, TIME ON;
delete dbo.st_table_1 WITH(TABLOCK)
where service_date between(select min(service_date) from stg_table)
                   and (select max(service_date) from stg_table);

    更新：SELECT INTO+sp_rename

    select * into db.temp_stg_table_1
from db.stg_table_1
where service_date not between( select min(service_date) from db.stg_table)
                             and (select max(service_date) from db.stg_table);

exec sp_rename 'stg_table_1' , 'stg_table_1_old'

exec sp_rename 'temp_stg_table_1' , 'test_table_1'

drop table stg_table_1_old

    如果按照上述逻辑删除数百万条记录如何。任何优点和缺点。

    除了性能之外，还sp_rename需要一个Sch-M锁才能完成，这意味着它必须等待所有其他会话释放它们对表的锁才能对其进行修改。原始表上的任何索引/约束都将消失，您将不得不重新创建它们。

    当我对自己的数据运行查询时：

    select * into dbo.temp_stg_table_1
from dbo.st_table_1
where service_date not between( select min(service_date) from dbo.stg_table)
                             and (select max(service_date) from dbo.stg_table);

    这并不代表您的数据，请记住这一点。

    它正在读取所有行以返回 0，这不是最佳的。

    在此处输入图像描述

    执行时间长：

     SQL Server Execution Times:
   CPU time = 27717 ms,  elapsed time = 10657 ms.

    但是，如果没有有关您的数据的更多信息，这并没有真正的意义。需要一个查询计划来给出更正确的建议。

    • 6

  2. 我永远不会在一个语句中删除 3700 万行。这与您获得的执行计划无关 - 查找要删除的行的开销（无论您是否有参数嗅探会影响这些行的查找）远低于实际删除它们并记录这些删除的开销。如果你把它分成几块，你可以随着时间的推移分摊成本，并按照你喜欢的时间表处理删除，而不是一次全部处理。

    -- you can play with these parameters to see what offers the best trade-off
DECLARE @BatchSize int = 10000, @TransactionInterval tinyint = 5;

DECLARE @s datetime, @e datetime, @r int = 1;

SELECT @s = MIN(service_date), @e = MAX(service_date) FROM dbo.stg_table;

BEGIN TRANSACTION;

WHILE (@r > 0)
BEGIN
  IF @r % @TransactionInterval = 1
  BEGIN
    COMMIT TRANSACTION;
    BEGIN TRANSACTION;
  END

  DELETE TOP (@BatchSize) FROM db.st_table_1
    WHERE service_date >= @s AND service_date <= @e;

  SET @r = @@ROWCOUNT;
END

IF @@TRANCOUNT > 0
BEGIN
  COMMIT TRANSACTION;
END

    如果您使用的是足够现代的 SQL Server 版本，您也可能会考虑延迟持久性（请参阅此答案和此博客文章）。

    • 2

  3. 由于缺少索引，上述查询可能执行正常，但查询仍然错误。

    Declare @Fromdate DateTime
Declare @Todate DateTime

select @Fromdate=min(service_date),@Todate=max(service_date) 
from dbo.stg_table

SET STATISTICS IO, TIME ON;
delete dbo.st_table_1 WITH(TABLOCK)
where service_date >=@Fromdate
                  and service_date <=@Todate

    我举了上面的例子并在没有索引的情况下执行，删除 410792 行花了 18 秒。

    如果我像上面那样创建索引，毫无疑问它会表现得最好。

    1. 所以没有条件，它可能会给出Sub Query复杂的查询。WhereHigh Cardianility Estimate
    2. 在写作中给予 Optimize query比更重要index。两者都很重要。

    笔记 ：

    如果性能不好或更差，Parameter Sniffing那么只有你应该找到合适的方法来避免Parameter sniffing，否则你应该忽略它。

    毕竟不是全部Store Procedure都是用OPTION RECOMPILE.

    据我了解，在我的脚本中@FromDate，@Todate它们不是 proc 参数，它们是局部变量，所以没有Parameter Sniffing.

    • 0

相关问题