Progress of a delete - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Progress of a delete

Author

Topic

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-10-13 : 09:20:07

Hi all,

I'm deleting about 10mil records (using looping, don't worry!) but it's taking time + I want to check progress, if I hit 'Cancel', should that roll back the entire operation, or will it tell me the number of rows already deleted?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-13 : 09:28:27

It depends on what explicit transactions you've set up.

Also you say using looping? Are you set based batches or doing them 1 at an agonizingly slow time?

When you are deleting that many records it often easier to move what you want to keep into another place then TRUNCATE the table. Obv you can't do this with constraints....

post the query you used to do the delete. We can't answer for sure based on what you've said.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-10-13 : 09:31:58

quote:
Originally posted by Jim Beam

Hi all,

I'm deleting about 10mil records (using looping, don't worry!) but it's taking time + I want to check progress, if I hit 'Cancel', should that roll back the entire operation, or will it tell me the number of rows already deleted?

Cheers Charlie,

SET @MINMSGID = @MINMSGID + 20000
WHILE @MINMSGID < @MAXMSGID
BEGIN
DELETE a
FROM DBSrvr2.Queues.dbo.outqueue2 a
WHERE MM = @MonthProcessing AND
YY = @YearProcessing AND
MsgID < @MINMSGID
SET @MINMSGID = @MINMSGID + 20000
END

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-10-13 : 09:45:50

If that's the whole thing then each statement execution should be its own little transaction.

If you cancel then whatever delete batch is currently execution will be rolled back but the batches that have been finalised will not be.

I would suggest adding some output using


SELECT * FROM PendingCommunication
DECLARE @msgText VARCHAR(255)
SET @MINMSGID = @MINMSGID + 20000
WHILE @MINMSGID < @MAXMSGID
BEGIN
	SET @msgText = 'MINMSGID: ' + CAST(@MINMSGID AS VARCHAR(10))
	RAISERROR(@msgText, 0, 1) WITH NOWAIT
	DELETE a
	FROM DBSrvr2.Queues.dbo.outqueue2 a 
	WHERE MM = @MonthProcessing AND 
	YY = @YearProcessing AND 
	MsgID < @MINMSGID
	SET @MINMSGID = @MINMSGID + 20000
END

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-13 : 10:24:33

I just execute this periodically while the delete operation is running


SELECT Rows FROM sys.partitions WHERE Object_Id = Object_Id('myTable');

Kristen
Test

22859 Posts

Posted - 2010-10-13 : 13:33:27

Couple of thoughts (no help with what you have running right now though).

I tend to find that it helps to get the PK of the rows-to-be-deleted into a TEMP table first, with an extra IDENTITY column, and then delete in batches based on IDENTITY ranges (say 10,000 per loop), then join the TEMP table to the Main Table to do the actual DELETE.

Reason is that the WHERE clause on your delete may be taking time at the Query Plan stage, or an inefficient query plan has been chosen, or the query plan becomes more inefficient as more and more data is deleted and indexes become more "sparse" (and statistics become stale, or need to be updated every loop iteration before the query runs) - that may take more time than the DELETE part itself!.

FKeys and Triggers may be taking time too, so worth considering them. We sometimes drop FKeys during the delete and duplicate the FKey logic in the WHERE clause (to make sure we don't delete anything with "children"). Doesn't help with error-checking of any other INSERT/UPDATES occurring during the delete which might contravene the (now disabled) FKeys

I put a WAITFOR for a couple of seconds at the bottom of the loop so that other processes can jump in

And usually we also adjust the number of rows being deleted, per loop, if the execution time of the loop increases over some threshold (and increase the number if the execution time is quick enough). Depends how sophisticated you want it.

Note that if you are only keeping, say, 10% - 20% of the data it is usually quicker to:

Copy rows to be kept to a new table.

Drop original table

Rename New Table back to Original Table Name

(You'll have to deal with any indexes / FKeys etc. on the original table)

Kristen
Test

22859 Posts

Posted - 2010-10-13 : 13:45:36

Here's a worked example:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141065#553313

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources