SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Lynn Pettis / Deleting Large Number of Records / Latest Posts

RE: Deleting Large Number of Records

jts_2003 — Sun, 20 Feb 2011 04:07:27 GMT

A great article - my only concern is that out production database is replicated to two other servers and log shipped to a third, so you couldn't use this process as is without breaking the log chain. But I think I can use the general gist of the process to only delete X rows at a time, by putting the delete code into a SQL Agent job and setting it to run every say 10 minutes e.g. to delete a million rows, you could have a job that deletes 10,000 rows and have it run 100 times.

RE: Deleting Large Number of Records

Indianrock — Sat, 19 Feb 2011 06:45:41 GMT

Great discussion and helpful solutions.1) many of us are a little too touchy about critiques of our code2) tell your management how much disk space you need to run sql server and that it's a cost of doing business. Otherwise tell them to use a pencil and notepad to keep track of data. So many posts about shrinking files, "ballooning" log files etc etc. Just buy the danged disk space and suck it up.3) I don't know if this is a trend, but in our shop backups were turned over to the systems team using Commvault. Any backups done outside of that product could break the log chain. Naturally this severely ties my hands. Also, any change in recovery model will cause Commvault to react according to it's programming, often causing an immediate full backup.4) You "could" go to simple recovery on a production database, but if the company asks you to restore to a point in time, and you can't do it because you stopped transaction log backups, you might be looking for another job.I've actually considered changing modes during our weekend maintenance because Commvault can't keep up and it's log backups are now occasionally finishing many hours apart, rather than the scheduled 15 minute interval. But I'm hoping this will give me the ammunition I need to establish weeknight maintenance windows so it's not all done on the weekend.

RE: Deleting Large Number of Records

dpersson-635827 — Fri, 18 Feb 2011 16:52:38 GMT

This is how I do it with a little bit of output. You can shorten it up by removing the RaisError output message. While (1=1) Begin Delete Top (10000) From [DimProductStyle] From [DimProductStyle] With (nolock) Left Join [Stp_Ods_Live]..[InvProductStyle] With (nolock) On [DimProductStyle].[StyleCode] = [InvProductStyle].[StyleCode] Where [InvProductStyle].[StyleCode] Is Null Declare @RowCountDelete integer; Set @RowCountDelete = @@RowCount If (@RowCountDelete > 0) Raiserror('%i Records Deleted',0,1,@RowCountDelete) With NoWait; Else break; End

RE: Deleting Large Number of Records

TheSQLGuru — Fri, 18 Feb 2011 09:48:19 GMT

d@mmit! I had a nice response typed in and submitted and it isn't showing up! :(Anyway, let me try to recreat.One thing I didn't see mentioned is if the WHERE clause field(s) are NCindexed it can be HUGELY beneficial for both performance AND concurrency to set the batch size (which I usually HARD CODE) such that you get index seeks/bookmark lookups for the DML action at hand. This avoids repeated table scans, which for (very) large tables can be REALLY REALLY bad thing to do for both perf and concurrency. I have used this to great effect many times in the past.

RE: Deleting Large Number of Records

SQLRNNR — Fri, 18 Feb 2011 09:22:01 GMT

Nice one Lynn. Glad to see the re-publish on it

RE: Deleting Large Number of Records

arsrini — Fri, 18 Feb 2011 09:06:17 GMT

Nice article Lynn...Is there a similar code for SQL 7? I have 13 databases which have over 500 million records and I need to delete those that fall in the date range between 01/01/2001 and 31/12/2004. Could you help?

RE: Deleting Large Number of Records

TheSQLGuru — Fri, 18 Feb 2011 08:25:26 GMT

One potentially HUGE performance item I didn't see touched on is if the column(s) in the WHERE clause are indexed you should do some initial testing to adjust batch size such that you get index seeks and bookmark lookups for the DELETE action. On very large tables this can provide a TREMENDOUS increase in both performance (avoids iterative large table scans) AND concurrency (don't lock table).

RE: Deleting Large Number of Records

khalprin — Fri, 18 Feb 2011 07:12:12 GMT

I know just enough about this to be dangerous...so I have a basic question.One of the posters (GSquared) offered this method:[code="sql"]select 1;while @@rowcount > 0delete top (1000)from tablewhere x= y;[/code]Is this simply for deleting in batches so the table remains available, or does this also affect the transaction log file size differently than a single "delete from table where x=y" ?Thanks.

RE: Deleting Large Number of Records

David Data — Fri, 18 Feb 2011 07:04:51 GMT

Is deleting in batches likely to save elapsed time?I frequently have to delete a million+ records from each of several many-million record tables, based on a short indexed field.SS 2008, SIMPLE recovery model, plenty of disk space, and it doesn't matter if the table is locked for the duration.The deletes can add several minutes to my ETL process. Would it be worth experimenting with batched deletes in this case, or would the change probably have little effect on elapsed time?

RE: Deleting Large Number of Records

john.campbell-1020429 — Fri, 18 Feb 2011 06:44:25 GMT

I agree with Ten Centuries on 'keep it simple'. Before I start this kind of operation, I have a backup of either the database or the table that I can restore if needed. I then usually switch the database to simple so I am not making huge transaction log backups. In most production environments there would not be time to track, organize and restore all of them.....Because of the nature of most of these tables, lack of partitioning and other issues, deleting the top 10000 records will usually bring production to a screeching halt because of the locking blocking issues.In most cases, I identify the group of records to delete by their clustered key and copy that clustered key value to a scratch table. I then set up a while loop to select top top xx records from my scratch table into a temp table, delete the records from the production table joined to the temp table by the clustered key, and then delete the records from the scratch table. I can do a count on the scratch table to tell me how many records I have to go at any time.This makes for much more work but the benefits are that you can start your delete record size at a moderate, for your table, size. Monitor the database and server, check for locks and blocks and adjust the size of the individual deletes either up or down depending on the activity on the database and server. An additional benefit is that you can kill the operation at any time and if there is a rollback, it is only a small one, and you can just restart the code when you are ready to run again. In heavily used tables a wait of 1 to 5 seconds at the bottom of the loop is sometimes called for.If production is up and running with no major delays and complaints about poor performance, does it really matter if you take 2 weeks to delete 200 + million records from the sales table.....This is not a job that I walk away from, but monitor rather closely, and will turn off or on depending on the business needs. It's more work for me, but production is running, generating money so that I can be paid :-) and it gets the job done, which is what it is all about anyway...

RE: Deleting Large Number of Records

sjimmo — Fri, 18 Feb 2011 06:41:34 GMT

[quote]Lynn[quote]rob.lobbe (9/15/2009)--------------------------------------------------------------------------------This works if you are the ONLY one using the database.Running in a production system you can't just go about changing the recovery model.as for a 'batch' deleteselect 1while @@rowcount > 0begindelete top () ....endif you are concerned about log growth have ANOTHER process manage it.[/quote]I'm not sure if you are talking about my code or not, but if you are, it will work in production[/quote]I read this article with fascination as I just finished this week deleting 6 years worth of records from a warehouse, with several tables needing 1.4 B records removed. Unfortunately our maintenance window is very small, and full. Deleteing records was a slow tedious process. I created procedures very similiar to what you had written, using batches of 10,000 as well as the rowlock hint. We were able to run this throughout the production day with minimal impact on the users. I checked frequently as obviously they took priority. Actually, without this we would still be working at it. That maintenance window I spoke of was only 4 hrs.

RE: Deleting Large Number of Records

bhart-902952 — Fri, 18 Feb 2011 04:56:38 GMT

Seems a bit overkill. A quick way around having to do the extra leg work is:Setup a variable table **Toss your ID set based on your critiera Setup a while loop to delete blocks of IDs (in thousands) from your main table using you temp table IDs as guide** For super data sets use a temp table and put the appropirate indexes in placeThis approah is quick, easy, doesn't lock the table and can be done in production hours:)

RE: Deleting Large Number of Records

Gabriel P — Fri, 18 Feb 2011 04:49:28 GMT

Hi Lynn, just now reading this article due to the re-publish. If you have a table with a high rate of transactions, what do you think of using the READPAST query hint to reduce locking even further?

RE: Deleting Large Number of Records

Alocyte — Fri, 18 Feb 2011 04:09:24 GMT

Hi guys and girlsI'd like to know why no-ones considered the Delete Cursor...DELETE [ FROM ] { table_name WITH ( < table_hint_limited > [ ...n ] ) | view_name | rowset_function_limited } [ FROM { < table_source > } [ ,...n ] ] [ WHERE { < search_condition > | { [ CURRENT OF { { [ GLOBAL ] cursor_name } | cursor_variable_name } ] } } ] [ OPTION ( < query_hint > [ ,...n ] ) ] Is there no batchability?

RE: Deleting Large Number of Records

mgfyo01 — Fri, 18 Feb 2011 02:09:55 GMT

Hi, why not create an empty table and insert into it only the data that must be kept together with all systems like triggers and so on ? Then either a name swap between both tables and that's it. Of course, there will be a short "blank" moment for users but if it is done together with other maintenance jobs... Best regards. Michel

RE: Deleting Large Number of Records

Renato Buda-153382 — Fri, 25 Sep 2009 07:51:24 GMT

Hi Lynn,Nice article and an interesting discussion.I am wondering if any folks who have been following this discussion can explain or corroborate this phenomenon:Last year I was working on a large(ish) ETL project. I was playing with large transactions and using batching techniques to control the batch sizes. We had SIMPLE recovery mode and single user. I found that there seems to be an optimal batch size beyond which performance degrades. The mystery to me is that performance degrades as batch size increases even [i]when there is plenty of free disk space [/i]for the transaction log. The performance degradation seemed to begin to occur with transaction volumes between about 500Mb and 4Gb. The performance degradation is beyond what would be expected for disk file allocation for the transaction log.

RE: Deleting Large Number of Records

GSquared — Fri, 18 Sep 2009 07:20:25 GMT

[quote][b]Simon-413722 (9/15/2009)[/b][hr]I've been using pretty much the same concept for data deletion since quite some time ago already. It does the same job as Lynn's code, except done a bit differently. I'll post the code anywaywhile 1 = 1begin delete top (???) from tablexxx IF @@Rowcount = 0 break -- (exit from loop or do something)endThe code above doesnt require rowcount to be stored and readable.Simon[/quote]You can also use:[code]select 1;while @@rowcount > 0delete top (1000)from tablewhere x= y;[/code]

RE: Deleting Large Number of Records

GSquared — Fri, 18 Sep 2009 07:19:06 GMT

[quote][b]Ol'SureHand (9/15/2009)[/b][hr][quote][b]rob.lobbe (9/15/2009)[/b][hr]This works if you are the ONLY one using the database.Running in a production system you can't just go about changing the recovery model.[/quote]Not so sure about it.MSDN (http://msdn.microsoft.com/en-us/library/ms190203(SQL.90).aspx) say nothing to imply one cannot change recovery modes in full flight. I did change production DBs recovery modes with no adverse effects... OK, had to as someone creates them in FULL mode but does not set trans log backups...Does anyone have reliable info to the contrary (except for general principles etc)?[/quote]Of course it's possible to change the recovery model in mid-flight. It's also possible to nail your foot to the floor. Doesn't mean either one is a good idea.If a database needs to be in full recovery so that point-in-time restores can be done, changing it to simple recovery in order to expedite a large delete process may not be a wise thing to do. In that case, "can't" doesn't refer to physical possibility, it refers to business needs.

RE: Deleting Large Number of Records

Paul White — Fri, 18 Sep 2009 06:42:33 GMT

So, overall, a good article and another interesting contribution to the SSC archives from Lynn :-)Paul

RE: Deleting Large Number of Records

Paul White — Fri, 18 Sep 2009 06:30:24 GMT

[quote][b]kjs714 (9/15/2009)[/b][hr]I have not run across anything more definitive what is in BOL. I recommend that you setup a test database and run Profiler, looking for the Lock Escalation event to find out when it occurs. I've seen it at strange numbers like 1889, even though the number of rowlocks taken was well below what BOL states. In the context of the article, 10,000 will escalate to a table lock if no other processes have a blocking lock.[/quote][url=http://blogs.msdn.com/sqlserverstorageengine/archive/2006/05/17/Lock-escalation.aspx]SQL Server Storage Engine Team: Lock Escalation[/url]That should clear things up for you.edit: regarding trace flags - the same article also compares TF 1211 with 1224. The latter is often preferred in the infrequent cases where it is required. Taking and holding a conflicting lock in another session can be problematic, especially in the circumstances Lynn describes in his article: the open transaction will prevent the log backup from achieving its goals...

RE: Deleting Large Number of Records

spaghettidba — Fri, 18 Sep 2009 02:07:35 GMT

Sorry for double posting, looks like the site has some issues.It says an error has occurred, but the post is added.

RE: Deleting Large Number of Records

spaghettidba — Fri, 18 Sep 2009 01:46:41 GMT

[quote][b]Tore Bostrup-382308 (9/16/2009)[/b][hr]Interesting article. I took a slightly different approach when needing to delete almost 99.5% of rows from two tables with a total of 200 million rows between them. The tables are log tables used for history and troubleshooting.Because the rows to be kept were much fewer than the ones to be deleted, and because there is no need to log the operation (will take place at night after a full backup and with very low activity), and the criticality of individual rows is minimal, I chose to truncate the table and reinsert the rows to be kept. In my case, I get the "keep" rows from a query inserting into a temp table, but I could get them from a backup, which would also allow minimizing the use of tempdb. After gathering/obtaining these "keep" rows (takes a couple of hours in test), I add any rows that are added since the start of the gathering query (takes about 3 minutes in test), and then I truncate the table, insert the keepers, and reset the Identity value.Originally, I had considered a similar approach using BCP/SSIS, but wound up simply using temp tables, as the amount of data to be kept was manageable in our environment.[/quote]Nice article, Lynn, it's in my briefcase now!:-)Tore Bostrup,I completely agree with this approach. I find myself very often in this situation, that basically applies to log tables.(I have wondered many times in the past if I'm logging too much information...)I think that write-intensive + non business-critical data (==>logs) fits better in a separate database, with simple recovery: it makes everything easier to maintain.

RE: Deleting Large Number of Records

spaghettidba — Fri, 18 Sep 2009 01:35:00 GMT

RE: Deleting Large Number of Records

Lynn Pettis — Wed, 16 Sep 2009 08:38:36 GMT

I agree, how you delete a large number of records is also dependent on how many records are being deleted. In the article I was deleting approximately 10% of the total number of records. I would definately use a different process if the goal was to KEEP only 10% (or in your case much less) of the records.

RE: Deleting Large Number of Records

Tore Bostrup-382308 — Wed, 16 Sep 2009 08:18:29 GMT

Interesting article. I took a slightly different approach when needing to delete almost 99.5% of rows from two tables with a total of 200 million rows between them. The tables are log tables used for history and troubleshooting.Because the rows to be kept were much fewer than the ones to be deleted, and because there is no need to log the operation (will take place at night after a full backup and with very low activity), and the criticality of individual rows is minimal, I chose to truncate the table and reinsert the rows to be kept. In my case, I get the "keep" rows from a query inserting into a temp table, but I could get them from a backup, which would also allow minimizing the use of tempdb. After gathering/obtaining these "keep" rows (takes a couple of hours in test), I add any rows that are added since the start of the gathering query (takes about 3 minutes in test), and then I truncate the table, insert the keepers, and reset the Identity value.Originally, I had considered a similar approach using BCP/SSIS, but wound up simply using temp tables, as the amount of data to be kept was manageable in our environment.

RE: Deleting Large Number of Records

Lynn Pettis — Wed, 16 Sep 2009 05:32:51 GMT

[quote][b]timothyawiseman (9/15/2009)[/b][hr][quote][b]Lynn Pettis (9/15/2009)[/b][hr]I have to make two observations about this article. As of just checking the main page, there have been 7,992 views recorded for this article. Compare this to where it was originally published, 963 views in 90 days. Plus, there has been a nice discussion along with it, where there was none on the other site.I am quite happy! :w00t:[/quote]Out of curiosity, where was it originally published?[/quote]I had originally published this and one other article on sswug.org.

RE: Deleting Large Number of Records

John Campbell-235017 — Wed, 16 Sep 2009 03:31:02 GMT

I guess you could look at setting trace flags (1211 springs to mind) to prevent lock escalation happening at all?But that could create another problem with insufficient memory ....

RE: Deleting Large Number of Records

clementhuge — Wed, 16 Sep 2009 01:37:15 GMT

Just a quick comment:I have to deal with large deletes as well and even with setting up rowlock and limiting the size of my deletes batch, I end up with micro-locks sometimes. It made sense because they are exepensive delete operations, with blob data notably.Having that said, I performed a lot of swith-out partition operations and for very large deletes operations, this is the best solution for performance. Having that said, it is not always that easy but when it is, partitioning is really the best solution (if you have the right version to do it).

RE: Deleting Large Number of Records

timothyawiseman — Tue, 15 Sep 2009 23:29:00 GMT

[quote][b]Lynn Pettis (9/15/2009)[/b][hr]I have to make two observations about this article. As of just checking the main page, there have been 7,992 views recorded for this article. Compare this to where it was originally published, 963 views in 90 days. Plus, there has been a nice discussion along with it, where there was none on the other site.I am quite happy! :w00t:[/quote]Out of curiosity, where was it originally published?

RE: Deleting Large Number of Records

timothyawiseman — Tue, 15 Sep 2009 23:27:00 GMT

[quote][b]Lynn Pettis (9/15/2009)[/b][hr]It is possible that the t-log backup files could over run the available disk space as well. That is another pain point in this process, but it too can be dealt with in a variety of ways. One would be to use a third party backup solution to zip the t-log backups on the fly so that they don't take as much space. In that regard, we use HyperBac on our SQL Server systems.[/quote]Excellent point. We use Red Gate SQL Backup 6 and it works beautifully for that.

RE: Deleting Large Number of Records

mohd.nizamuddin — Tue, 15 Sep 2009 22:27:00 GMT

Thanks LynVery helpful topic.

RE: Deleting Large Number of Records

Simon-413722 — Tue, 15 Sep 2009 22:24:00 GMT

I've been using pretty much the same concept for data deletion since quite some time ago already. It does the same job as Lynn's code, except done a bit differently. I'll post the code anywaywhile 1 = 1begin delete top (???) from tablexxx IF @@Rowcount = 0 break -- (exit from loop or do something)endThe code above doesnt require rowcount to be stored and readable.Simon

RE: Deleting Large Number of Records

Lynn Pettis — Tue, 15 Sep 2009 20:47:34 GMT

[quote][b]kjs714 (9/15/2009)[/b][hr]In the sample, you are using a batchsize of 10,000. This will end up causing a table lock unless the escalation gets blocked by other processes. If table locks must be prevented, batch sizes under 5000 need to be used. If a large batch size is required for decent performance, but the table must not be locked, lock escalation for the table can be disabled (2008), or another spid can be used to hold a special escalation blocking lock (Table IX). For example:BEGIN TRANSACTION;SELECT 1 FROM dbo.MyTable WITH (UPDLOCK HOLDLOCK) WHERE 1 = 0;Once the delete processing is done, the above transaction can be committed. I have researched the above and it does not prevent checkpointing when using the SIMPLE recovery model.One other thing to take into consideration regardling batch sizes is the type of delete plan generated. Depending on the number of indexes and their statistics, a row-by-row type plan or a set based plan may be generated. They should both be evaluated for page counts and durations.[/quote]This is where testing really needs to come into play in each environment. Depending on the tables, the size of the rows, etc it is possible that the locks could escalate and cause blocking of other other processes. depending on how quickly each batch can actually be deleted and the locks released while the T-LOG backup is run may not cause too much of an issue as if trying to delete several million rows in one batch. If a smaller batch size is needed for performance reasons on the table, then go with a smaller batch size.

RE: Deleting Large Number of Records

Lynn Pettis — Tue, 15 Sep 2009 20:39:42 GMT

[quote][b]rob.lobbe (9/15/2009)[/b][hr]This works if you are the ONLY one using the database.Running in a production system you can't just go about changing the recovery model.as for a 'batch' deleteselect 1while @@rowcount > 0 begin delete top () .... endif you are concerned about log growth have ANOTHER process manage it.[/quote]I'm not sure if you are talking about my code or not, but if you are, it will work in production. Also, the code does not change the recovery model of the database. It checks the recovery model since BACKUP LOG can not be run on a database using the SIMPLE recovery model.

RE: Deleting Large Number of Records

Lynn Pettis — Tue, 15 Sep 2009 20:29:12 GMT

[quote][b]John Campbell (9/15/2009)[/b][hr]Lynn ... great article!Any thoughts on how (or even if) this approach should be modified for bulk deletions on a SQL 2005 mirror configuration with log-shipping in place?JC[/quote]Sorry for not responding to this one right away, I really had to think about it. I currently don't work in an environment that uses log shipping so this is a bit more difficult conceptually. I would think that you'd need to modify this code so that it didn't do the backups, and at the same time modify your log shipping process during this time to run more frequently, perhaps as often as every 1 to 2 minutes.Again, I'm sorry I can't geive you a much better answer. Perhaps someone with more experience with log shipping could help out with this particular situation.

RE: Deleting Large Number of Records

kjs714 — Tue, 15 Sep 2009 18:19:58 GMT

[quote][b]Matt Whitfield (9/15/2009)[/b][hr][quote][b]kjs714 (9/15/2009)[/b][hr]WITH (ROWLOCK) is only nominally respected. After a certain number of locks, escalation will be attempted regardless of that hint. The lock escalation article in BOL gives some rough guidelines, but I have seen escalation happen well before the stated lock counts are reached.[/quote]I just deleted 5 million rows from a table using rowlock, and it, indeed, took the mickey - you learn something new every day! 1 exclusive table lock, 12794 exclusive page locks and 1600 exclusive extent locks...Do you know anywhere that has more detail (i.e. that isn't lying) about lock escalation and how lock hints are *actually* applied?Thanks for putting me right on that one :-D[/quote]I have not run across anything more definitive what is in BOL. I recommend that you setup a test database and run Profiler, looking for the Lock Escalation event to find out when it occurs. I've seen it at strange numbers like 1889, even though the number of rowlocks taken was well below what BOL states. In the context of the article, 10,000 will escalate to a table lock if no other processes have a blocking lock.

RE: Deleting Large Number of Records

Matt Whitfield — Tue, 15 Sep 2009 18:04:55 GMT

[quote][b]kjs714 (9/15/2009)[/b][hr]WITH (ROWLOCK) is only nominally respected. After a certain number of locks, escalation will be attempted regardless of that hint. The lock escalation article in BOL gives some rough guidelines, but I have seen escalation happen well before the stated lock counts are reached.[/quote]I just deleted 5 million rows from a table using rowlock, and it, indeed, took the mickey - you learn something new every day! 1 exclusive table lock, 12794 exclusive page locks and 1600 exclusive extent locks...Do you know anywhere that has more detail (i.e. that isn't lying) about lock escalation and how lock hints are *actually* applied?Thanks for putting me right on that one :-D

RE: Deleting Large Number of Records

Ol'SureHand — Tue, 15 Sep 2009 18:01:30 GMT

[quote][b]rob.lobbe (9/15/2009)[/b][hr]This works if you are the ONLY one using the database.Running in a production system you can't just go about changing the recovery model.[/quote]Not so sure about it.MSDN (http://msdn.microsoft.com/en-us/library/ms190203(SQL.90).aspx) say nothing to imply one cannot change recovery modes in full flight. I did change production DBs recovery modes with no adverse effects... OK, had to as someone creates them in FULL mode but does not set trans log backups...Does anyone have reliable info to the contrary (except for general principles etc)?

RE: Deleting Large Number of Records

kjs714 — Tue, 15 Sep 2009 17:46:36 GMT

[quote][b]Matt Whitfield (9/15/2009)[/b][hr][quote][b]kjs714 (9/15/2009)[/b][hr]In the sample, you are using a batchsize of 10,000. This will end up causing a table lock unless the escalation gets blocked by other processes. If table locks must be prevented, batch sizes under 5000 need to be used. If a large batch size is required for decent performance, but the table must not be locked, lock escalation for the table can be disabled (2008), or another spid can be used to hold a special escalation blocking lock (Table IX). For example:BEGIN TRANSACTION;SELECT 1 FROM dbo.MyTable WITH (UPDLOCK HOLDLOCK) WHERE 1 = 0;Once the delete processing is done, the above transaction can be committed.[/quote]Or you could just use WITH (ROWLOCK)...[/quote]WITH (ROWLOCK) is only nominally respected. After a certain number of locks, escalation will be attempted regardless of that hint. The lock escalation article in BOL gives some rough guidelines, but I have seen escalation happen well before the stated lock counts are reached.

RE: Deleting Large Number of Records

Matt Whitfield — Tue, 15 Sep 2009 16:59:52 GMT