Tuning of a delete query causing slowness

Question

Dear Experts, There is a delete statement which is causing considerable slowness on the server and it runs after hours. Please provide recommendations to tune this query. The primary key is there on this HostNTEvent_TimeGenerated column.

delete  FROM [MyDB].[dbo].[tbl_HostNTEvent] 
where [HostNTEvent_TimeGenerated] < dateadd(day,-30,getdate())
and HostNTEvent_EventCode not in(4407, 4003)

The select statement below returns around 10 millions rows.

select count(*)  from  [MyDB].[dbo].[tbl_HostNTEvent] 
where [HostNTEvent_TimeGenerated] < dateadd(day,-30,getdate())
and HostNTEvent_EventCode not in(4407, 4003)

Thanks,

Answer 1

There is not much you could optimize, it takes some time to delete data, which always cause an update in existing indexes as well. You could delete the data batch wise, let's say alway 10,000 records per batch =>

delete TOP (10000)  FROM [MyDB].[dbo].[tbl_HostNTEvent] 
where [HostNTEvent_TimeGenerated] < dateadd(day,-30,getdate())
and HostNTEvent_EventCode not in(4407, 4003)
GO 1000

Answer 2

Hi @mo boy

Please check if the performance is poor due to physical I/O.

Check if exists deadlocks or blocking.

If exists cascade delete or triggers.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.