I have one table (30 million records).One of the application uses sp where this table is being used.Now I have a simple requirement where I need to do update on one column .This application is used by the client throughout the day.I would like to learn when and why the best time to do an update on that table.
That is a hard question to answer, and perhaps you are best equipped to answer it. Assuming it is a one-time update that you need to do, what is the time of the day when the users would be least impacted? Nights? Perhaps on the weekends?
Actually, any time is fine if you do the update correctly. Doing all 30 million rows in a single update would be the wrong way to do it especially because every system (due to multiple factors) has a tipping point. For example, on my home desktop (an older box, to be sure), would do a million row update in 2-3 seconds. For argument sake, let's say it was always 2 seconds. I tried 2 million rows and it took the expected 4 seconds because double the rows would be expected to take roughly double the time. For the same reason, it updated 3 million rows in just 6 seconds. Expecting it to take only 8 seconds to run, I did an update on 4 million rows and it took over 2 hours!
I would recommend creating a loop to update may a million or two rows in the same order as the clustered index and then wait for the next logfile backup to occur. That will allow others to use the system and it will clear the guns on the logfile (and, hopefully, you're doing regular Log File "Point-in-Time" backups about ever 15-30 minutes). Doing the updates in the same order as the clustered index means less load on memory and faster performance because the update will occur on contiguous rows on pages doing much less work than in some other order.