Deleting a Column: Meta-data-only operation
Microsoft’s SQL Server development team is constantly working to improve performance. One important performance optimization methodology that has been eminent in SQL Server for a long time has been making as many meta-data-only operations as possible. That is, instead of modifying every page in a table when modifying a table definition, we should only change the definition of the table itself, and change each page in future when we need to rewrite the page for some other reason. The impact of meta-data-only operations can be seen in a variety of places, such as adding a nullable column to a table. Or adding a non-nullable column that has a default value defined. Or, as will be shown in this post, dropping an existing column from a table.
Greek Girls on the Shore, by Joaquin Sorolla, 1895
Dropping a column that is not referenced by any other object lets the storage engine simply mark the column definition as no longer present. Deleting the meta-data invalidates the procedure cache. Any query that subsequently references the affected table will result in the plan for that query be recompiled. The recompile operation can only return columns that currently exist in the table. As a result, the storage engine skips the bytes stored in each page for the dropped column, as if the column no longer exists.
If the table is rebuilt the rebuild operation ignores those bytes allocated to the dropped column. When a DML operation deletes a row, or modifies a value, the page affected is re-written without the de-allocated bytes.
This effectively spreads the load of dropping the column over time, making it less noticeable.
Caveat Emptor
There are circumstances where you cannot drop a column, such as when the column is included in an index, or when you’ve manually created a statistics object for the column. One example shows the error that is presented when attempting to alter a column that has a manually created statistics object. The same semantics apply when dropping a column – if the column is referenced by any other object, it cannot simply be dropped. The referencing object must be altered first so it no longer references the column to be dropped. At that point, the column can be dropped.
The Script
The code below shows how meta-data-only operations can be validated by looking at the transaction log. It uses the undocumented fn_dblog command to show records recorded in the transaction log.
It creates a table, inserts a row, and displays the transaction log contents of those operations. The code then drops the column and shows the transaction log afterwards. It also shows the effect of modifying an existing row after the column is deleted to show how pages are re-written on an as-needed-basis afterwards.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
DROP TABLE IF EXISTS dbo.DropColumnTest; GO CREATE TABLE dbo.DropColumnTest ( rid int NOT NULL CONSTRAINT DropColumnTest_pkc PRIMARY KEY CLUSTERED , someCol varchar(8000) NOT NULL ); INSERT INTO dbo.DropColumnTest (rid, someCol) SELECT 1, REPLICATE('Z', 8000); GO DECLARE @startLSN nvarchar(25); SELECT TOP(1) @startLSN = dl.[Current LSN] FROM sys.fn_dblog(NULL, NULL) dl ORDER BY dl.[Current LSN] DESC; DECLARE @a int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), LEFT(@startLSN, 8), 0), 1) , @b int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@startLSN, 10, 8), 0), 1) , @c int = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), RIGHT(@startLSN, 4), 0), 1); SELECT @startLSN = CONVERT(varchar(8), @a, 1) + ':' + CONVERT(varchar(8), @b, 1) + ':' + CONVERT(varchar(8), @c, 1) ALTER TABLE dbo.DropColumnTest DROP COLUMN someCol; SELECT * FROM sys.fn_dblog(@startLSN, NULL) --modify an existing data row SELECT TOP(1) @startLSN = dl.[Current LSN] FROM sys.fn_dblog(NULL, NULL) dl ORDER BY dl.[Current LSN] DESC; SET @a = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), LEFT(@startLSN, 8), 0), 1); SET @b = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), SUBSTRING(@startLSN, 10, 8), 0), 1); SET @c = CONVERT(varbinary(8), '0x' + CONVERT(varchar(10), RIGHT(@startLSN, 4), 0), 1); SELECT @startLSN = CONVERT(varchar(8), @a, 1) + ':' + CONVERT(varchar(8), @b, 1) + ':' + CONVERT(varchar(8), @c, 1) UPDATE dbo.DropColumnTest SET rid = 2; SELECT * FROM sys.fn_dblog(@startLSN, NULL); |
This post is part of our series on Database Internals.
Remus Rusanu has a great blog post about reading and understanding the transaction log with fn_dblog.
[…] Hannah Vernon takes us through column dropping: […]