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.
Best Answer
%%physloc%%
to find one).last_lsn
of the page.last_lsn
change?If the
last_lsn
changed the DDL is a size-of-data updates-every-record kind of change. If thelast_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).