Forum Discussion

nitinshete1975's avatar
nitinshete1975
Copper Contributor
Dec 06, 2024

Delete Statement slowness

 

Hello,

We are running a delete statement on database every weekend where it deletes rows based on the 16 where conditions. It has to pass 16 where conditions before delete more than millions row. 

Since delete statement itself is resource consuming activity and takes time to delete more than a millions of data. 

Is it something these many where conditions causing this issue?  our DB environment is like :-

  • SQL Server 2022 Ent Version with latest patch
  • TempDB files added considering the number of vCPUS on VM, also TempDB is on separate disk
  • NDF Files of secondary filegroups on separate disk for better throughput
  • MaxDOP is set to default 
  • Data and log files are on separate disks. 
  • Weekly DB maintenance plan is in place. 

regards,

Nitin

2 Replies

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Dec 11, 2024

    takes time to delete more than a millions of data. 

    Delete the data batch wise, e.g. always 10K rows instead of all in one run.

    DELETE TOP (10000)
FROM yourTable
WHERE ....

     

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Dec 11, 2024

    Could you share your query plan? like https://www.brentozar.com/pastetheplan/