How to Avoid the performance risk of the Delete Statements


Introduction

downloadHello everybody in my last post i explained How we can create Clustered index on all heap tables by one single click for video check this link today i will write and speak in new something How we can avoid the performance risk of the Delete Statement, YES delete statement can do big trouble on my server if i have one table with huge volume of data and more transaction hitting this table this meaning this critical table you should be Sensitive  with this critical  databases or this critical objects , because if you need to delete huge data from this table Based on certain criteria AS Example you need to delete 1,000,000 of record.

Problem

At this time when you need to delete 1,000,000 with some where condition and you are deleted from one of the critical table on your server don’t take the wrong way to write one Delete statement direct it will cost more Sync_network_IO and more CXPACKET also probably it can make Schema lock or Query lock .

Solution :

images

Forget the direct delete statement no think here ! you should do the delete statement as patching:

  1. grabbing any unique ID from Target table and insert it into Temp table.
  2. looping on the temp table to return each time the first 1000 record as Example.
  3. using Merge technology to delete the Data exists on target table where the ID equal the ID in source table.
  4. Update the counter of the looping
  5. commit the transaction if it success rollback transaction  if  it fail

by this way the statement of the delete will run smoothly without any bad affect on the SQL Server cluster performance. don’t think in the time you should think in the impact no problem for Query take time without impact because it is better than fast query Executed in 3 SEC but it  fire the CPU or the IO of the server when it run.

Things to consider while working with big tables.

  1. Use Truncate table, if you need to delete all
  2. If you are deleting records more than 70% of data, I would create a temp table, copy only the records you need to this table and run truncate on the original table. This approach is much faster.
  3. Breaking a big transaction into a small transactions applies to Insert and Update as well.
  4. Where possible use table partitioning. This makes the maintenance easy. You can drop a partition if you need to delete it.

DEMO PART :

Continue reading “How to Avoid the performance risk of the Delete Statements”