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 «««1234»»

Deleting large number of rows from a table & a heap in a VLDB Expand / Collapse
Author
Message
Posted Friday, March 25, 2011 12:26 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:17 PM
Points: 1,381, Visits: 1,774
Hello!

Thank-you all for your interest, and valuable feedback.

Some of the points mentioned in the feedback are really great. I will research them and update the article as necessary.

However, for some suggestions (filtered indexes, using partitioned tables, etc) are all great in hindsight. As mentioned, this is based upon a live example, and the schema was in place and no changes were allowed (at least in the case of the heap). That being said, I agree that if a purging solution is being designed from scratch, yes, these are some of the features that absolutely should be used.


Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #1084286
Posted Friday, March 25, 2011 2:01 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:50 AM
Points: 2,163, Visits: 2,184
Thanks for the article, and the discussion, there is a lot of good information here.
Post #1084342
Posted Tuesday, March 29, 2011 2:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 10:36 AM
Points: 5, Visits: 97
I liked the article. I'm a bit confused by your numbers though. You said make note of the original data and log file sizes and then show the before and after file sizes after the delete was performed. There's a significant difference in the original file sizes to the before and after file sizes. I'm not sure what's happening there. Can you give me a little more explanation on those?
Post #1085855
Posted Wednesday, March 30, 2011 7:51 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:17 PM
Points: 1,381, Visits: 1,774
Read It (3/29/2011)
I liked the article. I'm a bit confused by your numbers though. You said make note of the original data and log file sizes and then show the before and after file sizes after the delete was performed. There's a significant difference in the original file sizes to the before and after file sizes. I'm not sure what's happening there. Can you give me a little more explanation on those?


Hello!

Good to know that you liked reading my article. I can definitely help you out in understanding the difference in the file sizes. Please find the explaination below:

Case #1 - Deleting Random Data from a table
Initially, we generated our test data, and noted the file sizes. The data and log files came out to 2694MB and 1705MB respectively.
Next, we generated the lookup table, and then executed the purge. The "Before" and "After" values are with respect to the Purge operation, and hence contain the space occupied by the lookup table.

The above also applies to Case #2 - Deleting data from a heap (non-clustered table).

The basic point I was trying to make is that the file sizes remain constant during the purge operation, and hence I have taken the file size measurements accordingly.

Do let me know if you still have any doubts, and I will be more than happy to help you out.


Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #1086221
Posted Wednesday, March 30, 2011 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 24, 2013 10:36 AM
Points: 5, Visits: 97
I thought that might be the case, but didn't want to assume that. Thank you for the help.
Post #1086259
Posted Sunday, April 03, 2011 4:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 26, 2011 5:32 AM
Points: 2, Visits: 11
First of all, great article and very insightful so thanks for taking the time to write it. I do have one question about the approach you explained for deleting large quantities of data from tables with clustered index -

Assuming I understood correctly, you are saying that on a daily basis (or some periodicity), move the data you would like deleted into a look-up table. Then, once a week, join the look-up table to the table that data needs to be deleted from and perform delete. And subsequently truncate the lookup table or something. So my question is - in your implementation of this, have you encountered any blocks on that big table that the data needs to be deleted from during the join? What if that big table is frequently being used by other processes? I was planning to try this out and I am somewhat of a novice so I thought I'd check before breaking anything :)

Also, out of curiosity, I noticed that the look-up table that you created for deleting data from a clustered-index table itself had no clustered index, simply a non-clustered index on the column "UnixDateToDelete". Was this done for a specific reason?

Thanks again for the article!
Post #1087882
Posted Monday, April 04, 2011 12:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 10:17 PM
Points: 1,381, Visits: 1,774
APP_SQL (4/3/2011)
First of all, great article and very insightful so thanks for taking the time to write it. I do have one question about the approach you explained for deleting large quantities of data from tables with clustered index -

Assuming I understood correctly, you are saying that on a daily basis (or some periodicity), move the data you would like deleted into a look-up table. Then, once a week, join the look-up table to the table that data needs to be deleted from and perform delete. And subsequently truncate the lookup table or something. So my question is - in your implementation of this, have you encountered any blocks on that big table that the data needs to be deleted from during the join? What if that big table is frequently being used by other processes? I was planning to try this out and I am somewhat of a novice so I thought I'd check before breaking anything :)

Also, out of curiosity, I noticed that the look-up table that you created for deleting data from a clustered-index table itself had no clustered index, simply a non-clustered index on the column "UnixDateToDelete". Was this done for a specific reason?

Thanks again for the article!


Hello, APP_SQL!

Thank-you for your feedback, and I am happy that you liked reading my article.

As far as the concept goes, yes, you have understood correctly. Now, about your questions:

Q1. Whether or not I have encountered any blocks on the tables during the periodic purge cycles?
A1. Our is an on-premise system, and hence, we execute the purge during the weekly IT maintenance window provided to us by the customer (it's a configurable SQL job that does the purge). The window typically varies from 2 to 4 hours, and hence we have to be in and out of the system in about an hour for IT to do the rest of their maintenace. Because they happen during the maintenance window, all interfaces are down and hence, we have not had any blocking issues.
There have been cases where we had to execute the purge online, and even then we did not face any major blocking issues.

As an alternative, you may want to partition your table, and set the lock escalation to AUTO. What this will do is ask SQL Server to escalate locks to the partition, and not to the entire table.

Q2. Why did I use non-clustered index on the lookup table as opposed to a clustered index?
A2. No specific reason as such. Generally speaking, you can use a clustered index on the lookup table as well - no harm at all (in fact, your deletes may be even faster). In our case, we did not want to enforce any constraints or establish any relationships with the lookup table, and hence you will see that no PK-FK has been used. If your design is such that you can allow for a relationship to exist, please go ahead and use the clustered index by creating the "UnixDateToDelete" as Primary Key.

I hope that I was able to answer your questions satisfactorily. If there is anything else I can help you out with, do let me know.


Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #1087916
Posted Monday, April 04, 2011 8:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 26, 2011 5:32 AM
Points: 2, Visits: 11
Thanks Nakul! You have answered all my questions. Thanks for the response and once again, thanks for the article.
Post #1088067
Posted Monday, April 04, 2011 10:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, March 23, 2014 1:28 AM
Points: 22, Visits: 292
A great article and constructive discussion!
I do learn a lot of brilliant methods to delete very large table, but not just simply to make a where clause and wait for it to complete.
Post #1088170
Posted Sunday, March 17, 2013 8:40 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 2:42 PM
Points: 1,851, Visits: 3,575
I really liked the lookup-table technique for deleting data from a clustered index.
Will definitely suggest that to our dev teams going forward.
Thank you for the article!


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1431987
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse