How do I demonstrate Transaction Log activity?

Say I have the following setup:

use tempdb
go

set nocount on
go

create table MyTest
(
    Column1 varchar(100),
    Column2 text 
)
go

insert mytest (Column1, Column2)
select REPLICATE('a', 100), REPLICATE('a', 100)
from sys.syscolumns a, sys.syscolumns b

I'd like to convert each of the columns to varchar(max) like this:

-- processes every page:
alter table mytest
alter column Column1 varchar(max)

-- processes only metadata:
alter table mytest
alter column Column2 varchar(max)

How can I demonstrate that the first command processes the whole table while the second command only processes metadata. I was thinking of using SET STATISTICS IO which reports thousands of logical reads for the first command and nothing for the other. I was also thinking of using DBCC LOG or fn_dblog. But I wasn't sure how to interpret or tie the results to the queries I issued.

• Pick a page containing a record in your table (use %%physloc%% to find one).

• Run DBCC PAGE before the DDL, write down last_lsn of the page.

• Run your DDL

• Run again DBCC PAGE. Did last_lsn change?

If the last_lsn changed the DDL is a size-of-data updates-every-record kind of change. If the last_lsn did no change obviously the DDL did not update every record.

For a more elaborate way, you can track page write XEvents for a single statement (the DDL).

StackExchange Database Administrators Q#38992, answer score: 6