Timeline for sql server: updating fields on huge table in small chunks: how to get progress/status?
Current License: CC BY-SA 3.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Apr 13, 2017 at 12:42 | history | edited | CommunityBot |
replaced http://dba.stackexchange.com/ with https://dba.stackexchange.com/
|
|
| Sep 18, 2015 at 1:28 | comment | added | Solomon Rutzky |
@samsmith I am definitely not suggesting to add a new index. I was just explaining again the reason why I generally suggest adding one as the process moves along, but that you had already added one so I am not sure how that current index will affect the situation. But I am still curious about adding the TOP (@UpdateRows) to the UPDATE. I am not sure if it will help, but I can't see it hurting.
|
|
| Sep 18, 2015 at 0:13 | comment | added | Jonesome Reinstate Monica | We are happy with 8 million rows/hour. Yes, we can see it slowing down. We are hesitant to create any more indexes (because the table is locked for the whole build). What we have done a couple of times is do a reorg on the existing index (because that is on line). | |
| Sep 18, 2015 at 0:10 | comment | added | Solomon Rutzky |
@samsmith That's great :) right? Keep in mind two things: 1) The process will slow down as there are fewer and fewer rows matching the WHERE clause, hence why it would be a good time to add a filtered index, but you already added a non-filtered index at the start so I'm not sure if that will help or hurt, but still I would expect the throughput to decrease as it gets closer to being done, and 2) you could increase the throughput by reducing the WAITFOR DELAY to a half-second or so, but that is a trade-off with concurrency and possibly how much is sent over via log-shipping.
|
|
| Sep 17, 2015 at 23:01 | comment | added | Jonesome Reinstate Monica | Without the UPDATE enhancements, we are getting about 8 million updates/hour... with the @BatchRows set to 10000000 (ten million) | |
| Sep 17, 2015 at 20:20 | comment | added | Solomon Rutzky | @samsmith Please see the UPDATE section I just added as there are some ideas for potentially making the process perform even faster. | |
| Sep 17, 2015 at 20:19 | history | edited | Solomon Rutzky | CC BY-SA 3.0 |
Added UPDATE section
|
| S Sep 17, 2015 at 17:13 | history | edited | Solomon Rutzky | CC BY-SA 3.0 |
added a touch of cleanup code to make testing easier
|
| S Sep 17, 2015 at 17:13 | history | suggested | Jonesome Reinstate Monica | CC BY-SA 3.0 |
added a touch of cleanup code...
|
| Sep 17, 2015 at 17:03 | comment | added | Jonesome Reinstate Monica | This is excellent. I am running it now, and it smokes that we can run it on line, during the day. Thank you! | |
| Sep 17, 2015 at 17:03 | vote | accept | Jonesome Reinstate Monica | ||
| Sep 17, 2015 at 17:02 | review | Suggested edits | |||
| S Sep 17, 2015 at 17:13 | |||||
| Sep 17, 2015 at 15:42 | history | edited | Solomon Rutzky | CC BY-SA 3.0 |
added 430 characters in body
|
| Sep 17, 2015 at 14:58 | history | edited | Solomon Rutzky | CC BY-SA 3.0 |
Updated with mechanism to request the process to Pause
|
| Sep 17, 2015 at 6:45 | history | answered | Solomon Rutzky | CC BY-SA 3.0 |