> As you can see from the output of the fn_dblog command in the test above, the entire operation is fully logged. This goes for simple recovery, as well as full recovery. The terminology "fully logged" maybe misinterpreted as the data modification is not logged. This is not what happens - the modification is logged, and can be fully rolled back.
> The log is simply only recording the pages that were touched, and since none of the table's data-pages were logged by the DDL operation, both the DROP COLUMN, and any rollback that might occur will happen extremely quickly, regardless of the size of the table.
yah, perhaps my use of the word "fully" is misleading. Sounds too close to the "full recovery" definition of the word, perhaps. I should change it to say "the entire operation is logged."
by "rolled back" - I meant if you did that inside a transaction and did a ROLLBACK at the end.
I was judging it as non-minimal logging, IE the data pages modifications are captured, due to that docs page being ambiguous and me taking it literally.
It's not misleading to me. But I could totally see how it could be to other people
@ErikDarling I believe her. I've seen a working repro now, but you need high concurrency to see the difference. Erland found a roughly 20% throughput improvement using 100 threads.
@MaxVernon I think it would be more accurate to say the column is removed from the metadata, the schema change invalidates existing plans, new plans can only reference columns that continue to exist, so naturally the storage engine does not return them (it's never asked to). Something like that. You could also use DBCC PAGE perhaps. That would show the column data continues to exist on the page. Talking about logging seems rather indirect.
@MaxVernon Oh I see why you talk about logging now, it's in the question! LOL
They're really two separate questions, but too late now I guess.
@JoshDarnell Yes, exactly. The lasting bulk value of Q & A is in the answers. There is some transient value to the OP as well, but that's just one person.
100 threads x 100 iterations, they both take around 37 seconds
i get less PAGELATCH_EX, but wow that WRITELOG
DROP TABLE dbo.Votes_Insert;
CREATE TABLE dbo.Votes_Insert
(
Id INT IDENTITY(1, 1) NOT NULL,
PostId INT NOT NULL,
UserId INT NULL,
BountyAmount INT NULL,
VoteTypeId INT NOT NULL,
CreationDate DATETIME NOT NULL,
CONSTRAINT PK_Votes_Insert_Id
PRIMARY KEY CLUSTERED (Id ASC)
WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)
);
GO
CREATE PROCEDURE dbo.VoteInserter
AS
SET NOCOUNT, XACT_ABORT ON
BEGIN
INSERT dbo.Votes_Insert ( PostId, UserId, BountyAmount, VoteTypeId, CreationDate )
i wonder if it matters that i truncate the table in between runs
DROP TABLE dbo.Votes_Insert;
CREATE TABLE dbo.Votes_Insert
(
Id INT IDENTITY(1, 1) NOT NULL,
CONSTRAINT PK_Votes_Insert_Id
PRIMARY KEY CLUSTERED (Id ASC)
WITH (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)
);
GO
