deletion of 2 million records

Question

deletion of 2 million records

Viewing 15 posts - 16 through 30 (of 30 total)

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

The Natrix SSC Eights! Points: 835 More actions · Answer 1

I read Lynn's article and got a few ideas based on the article and discussions that were done. I like the partition idea, since it seems to be quicker and can be done Live. I'll have to research and familiarize myself with it. I also liked Lynn's suggestion with the 'waitfor delay'.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Partitions are great, but you have to running the Enterprise Edition to use them. So, with that, are you running SQL Server 2005 Enterprise Edition?

Sandy2704 SSCrazy Points: 2156 More actions · Answer 3

No, i am not running Enterprise edition......I am working on Standard edition 🙁

WayneS SSC Guru Points: 95460 More actions · Answer 4

ncodner (3/19/2010)
@WayneS - you're saying if I do the deletion in a loop there will be no locks on the table? Is this better than specifying 'no lock' in the query?

No, I'm saying that if you delete all 2 million rows at once, there will be one big table lock. Deletions still require locks, the looping is to minimize the duration and even possible the extent.

I had forgotten all about Lynn's article... purging the t-log is something you might definitely want to consider doing with deleting 2 million rows.

@paul-2 - what, do you have encyclopedic knowledge of all articles published here? You just keep pulling out all these great links! 😎

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 5

Lynn Pettis (3/19/2010)
Partitions are great, but you have to running the Enterprise Edition to use them. So, with that, are you running SQL Server 2005 Enterprise Edition?

If think ncodner was referring to the idea of partitioning the deletes into batches, rather than table partitioning per se.

I may be wrong :unsure:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 6

WayneS (3/19/2010)
@Paul - what, do you have encyclopedic knowledge of all articles published here? You just keep pulling out all these great links! 😎

Well it is easier than doing the work myself 😛

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 7

Lynn Pettis (3/19/2010)
First, I want to thank Paul for referencing my article and the nice comments regarding it as well.

You are welcome - it is a good article. Funny how this question seems to be coming up a lot again recently.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Paul White NZ (3/19/2010)
Lynn Pettis (3/19/2010)
Partitions are great, but you have to running the Enterprise Edition to use them. So, with that, are you running SQL Server 2005 Enterprise Edition?
If think ncodner was referring to the idea of partitioning the deletes into batches, rather than table partitioning per se.
I may be wrong :unsure:

Hard to say, but I do recall in the discussion on my article that using partitions to switch out records to be deleted was brought up and ncodner did indicate that he had also read the discussion. Based on that, I made the assumation that he meant partitions as in partitioned tables.

I may have been mistaken, myself.

The Natrix SSC Eights! Points: 835 More actions · Answer 9

Lynn Pettis (3/19/2010)
Partitions are great, but you have to running the Enterprise Edition to use them. So, with that, are you running SQL Server 2005 Enterprise Edition?

It turns out we aren't, so that idea is scrapped...

The Natrix SSC Eights! Points: 835 More actions · Answer 10

Lynn Pettis (3/20/2010)
Paul White NZ (3/19/2010)
Lynn Pettis (3/19/2010)
Partitions are great, but you have to running the Enterprise Edition to use them. So, with that, are you running SQL Server 2005 Enterprise Edition?
If think ncodner was referring to the idea of partitioning the deletes into batches, rather than table partitioning per se.
I may be wrong :unsure:
Hard to say, but I do recall in the discussion on my article that using partitions to switch out records to be deleted was brought up and ncodner did indicate that he had also read the discussion. Based on that, I made the assumation that he meant partitions as in partitioned tables.
I may have been mistaken, myself.

I did mean partitions in partitioned tables. But since we don't have enterprise edition I started doing the delete in batches based on Lynn's method posted in her article. So thanks again Paul for the reference to Lynn's article and thanks Lynn for your article 🙂

P.S. I'm female, guess I should change my display name/nick name lol

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Natalie C (3/20/2010)
Lynn Pettis (3/20/2010)
Paul White NZ (3/19/2010)
Lynn Pettis (3/19/2010)
Partitions are great, but you have to running the Enterprise Edition to use them. So, with that, are you running SQL Server 2005 Enterprise Edition?
If think ncodner was referring to the idea of partitioning the deletes into batches, rather than table partitioning per se.
I may be wrong :unsure:
Hard to say, but I do recall in the discussion on my article that using partitions to switch out records to be deleted was brought up and ncodner did indicate that he had also read the discussion. Based on that, I made the assumation that he meant partitions as in partitioned tables.
I may have been mistaken, myself.
I did mean partitions in partitioned tables. But since we don't have enterprise edition I started doing the delete in batches based on Lynn's method posted in her article. So thanks again Paul for the reference to Lynn's article and thanks Lynn for your article 🙂
P.S. I'm female, guess I should change my display name/nick name lol

Doesn't always help, I'm a guy. Most of the time when we use the term "he", it should be taken gender neutral.

Paul White SSC Guru Points: 150467 More actions · Answer 12

Natalie C (3/20/2010)
I did mean partitions in partitioned tables. But since we don't have enterprise edition I started doing the delete in batches based on Lynn's method posted in her article. So thanks again Paul for the reference to Lynn's article and thanks Lynn for your article 🙂

I made that mistake originally too...Lynn is male. I had never come across anyone male called 'Lynn' (as opposed to Lynne) before visiting this site. Maybe the usage is unique to America?

P.S. ...guess I should change my display name/nick name lol

Thanks for explaining - I was confused there for a moment!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

BalasundaramRangasamy SSC Veteran Points: 208 More actions · Answer 13

I have used simple while loop logic while inserting million records into a table. Its a data migration project to move the data from the old database to a newly designed database.

This batch insert really helped a lot while inserting records. I guess similar while loop logic can be written for this deletion too

David-Leibowitz SSChasing Mays Points: 610 More actions · Answer 14

Easiest way? There are several and these work in 2000, 2005, 2008. This has not been tested in 2008 r2.

Method 1:

Involves taking hammer to harddrive

Method 2:

harddrive + lighter fluid + match

method 3:

bucket + battery acid

Same speed of record deletion regardless of recordsize.

SQLScrubs: SSRS Audit, Optimization & Management reports

Paul White SSC Guru Points: 150467 More actions · Answer 15

David-Leibowitz (3/22/2010)
Easiest way? There are several and these work in 2000, 2005, 2008. This has not been tested in 2008 r2.
Method 1:
Involves taking hammer to harddrive
Method 2:
harddrive + lighter fluid + match
method 3:
bucket + battery acid
Same speed of record deletion regardless of recordsize.

Confirmed as effective on R2 :laugh:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi