Help Please

  • 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.

  • 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"

  • 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

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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?

  • 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