Say I have the following setup:

use tempdb

set nocount on

create table MyTest
    Column1 varchar(100),
    Column2 text 

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.

  • Which version of SQL Server are you using? – Max Vernon Apr 1 '13 at 15:19
  • 2008 and later. I'd be curious to learn if and why it matters. – Michael J Swart Apr 1 '13 at 15:21
  • Answers you get might be for specific versions, either older or new than you are using. – Max Vernon Apr 1 '13 at 15:22
  • 1
    Using the answers to this question, I was able to write this post: Altering Text Columns: Only a Metadata Change? michaeljswart.com/2013/04/… – Michael J Swart Apr 3 '13 at 17:40
  • 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).

  • Added my own answer. But yours is just as effective. Thanks Remus (marking yours as correct). – Michael J Swart Apr 1 '13 at 16:32

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:
backup database [MyTest] to disk = 'nul:'

And I can clear these logs any time with:

-- clear Tlogs
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.