July 20, 2011 at 7:32 am
Hi everyone, this is my first post as I usually can find what I am looking for out on the Interent or within others previous posts.
Recently a discussion was sparked between myself and another dba on the rollback behavior when cancelling a long running sp_updatestats command against a 430 GB database. Basically what has happened is that we found out that re-indexing has been occurring regularly once a week on the weekends which was updating the index statistics, however, no one implemented a general column statistics update which hasn't been run for about a year now. Due to application performance issues we ended up running an sp_updatestats against the entire database with a 20% sampling because we are in the middle of the week (full scan will happen this weekend off hours).
What we are worried about is that if this will still run into normal business hours and we kill it so that it does not degrade application performance, will that end up trying to roll everything back? For instance, we started the update at 4:00 am and it is still running into 6:30 am, 3/4 of the tables in the db have been updated and we can expect another hour and 1/2. If we need to cancel the command based on issues with performance, will the following happen?
1. Will the command just terminate and all statistics that were updated remain that way, and any statistics that were not touched utilize original statistics?
2. Will the command rollback the statistics on just the table that was actively being updated when cancelled?
3. Will the command rollback the 3/4 of completed statistics back to the original which could potentially take another 2 & 1/2 hours to rollback?
We want to be able to definitively say that we should let the command finish the updating of the 1/4 tables that are left if everything were to rollback in terms of time, or that we can kill the actual command as it will stop immediately and not blow away all the statistics that were updated.
If anyone can give any insight on how this works I would appreciate it. Thanks everyone!!
Derek
July 21, 2011 at 4:32 pm
Welcome!
dgossdba (7/20/2011)
1. Will the command just terminate and all statistics that were updated remain that way, and any statistics that were not touched utilize original statistics?
Yes.
2. Will the command rollback the statistics on just the table that was actively being updated when cancelled?
No rollback, just no update because it was cancelled. The stats for the table it's working on are not updated until it's done doing its analysis. When you cancel it, analysis stops.
3. Will the command rollback the 3/4 of completed statistics back to the original which could potentially take another 2 & 1/2 hours to rollback?
No.
Look at the code to sp_update stats for some ideas about what's happening under the covers.
EXEC sys.sp_helptext
@objname = N'sys.sp_updatestats' ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply