In SQL Server 2016, the
WITH (ONLINE = ON) option was added to
ALTER TABLE...ALTER COLUMN statements. The documentation states the purpose of this pretty nicely:
Allows many alter column actions to be carried out while the table remains available. Default is OFF. You can run alter column online for column changes related to data type, column length or precision, nullability, sparseness, and collation.
I’ve seen several blog posts out there that talk about how
ONLINE operations like this take longer, which is one of the tradeoffs you make to keep the table online.
But what happens when you update the values in the column being altered? As it turns out, the
tempdb version store is used to store the new values until the operation completes - in the same way as if you were using the
SNAPSHOT isolation level.
Most interesting of all (to me, anyway), this happens even if the database option
ALLOW_SNAPSHOT_ISOLATION is set to
The Inciting Incident
I was reading a question on Database Administrators Stack Exchange: 60GB in version store in short amount of time, allow snapshot isolation is disabled
As I speculated about what might cause this inexplicable version store growth, the first thing that came to mind was Availability Group “readable secondaries.” Queries against readable secondaries use
SNAPSHOT isolation regardless of the requested isolation level, which can lead to quick version store growth on systems with high throughput (or long-running transactions) on the primary.
My guess was wrong in this case (the only AG on this instance was acting as the primary node at the time of the incident).
Then Joe Obbish pointed out to me that
ONLINE column operations might also use the version store in this way, so I set out to test it.
I’m using the 2010 version of the Stack Overflow database, running on SQL Server 2017 CU15. I’ve turned off both of the
SNAPSHOT-related settings in the database:
Altering and Faltering
ONLINE column operation. Don’t try to act like turning an
int into a
varchar(8) is the weirdest thing you’ve seen in a database:
ALTER TABLE dbo.Votes ALTER COLUMN PostId varchar(8) NOT NULL WITH (ONLINE = ON);
The votes table has about 10 million rows in it, so this takes a bit of time (10-15 seconds if nothing else is happening). If I check
sp_WhoIsActive, I can see that:
- the version store is not growing, and
ALTERstatement is chugging along making progress
While that’s running, I’m going to open a second session and update all 10 million rows in the table, and then roll it all back:
BEGIN TRANSACTION; UPDATE dbo.Votes SET PostId = 1; ROLLBACK TRANSACTION;
Here are the results of the monitoring queries after a couple of minutes of both sessions running:
As you can see, the update resulted in 184 MB of version store usage.
On a side note, in this screenshot the
ALTER operation has basically finished doing its work, but it needs a SCH-M lock in order to replace the existing column with the new copy of the column. You can see this as
LCK_M_SCH_M wait reported in the
wait_info column. This clears up once the
Just to really drive the point home about users not being able to use
SNAPSHOT directly, trying to set that isolation level:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT; BEGIN TRANSACTION; UPDATE dbo.Votes SET PostId = 1;
Results in the expected error message:
Msg 3952, Level 16, State 1, Line 5
Snapshot isolation transaction failed accessing database ‘StackOverflow2010’ because snapshot isolation is not allowed in this database. Use ALTER DATABASE to allow snapshot isolation.
If you’re seeing unexpected version store activity in
tempdb on SQL Server 2016 or higher, look out for any
ALTER COLUMN statements with the
ONLINE = ON option enabled.
And if you have any ideas on what might be going on with Tony’s server, feel free to leave a comment here - or add an answer on the Stack Exchange post!