October 9, 2007 at 11:34 pm
I am deleting about 2 millions rows from a table using where clauses on primary key (Clustered Index is built on it). Table has about 8 Billion data on it
It is taking for ever to get those data deleted. Can anyone suggest how to understand how many data are deleted? Also a faster way to delete these data?
I can use a where claues on non clustered index also if required
Thanks in advance.
October 9, 2007 at 11:48 pm
Delete rows in batches?
SET ROWCOUNT 20000
WHILE 1 = 1
BEGIN
DELETE FROM Table1 WHERE ID BETWEEN 200000 AND 2200000
IF @@ROWCOUNT = 0 BREAK
END
SET ROWCOUNT 0
N 56°04'39.16"
E 12°55'05.25"
October 9, 2007 at 11:58 pm
Hi Peter
Thanks for reply. Unfortunately query is executing for last 8 hours.. So I understand if we kill it.. it will take equal time to roll back.. Am I correct?
Also, using rowcount will improve the performance? Also is there any way to delete the rows with out logging them or without rebuilding the clustered index? I am kind of ducked here as I am mid of query execution 🙁
Regards
Utsab
October 10, 2007 at 12:17 am
On the same issue.. plz let me know if you think if below work around will work?
The DB is bulk logged recovery mode... so delete is logging it. If I change the recovery to simple now.. LL the current query also stop logging? (The delete query which is executing from last 8 hours)? I guess if this way works.. the issue will be resolved...
Many thanks in advance
Regards
Utsab
October 10, 2007 at 12:34 am
All simple recovery mode does is to automatically truncate the transaction log on a checkpoint. The deletes will still be logged.
If you have any NC indexes, drop them. If you have any foreign keys on this table or referencing this table, drop them. Both can slow deletes down.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2007 at 7:36 am
Seems like you've hit a bottleneck (or several)
What does the delete do? sleep or run? Is it waiting most of the time?
What is it waiting for? What is the CPU load?
What is the load on the server? What is the load on the disks?
Is the db and log on different arrays?
What kind of arrays are they? (RAID level)
How many drives?
Is other work also demanding attention from the drives?
What is the capacity of the disksystem?
...and so on... There's a lot of things to consider.
Also, for each row deleted, the clustered index and all nonclustered index requires an update to reflect the deletion, causing additional overhead, which in turn stresses your drives even more.
/Kenneth
October 10, 2007 at 9:01 am
The server looks good and job is runnable only. (Via sp_who2 active)
Do u think killing the sp id will be a good dicission? Then I will drop all indexes and delete? If I kill SPID, ll it still roll back?
Also, does the index is rebuilt after each row deletion?
October 10, 2007 at 5:07 pm
Utsab Chattopadhyay (10/10/2007)
The server looks good and job is runnable only. (Via sp_who2 active)Do u think killing the sp id will be a good dicission? Then I will drop all indexes and delete? If I kill SPID, ll it still roll back?
Also, does the index is rebuilt after each row deletion?
Yes, if you kill SPID, it still rolls back.
And it will more time to roll back than it took to get to the current point.
You can estimate how far it's gone by looking at allocated space in database files.
Easiest way is to look at Taskpad in EM.
Of course it's gonna be rough estimation, and you need to have an idea about actual size of data in that table. But at least it will give you SOME indication.
I guess next time you'll prepare better for such deletions. 😉
Does it actually stop users from working with data?
If you delete rows by clustered index and users don't access those rows it should not interfere with normal activity. It will just slow it down, I mean slooooow down, but I'm afraid you cannot do anything with it now.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply