Database is Always On High Availability synchronised database on SQL Server 2019 with READ_COMMITTED_SNAPSHOT on and we are updating datatype from varchar to nvarchar (with "alter table" command) on a column on a large (1 TB) table.
It blew up our tempdb due to version store.
I understand why this happened, but really just looking at (maybe creative) ways to stop this happening (ideally to bypass the version store for this command).
I'm considering recommending ADR to at least mean that at least issue is isolated to this particular database, but would love to hear any other ideas.
Add a comment
|
2 Answers
Prefacing this by saying I think my idea is lame, but it is an idea. :)
You can try creating a new empty NVARCHAR column (of the appropriate size) and do batches of updates from OldVarCharColumn to NewNVarCharColumn at the rate your system can tolerate. You can even automate the entire update by iteratively looping and using a rolling range of the key (assuming it's numerical) for which rows, by setting a variable on each iteration (e.g. WHERE KeyField >= @CurrentIterationValue - 1000 AND KeyField < @CurrentIterationValue) and by using the WAITFOR DELAY command to pause between each iteration for any amount of time you specify (e.g. WAITFOR DELAY '00:05:00.000' will pause execution for 5 minutes).
Once your new column is fully updated you can then DROP the OldVarCharColumn.
-
1That's pretty lame :) but probably the best option we got. I'll mark this as accepted answer unless anyone comes up with a better idea soon.– PatrickCommented Mar 4, 2021 at 9:17
Just being playing around and found that using WITH (ONLINE=ON) does not appear to write to version store. Note it takes (at least) twice as long, so will talk to team about pros and cons of this.