Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Efficient Data Deletion Method and Transactional Databases Expand / Collapse
Author
Message
Posted Monday, December 19, 2011 1:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:48 AM
Points: 30, Visits: 77
What is the efficient method/T-SQL that I can use to delete data from my SQL Server 2008 R2 database.

Currently I am using
DELETE TOP (100) PERCENT FROM Table1
WHERE Id IN
(
SELECT Id FROM DeletionIds
)

As I have to delete data from 20 tables so first I move the choosen Ids into DeletionIds table and then delete all these ids from referenced key tables.

If I am doing something wrong; kindly guide as data deletion process is becoming very critical.

We have disk space limitations and currently cannot increase it.

The daily data insert operations are from 5-7 millions and currently I am only able to delete 2.5-3 million records from database. It means incoming data is more than outgoing data and incoming data is eating my disk space.

Please help to resolve this issue; Thank you.
Post #1224046
Posted Tuesday, December 20, 2011 5:35 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
As a quick suggestion you may modify your code to match with following...

Example:
-- JOIN based deletion
USE AdventureWorks2008R2;
GO
DELETE FROM Sales.SalesPersonQuotaHistory
FROM Sales.SalesPersonQuotaHistory AS spqh
INNER JOIN Sales.SalesPerson AS sp
ON spqh.BusinessEntityID = sp.BusinessEntityID
WHERE sp.SalesYTD > 2500000.00;


Now few question:
• Why TOP (100) PERCENT?
• What’s the purpose of mass deletion? Please explain it. We might be able to suggest better.


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1224354
Posted Tuesday, December 20, 2011 6:01 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
The most efficient way to do mass deletions is to introduce a sliding window partitioned table setup.

This would allow you to switch out whole days of data with out meta data changes and allow you to drop the table you switched the partition into.
Post #1224369
Posted Tuesday, December 20, 2011 7:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, November 21, 2014 1:19 AM
Points: 846, Visits: 1,381
First of all, I agree with Dev on the syntax. So, to your issue. If you have referential integrity, you have to delete from the referencinig tables first, followed by deleting from the referenced (central) table. How simple this may look, you may run into performance issues, most likely with the last delete. When you delete from the central table, referential integrity will be evaluated against all the referencing tables, possibly creating some 20 table or clustered index scans in your execution plan. If so, you may consider creating nonclustered indexes on the column(s) in the referencing tables which is a part of your foreign keys. I hope this helps.

As for space, deleting from tables do generate a lot of transaction log, beacuse SQL Server both logs the deletion and the old data so that the change can be rolled back, and it reserves enough space in the transaction log to be able to roll back. It is not uncommon for me to generate 10GB+ of transaction log for a delete transaction (from some ten tables).




Ole Kristian Velstadbråten Bangås - Virinco - Facebook - Twitter

Concatenating Row Values in Transact-SQL
Post #1224416
Posted Tuesday, December 20, 2011 9:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:48 AM
Points: 30, Visits: 77
I worked with Join previously and found that It takes around 15-20 min to delete 1 million records from 20 tables.
Then I changed the strategy and first pick and insert the ids (primary key) in DeletionIds table and then delete all foreign keys data (19 tables) first and then remove the data from main table and deletion time reduced to 10-13 minutes.

Why TOP (100) PERCENT?
I just do some R & D on web and try to find quick way to delete data from database. With this I found a 20-30% improvement in deletion process as compared to DELETE FROM Table1.

Mass Deletion?
1. The main reason is that my client's requirement is that I have to delete the data from database older than specific retention period (say 30 days)
2. Also we have a limited disk space that we want to use for new/latest data and new client for the application.

@MysteryJimbo; I will look into "sliding window partitioned table setup". Does this support all SQL 2008 R2 versions? I am using Standard version of SQL Server.

Thanks
Post #1224578
Posted Tuesday, December 20, 2011 9:52 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:53 PM
Points: 1,932, Visits: 1,589
Babar Javaid (12/20/2011)
I worked with Join previously and found that It takes around 15-20 min to delete 1 million records from 20 tables.
Then I changed the strategy and first pick and insert the ids (primary key) in DeletionIds table and then delete all foreign keys data (19 tables) first and then remove the data from main table and deletion time reduced to 10-13 minutes.

Why TOP (100) PERCENT?
I just do some R & D on web and try to find quick way to delete data from database. With this I found a 20-30% improvement in deletion process as compared to DELETE FROM Table1.

Mass Deletion?
1. The main reason is that my client's requirement is that I have to delete the data from database older than specific retention period (say 30 days)
2. Also we have a limited disk space that we want to use for new/latest data and new client for the application.

@MysteryJimbo; I will look into "sliding window partitioned table setup". Does this support all SQL 2008 R2 versions? I am using Standard version of SQL Server.

Thanks


Based on your reply, 'sliding window' is a good idea. Yes, SQL Server 2008 R2 supports it.


Dev

Devendra Shirbad | BIG Data Architect / DBA | Ex-Microsoft CSS (SQL 3T)
*** Open Network for Database Professionals ***

LinkedIn: http://www.linkedin.com/in/devendrashirbad
Post #1224596
Posted Monday, December 26, 2011 12:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:11 AM
Points: 2,840, Visits: 3,976

Babar Javaid (12/20/2011)
1. The main reason is that my client's requirement is that I have to delete the data from database older than specific retention period (say 30 days)

IF this would be a continuous periodic job (say every month) i would recommend Batch approach in off peak hours, by this you can also manage you log.

Babar Javaid (12/20/2011)
2. Also we have a limited disk space that we want to use for new/latest data and new client for the application.
rather deleting the data , i would say move the required data (latest) data into permanent tmp table (prefer new disk if you can or if you require) , rename existing table into some history table, and rename tmp into main table.in this way you can also put less overhead (log's space wise) on main table.and less data to operate.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1226623
Posted Wednesday, November 7, 2012 2:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 2:48 PM
Points: 3, Visits: 21
Deleting as fast as possible:
Two suggestions 1) log(ldf) and data(mdf) files on physically separate disks.
2) Partition the table so that records to be deleted are distributed among several spindles
if possible, partition the table so that incoming data and to_be_deleted data resides on seperate partition. if you can do that just delete the partition.
Post #1382179
Posted Thursday, November 8, 2012 11:44 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:11 AM
Points: 2,840, Visits: 3,976
apatel62 (11/7/2012)
Deleting as fast as possible:
Two suggestions 1) log(ldf) and data(mdf) files on physically separate disks.
2) Partition the table so that records to be deleted are distributed among several spindles
if possible, partition the table so that incoming data and to_be_deleted data resides on seperate partition. if you can do that just delete the partition.
+1


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1382834
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse