tuning huge delete operation on sql server table

Question

I am performing a delete operation on very large sql server table based on query as discussed below.

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 and stg_table_1 doesn't have indexes on service_date.

both of these tables are loaded with million rows of data and delete operation is taking lot of time. Requesting your suggestion to improve the performance of this query.

I referred to strategy described in below question but couldn't understand how to implement it.

How to delete large amount of data in sql server without data loss?

requesting your kind suggestion on this.

Update:

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

how about if go with above logic to delete the millions of records. any advantages and disadvantages with that.

Answer 1

Testing based on your comments

Tested on SQL Server 2014 SP3

stg_table and stg_table_1 doesn't have indexes on service_date.

both of these tables are loaded with million rows of data and delete operation is taking lot of time.

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 + Clustered indexes on identity fields.

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 rows in dbo.st_table_1 and 5M rows in dbo.stg_table (Almost) all of these 2.5M rows will be deleted by the query Which is a more than 10 times less than yours.

Running your query

The actual execution plan for your base delete statement

As expected dbo.stg_table is accessed twice to get the max & min values with a stream aggregate. The cpu time & elapsed / execution time:

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

A missing index hint is added to the execution plan:

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

However, when we add the index, an extra sort appears to delete the rows from this newly added index:

The plan

And the cpu time / elapsed time increases:

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

YMMV, but from my example, based on your comments about data, it did not improve the query.

Creating an index on [dbo].[stg_table]

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

As a result the MAX() and MIN() can leverage the newly created index to return only one row instead of a full clustered index scan:

With the execution time improved:

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

And the execution plan

But this is only based on indexing & my own example. Proceed at your own risk.

---

Extra Notes

You should look into splitting that delete into separate batches so it won't fill up the log file & not having one big block of failed / succeeded delete .

You could also consider using (TABLOCK) so the entire table is locked from the very beginning.

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);

---

Update: 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

how about if go with above logic to delete the millions of records. any advantages and disadvantages with that.

Apart from performance, sp_rename needs a Sch-M lock to complete, meaning that it has to wait for all other sessions to release their locks on the table before it can be modified. Any indexes/constraints on the original table will be gone and you will have to recreate them.

When I run the query on my own data:

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);

This does not represent your data, keep that in mind.

It is reading all rows to return 0 which is not optimal.

With a high execution time:

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

But this is not really meaningful without more information regarding your data. A query plan would be needed to give out more correct advice.

Answer 2

I would simply never delete 37 million rows in one statement. This isn't about the execution plan you get - the overhead of finding rows to delete (whether you have parameter sniffing affect the finding of those rows or not) is far lower than the overhead of actually deleting them and logging those deletes. If you split this into chunks you can amortize that cost over time and process the deletes on a whatever-suits-your-fancy schedule instead of all at once.

-- 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

You might also consider delayed durability if you are on a modern enough version of SQL Server (see this answer and this blog post).

Answer 3

Above query may perform ok because of Missing Index, but query is still wrong.

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

I took above example and executed without Index, it took 18 sec to delete 410792 rows.

If I create Index like above no doubts it will perform best.

1. So no Sub Query in Where condition,it may give High Cardianility Estimate in complex query.
2. Give more importance in writing Optimize query than index.Both are important.

Note :

If performance is bad or worse because of Parameter Sniffing then only you should find suitable way to avoid Parameter sniffing,else you should IGNORE it.

After all not all Store Procedure is written with OPTION RECOMPILE .

As far as I understand ,in my script @FromDate and @Todate are not proc parameter they are local variable,so there is no question of Parameter Sniffing.