How to Avoid the performance risk of the Delete Statements

Introduction

Hello 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 :

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 :

• grabbing the ID of the data need to delete it.

SET QUOTED_IDENTIFIER ON;
SET NOCOUNT OFF;
SET XACT_ABORT ON;
SET DEADLOCK_PRIORITY HIGH

DECLARE @email_address NVARCHAR(50)
DECLARE @Recipient NVARCHAR(50)
DECLARE @ERROR_CODE NVARCHAR(400)
DECLARE @ERROR_PROCEDURE NVARCHAR(400)
DECLARE @ERROR_MESSASGE NVARCHAR(400)
DECLARE @TotalMessage NVARCHAR(1200)
DECLARE @count INT

CREATE TABLE #DeleteTemp
(
StudUQID UNIQUEIDENTIFIER
)
CREATE CLUSTERED INDEX #DeleteTemp_Index1 ON #DeleteTemp (StudUQID) WITH (FILLFACTOR =80 , DATA_COMPRESSION=PAGE)

INSERT INTO #DeleteTemp
SELECT StudUQID
FROM hsa_StudentsMaster_Temp WITH ( NOLOCK )
WHERE hsa_StudentsMaster_Temp.SubmissionID = 13003
SET @count = ( SELECT COUNT(1)
FROM #DeleteTemp
)

• Delete statement with patching

WHILE @count > 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION Delete_Statistics_New
MERGE TOP ( 10000 ) hsa_StudentsMaster_Temp T
USING #DeleteTemp S WITH ( NOLOCK )
ON T.StudUQID = S.StudUQID
WHEN MATCHED AND T.SubmissionID = 13003 THEN
DELETE;
COMMIT TRANSACTION Delete_Statistics_New
CHECKPOINT
PRINT ‘10000 Record Is Deleted Now’
PRINT @count
END TRY

• Rollback Transaction and Email notification with the reason.

BEGIN CATCH
IF XACT_STATE() = -1
BEGIN

ROLLBACK TRANSACTION Delete_Statistics_New

SET @ERROR_MESSASGE = ERROR_MESSAGE()
SET @TotalMessage = ‘…..The ERROR_MESSASGE is’
+ @ERROR_MESSASGE

EXECUTE msdb.dbo.sp_notify_operator @profile_name = ‘Proflie Mail’,
@name = N’Operator Name’,
@subject = N’Delete from Statistics_New’,
@body = N’failure on the Delete from hsa_StudentsMaster_Temp Table’

EXECUTE msdb.dbo.sp_notify_operator @profile_name = ‘Proflie Mail’,
@name = N’Operator Name’,
@subject = N’Delete from Statistics_New’,
@body = @TotalMessage

RAISERROR (@TotalMessage,16,1)WITH NOWAIT
END
END CATCH

SET @count = @count – 10000
WAITFOR DELAY ‘000:00:10’
END
—DROP TABLE #DeleteTemp

Follow the author:

View all my tips , LinkedIn , Website , Slideshare  , ABOUT ME