October 9, 2003 at 9:28 am
I have a two part problem:
1. I tried to run an update query on a tbale with 36 million rows. I removed all indexes and keys thinking it would run faster. 12 hors later my server was bogging, the scheduled job reported failed, yed the spid was still out there. How should I update 36 million rows?
2. I tried to Kill the spid and now it's doing a rollback. I just want it to stop so I can restore my backup and try another stategy. My server sloooow. I'm afraid if I stop SQL and reboot the server it will get stuck recovering.
Help Please!
Cliff
October 9, 2003 at 1:14 pm
quote:
1. I tried to run an update query on a tbale with 36 million rows. I removed all indexes and keys thinking it would run faster. 12 hors later my server was bogging, the scheduled job reported failed, yed the spid was still out there. How should I update 36 million rows?
You need to break the large update into many small batchs. It may still run long hours but you will prevent large transaction log or log full situation. If anytime the update failure, you don't have to update 36 million rows again, those row have been updated don't need to be updated.
quote:
2. I tried to Kill the spid and now it's doing a rollback. I just want it to stop so I can restore my backup and try another stategy. My server sloooow. I'm afraid if I stop SQL and reboot the server it will get stuck recovering.
Kill the update process will cause the rollback that may even take much longer time to complete the rollback. Shuttting down SQL Server service/Power off machine will stop the rollbak, but when you restart SQL Server, the recovery may take long time too. In addition, it may also cause protential database integrity issue by forcing it down. I do not suggest you to do that.
How long the rollback has been running? You may need be a little patient.
October 9, 2003 at 8:33 pm
well - I shut it down and suffered the recovery problem. I batched the updates (looped every 100000 rows) with begin/end transactions and all is right with the world. Live and learn, right. Would it also be a good idea to force it to run on a single processor? I know it can be done but would have to look it up.
Edited by - cliffh on 10/09/2003 8:33:42 PM
Edited by - cliffh on 10/09/2003 8:34:06 PM
October 10, 2003 at 7:14 am
quote:
Would it also be a good idea to force it to run on a single processor?
It depends. Check parallelism in BOL.
October 10, 2003 at 10:00 am
Also, dropping the keys may have been part of your speed problem. Depending on how the update query was written, it is likely that it would need at least the primary key index. (Keep in mind this is hypothetical since I have not seen your SQL query.) Generally you will only want to drop indexes on fields that are being changed by the update -- and in most cases the primary key is not changed.
October 10, 2003 at 12:04 pm
I have also found cursors good here (i.e. date range by weeks, period/month/ year). Use a print statement in the cursor to show progress (which gives me warm fuzzies instead of watching processing time creep on), and fetch next date.
I have tables in the 2-5 million range that join to code tables that can contain upwards of 30K entries for accounting codes. An update statement performed directly on a well indexed table from FK related tables can still take 4-6 hours if it is of this size.
The cursors will run the same updates in 1.5 hours, and keep you current of its progress.
EX: PRINT 'Cursor run for date: ' + @run_date + 'at: ' + CONVERT(CHAR(8),GETDATE(),108)
You can see how long each day takes.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply