Late last night, I executed a 70mm update on a replicated table. This was a somewhat low priority update, so the primary goal (aside from the data change) was to avoid any issues on the publishing and subscribing servers, including replication latency errors. I have performed many large updates in the past, but this was the first on a replicated table.
To minimize impact, one of our system DBA’s suggested the use of a replicated stored procedure. Basically, instead of replicating each of the 70mm updates as a singleton transaction, the replicated stored procedure is called on the subscribing server, which then performs the bulk update locally. This was my first time using it and it worked beautifully.
Another of my colleagues, Jeff M., suggested the use of a control table. Normally, I would just output the last affected ID and update the script manually. However, this also worked so well that it will be adopted in all future update scripts.
Using the following pseudo-script and a replicated stored procedure, I was able to execute the update on 70mm records in 11.5 hours with *zero* impact on the servers and replication. I could’ve probably increased the batch size and reduced the execution time even further, but as I mentioned, this was a low priority update, so there was no need to push it.
This process should work equally well on non-replicated updates; merely replace the Execute statement with the actual update.
/************ Chunked Update Script with Control Table ************/ /* --------------------- Preparation Script --------------------- */ /* Note: Execute this section in a separate query window */ Use yourDatabase; Go Set NoCount On; /* Create a control table to facilitate tweaking of parameters */ Create Table dbo.scratch_largeUpdate_control ( sizeOfBatch int , waitForDelay char(8) , minRecordToUpdate int , maxRecordToUpdate int ); /* Create your control data; you only want 1 row in this table */ Insert Into dbo.scratch_largeUpdate_control (sizeOfBatch, waitForDelay, minRecordToUpdate, maxRecordToUpdate) Select 10000, '00:00:05', 40297132, 107459380; /* Update Script */ Update dbo.scratch_largeUpdate_control Set sizeOfBatch = 100000 , waitForDelay = '00:00:30'; /* ------------------------ Update Script ------------------------ */ Use yourDatabase; Go Set NoCount On; Declare @batchSize int , @minID int , @maxID int , @procMinID int , @procMaxID int , @delay char(8) , @statusMsg varchar(1000); Begin Try IF @@SERVERNAME Not In ('PRODSERVER') RAISERROR('Sorry, this cannot be executed here!', 16, 1); If Not Exists( Select object_id From sys.objects Where [name] = 'scratch_largeUpdate_control' And type = 'U' ) RaisError ('ERROR: Control table does not exist!', 16, 1) With NoWait; Else Select @minID = minRecordToUpdate , @maxID = maxRecordToUpdate , @batchSize = sizeOfBatch , @delay = waitForDelay From dbo.scratch_largeUpdate_control With (NoLock); Set @statusMsg = 'Beginning update; batch size of ' + Cast(@batchSize As varchar(10)) + ', delay of ' + @delay + ' defined. Estimate ' + Cast((((@maxID - @minID) / @batchSize) + 1) As varchar(10)) + ' iterations to be performed.' RaisError (@statusMsg, 10, 1) With NoWait; While @minID < @maxID Begin Select @procMinID = @minID , @procMaxID = (@minID + (@batchSize - 1)); /* Execute actual update code here OR Call a replicated stored procedure, i.e. */ Execute dbo.myReplicatedUpdateProc @minRecordID = @procMinID , @maxRecordID = @procMaxID; Set @statusMsg = 'Updating records ' + Cast(@minID As varchar(10)) + ' through ' + Cast((@minID + (@batchSize - 1)) As varchar(10)) + '...'; RaisError (@statusMsg, 10, 1) With NoWait; /* Update our control table with the last successfully updated record ID. In the event of an error, we can start from here. */ Update dbo.scratch_largeUpdate_control Set minRecordToUpdate = @minID + @batchSize; Select @minID = @minID + @batchSize; WaitFor Delay @delay; -- breather for the server /* Check to see if our control values have changed */ If Not Exists( Select * From dbo.scratch_largeUpdate_control With (NoLock) Where @batchSize = sizeOfBatch And @delay = waitForDelay) Begin /* There was a change, so grab our new values */ Select @batchSize = sizeOfBatch , @delay = waitForDelay From dbo.scratch_largeUpdate_control With (NoLock) /* Print a status message with the new values */ Set @statusMsg = 'Parameters changed: batch size = ' + Cast(@batchSize As varchar(10)) + ', delay = ' + @delay; RaisError (@statusMsg, 10, 1) With NoWait; End End RaisError ('Success!', 10, 1) With NoWait; End Try /* Handle your errors */ Begin Catch Set @statusMsg = 'An error has occurred and the last ' + 'transaction has been rolled back. ' + 'Last record successfully updated was ' + 'record_id = ' + Cast((@minID + (@batchSize - 1)) As varchar(10)); RaisError (@statusMsg, 16, 1) With NoWait; /* Return the error message */ Select Error_Number() , Error_Procedure() , DB_Name() , Error_Line() , Error_Message() , Error_Severity() , Error_State(); End Catch; /* -------------------------- Clean-Up ---------------------------- Drop Table dbo.scratch_largeUpdate_control; ----------------------------------------------------------------- */ |
