Delete Operation on table with 60 Million records

Question

Delete Operation on table with 60 Million records

ArjunaReddy

Default port

Points: 1467
More actions
June 24, 2013 at 12:41 am

#395019

Hi ,
Environment: SQL Server 2008 Enterprise Edition with sufficient hardware.
I have a table with > 60 Million records on which I am performing delete operation using a stored proc. I am deleting the records by batch using TOP clause by joining with a #temp table (with no indexes). I have 1 Cluster Index and 3 non-cluseter index on this main table. Issue is the block itself is taking 95% of the stored proc's total execution. Below is the query that I am using to delete records.
WHILE 1 = 1
BEGIN
DELETE TOP(10000) t
FROM EXPORT.SE_VARCS_DBF t -- Main table with > 60 Million Recs
INNER JOIN #PatchIdentifiers p -- Temp Table
ONt.M_SCNTYPE = p.M_SCNTYPE AND
t.M_FAMILY0 = p.M_FAMILY0 AND
t.M_MLABEL0 = p.M_MLABEL0 AND
t.M_SLABEL0 = p.M_SLABEL0
WHERE t.ContainerID = @TargetContainerID
IF @@ROWCOUNT = 0
BREAK
END
Could you please help me out with the advice.
Thanks,
Arjun.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

ArjunaReddy (6/24/2013)
Hi ,
Environment: SQL Server 2008 Enterprise Edition with sufficient hardware.
I have a table with > 60 Million records on which I am performing delete operation using a stored proc. I am deleting the records by batch using TOP clause by joining with a #temp table (with no indexes). I have 1 Cluster Index and 3 non-cluseter index on this main table. Issue is the block itself is taking 95% of the stored proc's total execution. Below is the query that I am using to delete records.
WHILE 1 = 1
BEGIN
DELETE TOP(10000) t
FROM EXPORT.SE_VARCS_DBF t -- Main table with > 60 Million Recs
INNER JOIN #PatchIdentifiers p -- Temp Table
ONt.M_SCNTYPE = p.M_SCNTYPE AND
t.M_FAMILY0 = p.M_FAMILY0 AND
t.M_MLABEL0 = p.M_MLABEL0 AND
t.M_SLABEL0 = p.M_SLABEL0
WHERE t.ContainerID = @TargetContainerID
IF @@ROWCOUNT = 0
BREAK
END
Could you please help me out with the advice.
Thanks,
Arjun.

Performance issues generally require a lot more information. Have a read through this --> http://www.sqlservercentral.com/articles/SQLServerCentral/66909/%5B/url%5D.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Can you just put the PKs of the main table to be deleted in the temp table, then index it, then join on that.

Also, you need to surround your deletes with a begin/commit tran:

Begin tran

delete ...

commit tran

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ArjunaReddy Default port Points: 1467 More actions · Answer 3

Please find the execution plan for the delete statement. Looking at the plan INDEX delete operation is taking most of the time, Please let me know if there is any way to reduce.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Can you post the actual plan (not the estimated plan) for this batch, please?

-- Determine what is in the patch container

SELECT TOP(1000) DISTINCT M_SCNTYPE, M_FAMILY0, M_MLABEL0, M_SLABEL0, ContainerID = @TargetContainerID

INTO #PatchIdentifiers

FROM Export.SE_VARCS_DBF

WHERE ContainerID = @PatchContainerID

-- Remove patch identifiers from target in batches

DELETE t

FROM Export.SE_VARCS_DBF t

INNER JOIN #PatchIdentifiers p

ONt.M_SCNTYPE = p.M_SCNTYPE AND

t.M_FAMILY0 = p.M_FAMILY0 AND

t.M_MLABEL0 = p.M_MLABEL0 AND

t.M_SLABEL0 = p.M_SLABEL0 AND

t.ContainerID = p.TargetContainerID

Cheers

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Aadhar Joshi Hall of Fame Points: 3202 More actions · Answer 5

I insist to ignore top clause as there are around 60 million records. when u perform top it default sorts data. I insist use between clause that on your identity column that helps may more.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Aadhar Joshi (6/24/2013)
I insist to ignore top clause as there are around 60 million records. when u perform top it default sorts data.

No it doesn't. A TOP just limits the resultset to that number, to get a sort, you need something like an ORDER BY, which isn't valid on a delete

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass