Why Should I Rebuild My Clustered Index? Part II

Following on from my post, regarding columns not being physically deleted until a clustered index is rebuild (see here), I just wanted to mention that somewhat surprisingly, the same applies if you increase the width of a column.

If you expand a column's width, SQL does not alter the existing column, it creates a new one. Then, similarly to deleting a column from a table, the original column is not deleted until the CI has been rebuilt.

Ok, ok, lets prove it! :)

First off, I'll create a table to use as a test...

...Now, lets see which page is storing the data, by using %%physloc%% (see here and yes, for those of you following my blog, this is my new favourite function. Thanks Charles! :-) )...

...So now we have a page number, (26901 in my case) we will have a look inside it using DBCC PAGE...

...When I ran this, I saw the following (partial) results...


Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
ID = 1
Slot 0 Column 2 Offset 0x8 Length 6000 Length (physical) 6000
Big_Col1 = Big text


Slot 0 Column 3 Offset 0x1778 Length 400 Length (physical) 400
Big_Col2 = More text
...So now, lets resize the column, and then look inside the page again...

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
ID = 1
Slot 0 Column 2 Offset 0x8 Length 6000 Length (physical) 6000
Big_Col1 = Big text


Slot 0 Column 67108865 Offset 0x1778 Length 0 Length (physical) 400
DROPPED = NULL
Slot 0 Column 3 Offset 0x190f Length 400 Length (physical) 400
Big_Col2 = More text
...As you can see from the output above, the column has been marked as a "Ghost Column", but has not been physically deleted. It will remain this way until the CI is rebuilt (or created).

Can this get worse? Well...yes it can! What happens if I expand this column again, this time to 1000 characters. In theory, we have a 4-byte int column, a 6000-byte nchar and a 2000-byte nchar. The problem is, we will also have the 2 400-byte "Ghost" columns, making the row size 8804-bytes. This is a problem because the maximum row size is 8060-bytes. So what will happen?

Let's alter the table again, this time increasing the size of Big_Col_1 and then look  at the Page again...

ALTER TABLE dbo.ResizeColumnDemoSlot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
ID = 1
Slot 0 Column 2 Offset 0x8 Length 6000 Length (physical) 6000
Big_Col1 = Big text


Slot 0 Column 67108865 Offset 0x1778 Length 0 Length (physical) 400
DROPPED = NULL
Slot 0 Column 3 Offset 0x190f Length 400 Length (physical) 400
Big_Col2 = More text
...So where is the old version of Big_Col_1? Physically deleted? No! Lets look at some system tables...

...The output below, clearly shows that it has pushed the original version of the column off the page, into the "Small LOB" store. This means that not only are we still storing the original column, we are also storing a 24-byte pointer to it in the ROW_OVERFLOW_DATA allocation unit!

object_id               rows           type_desc                                       total_pages
311672158           1                 IN_ROW_DATA                               2
311672158           1                 ROW_OVERFLOW_DATA               2

...The reason that each allocation unit shows a value of 2 for total pages, is because each allocation unit has a separate IAM page.

     DROP CONSTRAINT DF_Table_1_Big_Col1
GO
ALTER TABLE dbo.ResizeColumnDemo
     DROP CONSTRAINT DF_Table_1_Big_Col2
GO

ALTER TABLE dbo.ResizeColumnDemo
     ALTER COLUMN Big_Col1 NVARCHAR(4000) NOT NULL
GO

ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col1 DEFAULT (N'Big text') FOR Big_Col1
GO
ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col2 DEFAULT (N'More text') FOR Big_Col2
GO

SELECT object_id, rows, type_desc, total_pages
FROM sys.partitions p
INNER JOIN sys.allocation_units a
     ON p.partition_id = a.container_id
WHERE object_id = OBJECT_ID('dbo.ResizeColumnDemo')

ALTER TABLE dbo.ResizeColumnDemo
     DROP CONSTRAINT DF_Table_1_Big_Col1
GO

ALTER TABLE dbo.ResizeColumnDemo
     DROP CONSTRAINT DF_Table_1_Big_Col2
GO

ALTER TABLE dbo.ResizeColumnDemo
     ALTER COLUMN Big_Col2 NVARCHAR(500) NOT NULL
GO

ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col1 DEFAULT (N'Big text') FOR Big_Col1
GO

ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col2 DEFAULT (N'More text') FOR Big_Col2
GO

DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2008',1,26901,3)

SELECT sys.fn_PhysLocFormatter(%%physloc%%), *
FROM ResizeColumnDemo

CREATE TABLE dbo.ResizeColumnDemo
 (
 ID int NOT NULL IDENTITY (1, 1),
 Big_Col1 nchar(3000) NOT NULL,
 Big_Col2 nchar(200) NOT NULL
 )  ON [PRIMARY]
GO

ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col1 DEFAULT N'Big text' FOR Big_Col1
GO
ALTER TABLE dbo.ResizeColumnDemo ADD CONSTRAINT
     DF_Table_1_Big_Col2 DEFAULT N'More text' FOR Big_Col2
GO

INSERT INTO dbo.ResizeColumnDemo DEFAULT VALUES
GO