How do I demonstrate Transaction Log activity?

Question

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.

Answer 1

• 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).

Answer 2

Here's what I came up with: If I switch to using a test database (i.e. [MyTest] instead of [tempdb]), then I can collect the transaction log with:

-- start collecting TLogs:
ALTER DATABASE [Mytest] SET RECOVERY FULL WITH NO_WAIT
backup database [MyTest] to disk = 'nul:'

And I can clear these logs any time with:

-- clear Tlogs
checkpoint
backup log [mytest] to disk = 'nul:'

I can then run my alter table statements one at a time and examine their effects on the transaction log with:

-- Examine TLog for the above statement
declare @transactionId nvarchar(28)
select TOP 1 @transactionId = [Transaction Id]
from fn_dblog(null, null)
where [Transaction Name] = 'ALTER TABLE'

select count(1), AllocUnitName
from fn_dblog(null, null)
where [Transaction Id] = @transactionId
group by AllocUnitName

Assuming that my statement is the only ALTER TABLE statement run since I cleared the log.