CODEWITHSUNDEEP
sqlsql-server
Tomas
Tomas

Reputation: 18087

SQL Server Delete query execution timeout

I have Statistic table with about 2 000 000 records and I need to delete record which are older than one month.

While executing DELETE query below I get Time Out error. Can it be related with Indexes? I am not sure where is the problem and how to solve it.

DELETE FROM Statistic WHERE (DateStamp < DATEADD(mm, -1, GETDATE()))

I am also adding execution plan

enter image description here

Image url: https://i.sstatic.net/wfh6k.png

Upvotes: 0

Views: 3095

Answers (3)

Johnny Bones
Johnny Bones

Reputation: 8402

Assuming you have a clustered index (I can't tell if you do), disabling the clustered index will make the whole table inaccessible and you would not be able to run a DELETE on the table anyway. It would fail with:

The query processor is unable to produce a plan because the index ... is disabled.

If you're deleting (or inserting) more than 10% of the table, you should remove all of the non-clustering indexes, delete the records, then rebuild the non-clustering indexes.

If you're deleting less than 10% of the table, leave the indexes in place.

Upvotes: 0

Bernd Linde
Bernd Linde

Reputation: 2152

As indicated in the planned execution plan that you provided, the main time spent is on updating the indexes (preparing the table, sorting the data and re indexing them) after deleting all the data. The delete itself is running quickly.

I would recommend that you have a look at the possibility to drop all the indexes on Statistic (except for IX_DateStamp since that will be used by your delete command and PK_dbo.Statistic since that looks to be your primary key index) before doing the delete and then recreating them after the delete command has finished.

Remember that if you will be dropping the indexes, any query in a production database environment that attempts to retrieve data from Statistic while those indexes are dropped, will be extremely slow. So this might need to be scheduled as part of a maintenance windows.

Upvotes: 1

Matt
Matt

Reputation: 835

You could do something along the lines of:

DELETE 
FROM Statistic 
WHERE PK IN (
            SELECT PK
            FROM Statistic WITH (NOLOCK)
            WHERE (DateStamp < DATEADD(mm, -1, GETDATE()))
            )

Upvotes: 0

Related Questions