Can't Update a Bit Column in 227 Million Row Table
I tried last night (after turning of all websites, applications and even SQL jobs that use the DB) to update a bit column to 0 on a table that contains 227 million rows. After 5.5 hours and it being 5:30 AM, I had to give up and rollback the change (which probably took about 3 hours).
Strangely, a week prior I update a DateTime field to '1/1/1900' in the same table without any issue. I don't understand why I'm not able to run the update in a reasonable amount of time. Perhaps I need to delete all the indexes first and then build them back up after? That's a royal pain.
Strangely, a week prior I update a DateTime field to '1/1/1900' in the same table without any issue. I don't understand why I'm not able to run the update in a reasonable amount of time. Perhaps I need to delete all the indexes first and then build them back up after? That's a royal pain.
wdosanjos
Can you post the table / indexes definition including the update statement?
ASKER
FileDisplay (FDID int, CMID int, LMID int, Date datetime, Dateadded Datetime, IsFileDrop bit)
IX_FD primary key, nonclustered, 70% fill
CMID, nonclustered, 70% fill
LMID, nonclusterd, 70% fill
Date nonclustered, 70% fill
Update FileDisplay
Set IsFileDrop = 0
IX_FD primary key, nonclustered, 70% fill
CMID, nonclustered, 70% fill
LMID, nonclusterd, 70% fill
Date nonclustered, 70% fill
Update FileDisplay
Set IsFileDrop = 0
I don't see anything special about your table or update.
Concurrency issues is what comes to my mind. Do you know what other processes were doing at that time? Those processes may have locked rows on the FileDisplay table or stressed the server.
Concurrency issues is what comes to my mind. Do you know what other processes were doing at that time? Those processes may have locked rows on the FileDisplay table or stressed the server.
ASKER
I actually shut down all sql jobs, web sites and other processes that would have been hitting the DB. I was getting a lot of pageiolatch_sh wait times with an Average Latch Wait of 876 ms. I may try a simple reboot of the server and see if anything seems to clear up.
ASKER
either that, or I may have to batch it out somehow. what a pain that would be...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Little confused by code snippet. If I'm only updating the top 200 that are null then why would I need to use any rowcount or while loop. Everyday (or hour, or 30 mins) I could just update top X rows until it's updated.
You should test and change the batch size to a value that works and is NOT impacting your production assuming this is done in production database. As far as how often you could run it even every minute as if it's a sql job next run won't start is ther's one running already.
The code is written like that on purpose - think you have an update less than 200 rows because a value in the top 200 already changed so if you use this batch in a loop it may be broken.
Hope all this makes sense - please let me know if you have more questions.
The code is written like that on purpose - think you have an update less than 200 rows because a value in the top 200 already changed so if you use this batch in a loop it may be broken.
Hope all this makes sense - please let me know if you have more questions.
Oh and I forgot to mention one other thing - hopefully there are no triggers on the table you need to update.