3

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.

  • 2
    Could you add the estimated / actual execution plan to PasteThePlan? Indexing could speed up your query, but larger deletes can take longer. How many rows are you deleting? You should consider deleting in batches – Randi Vertongen Jul 22 at 9:35
  • I don't have access to net on server so may not be able to paste the plan. I have to delete 36994798 records from st_table_1 table. Do I need to create a index on service_date for both the tables. – vikrant rana Jul 22 at 10:03
  • How much data you'd like to retain? If <= 10%, maybe to use TRUNCATE approach. 1-SELECT 10% data INTO temp; 2-Remove FKs;3-TRUNCATE table;4-Recreate FKs;5-INSERT all data in temp back to your table – Dat Nguyen Jul 22 at 10:21
  • @ Randi Vertongen. Thanks for your valuable inputs. I am accepting the answer. what if I delete rows based on below statements. 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 – vikrant rana Jul 22 at 10:27
  • @ Randi Vertongen. could you please also share your thoughts on the updated delete logic. I saw some peoples are deleting this way also. – vikrant rana Jul 23 at 9:02
6

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:

enter image description here

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:

enter image description here

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.

enter image description here

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.

  • Thanks a lot Randi. my delete query is running fine now. – vikrant rana Jul 23 at 12:46
  • @vikrantrana great, good to hear! Good luck :) – Randi Vertongen Jul 23 at 13:40
1

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

0

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.

  • Thank you KumarHarsh. Could you please also share your thought on the approach as mentioned in above comment. 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); – vikrant rana Jul 22 at 12:16
  • 3
    If using parameters, I would add OPTION(RECOMPILE) to the query as to make sql server 'see' the parameters at execution. E.G. delete dbo.st_table_1 WITH(TABLOCK) where service_date >=@Fromdate and service_date <=@Todate OPTION(RECOMPILE). This will grant better estimates at the cost of recompiling the execution plan each time. – Randi Vertongen Jul 22 at 12:16
  • @RandiVertongen, No offence taken and with regard, I was not going so far like parameter sniffing problem etc.Read my edited Note onwards. – KumarHarsh Jul 23 at 3:59
  • 1
    @KumarHarsh No problem :). However, that comment was not only about parameter sniffing. When declaring local variables like that, SQL Server will not know the value inside the variable when running the delete. This means that when creating the query plan an inneficient plan could be chosen instead of a plan for that specific value for @Fromdateand @Todate. More on that here – Randi Vertongen Jul 23 at 6:22
  • 1
    @KumarHarsh this isn't about parameter sniffing. See this post. You answer is in scope, but you're leaving out a pretty big pitfall with doing things the way you're suggesting. – Erik Darling Jul 23 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.