Minimizing impact of widening an IDENTITY column – part 1

[ Part 1 | Part 2 | Part 3 | Part 4 ]

A problem that I have seen crop up a few times recently is the scenario where you have created an IDENTITY column as an INT, and now are nearing the upper bound and need to make it larger (BIGINT). If your table is large enough that you're hitting the upper bound of an integer (over 2 billion), this is not an operation you can pull off between lunch and your coffee break on a Tuesday. This series will explore the mechanics behind such a change, and different ways to make it happen with varying impacts on uptime. In the first part, I wanted to take a close look at the physical impact of changing an INT to a BIGINT without any of the other variables.

What Actually Happens When You Widen an INT?

INT and BIGINT are fixed size data types, therefore a conversion from one to the other has to touch the page, making this a size-of-data operation. This is counter-intuitive, because it seems like it would not be possible for a data type change from INT to BIGINT to require the additional space on the page immediately (and for an IDENTITY column, ever). Thinking logically, this is space that could not possibly be needed until later, when an existing INT value was changed to a value > 4 bytes. But this isn't how it works today. Let's create a simple table and see:

CREATE TABLE dbo.FirstTest
(
  RowID  int         IDENTITY(1,1), 
  Filler char(2500)  NOT NULL DEFAULT 'x'
);
GO

INSERT dbo.FirstTest WITH (TABLOCKX) (Filler)
SELECT TOP (20) 'x' FROM sys.all_columns AS c;
GO

A simple query can tell me the low and high page allocated to this object, as well as the total page count:

SELECT 
  lo_page    = MIN(allocated_page_page_id), 
  hi_page    = MAX(allocated_page_page_id), 
  page_count = COUNT(*)
FROM sys.dm_db_database_page_allocations
(
  DB_ID(), OBJECT_ID(N'dbo.FirstTest'), NULL, NULL, NULL
);

Now if I run that query before and after changing the data type from INT to BIGINT:

ALTER TABLE dbo.FirstTest ALTER COLUMN RowID bigint;

I see these results:

-- before:

lo_page    hi_page    page_count
-------    -------    ----------
243        303        17

-- after:

lo_page    hi_page    page_count
-------    -------    ----------
243        319        33

It is clear that 16 new pages were added to make room for the additional space required (even though we know none of the values in the table actually require 8 bytes). But this wasn't actually accomplished the way you might think – rather than widen the column on the existing pages, the rows were moved to new pages, with pointers left behind in their place. Looking at page 243 before and after (with the undocumented DBCC PAGE):

-- ******** Page 243, before: ********

Slot 0 Offset 0x60 Length 12

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP    Record Size = 12

Memory Dump @0x000000E34B9FA060

0000000000000000:   10000900 01000000 78020000                    ..	.....x...

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

RowID = 1                           

Slot 0 Column 2 Offset 0x8 Length 1 Length (physical) 1

filler = x                          


-- ******** Page 243, after: ********

Slot 0 Offset 0x60 Length 9

Record Type = FORWARDING_STUB       Record Attributes =                 Record Size = 9

Memory Dump @0x000000E34B9FA060

0000000000000000:   04280100 00010078 01                          .(.....x.
Forwarding to  =  file 1 page 296 slot 376

Then if we look at the target of the pointer, page 296, slot 376, we see:

Slot 376 Offset 0x8ca Length 34

Record Type = FORWARDED_RECORD      Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 34                    
Memory Dump @0x000000E33BBFA8CA

0000000000000000:   32001100 01000000 78010000 00000000 00030000  2.......x...........
0000000000000014:   01002280 0004f300 00000100 0000               .."...ó.......
Forwarded from  =  file 1 page 243 slot 0                                

Slot 376 Column 67108865 Offset 0x4 Length 0 Length (physical) 4

DROPPED = NULL                      

Slot 376 Column 2 Offset 0x8 Length 1 Length (physical) 1

filler = x                          

Slot 376 Column 1 Offset 0x9 Length 8 Length (physical) 8

RowID = 1 

This is a very disruptive change to the structure of the table, obviously. (And an interesting side observation: the physical order of the columns, RowID and filler, have been flipped on the page.) Reserved space jumps from 136 KB to 264 KB, and average fragmentation bumps up modestly from 33.3% to 40%. This space does not get recovered by a rebuild, online or not, or a reorg, and – as we'll see shortly – this is not because the table is too small to benefit.

Note: this is true even in the most recent builds of SQL Server 2016 – while more and more operations like this have been improved to become metadata-only operations in modern versions, this one hasn't been fixed yet, though clearly it could be – again, especially in the case where the column is an IDENTITY column, which can't be updated by definition.

Performing the operation with the new ALTER COLUMN / ONLINE syntax, which I talked about last year, yields some differences:

-- drop / re-create here
ALTER TABLE dbo.FirstTest ALTER COLUMN RowID bigint WITH (ONLINE = ON);

Now the before and after becomes:

-- before:

lo_page    hi_page    page_count
-------    -------    ----------
243        303        17

-- after:

lo_page    hi_page    page_count
-------    -------    ----------
307        351        17

In this case, it was still a size-of-data operation, but the existing pages were copied and re-created due to the ONLINE option. You might wonder why, when we changed the column size as an ONLINE operation, the table is able to cram more data into the same number of pages? Each page is now denser (fewer rows but more data per page), at the cost of scatter – fragmentation doubles from 33.3% to 66.7%. Space used shows more data in the same reserved space (from 72 KB / 136 KB to 96 KB / 136 KB).

And at Larger Scale?

Let's drop the table, re-create it, and populate it with a lot more data:

CREATE TABLE dbo.FirstTest
(
  RowID INT IDENTITY(1,1), 
  filler CHAR(1) NOT NULL DEFAULT 'x'
);
GO

INSERT dbo.FirstTest WITH (TABLOCKX) (filler) 
SELECT TOP (5000000) 'x' FROM sys.all_columns AS c1
  CROSS JOIN sys.all_columns AS c2;

From the outset, we now have 8,657 pages, a fragmentation level of 0.09%, and space used is 69,208 KB / 69,256 KB.

If we change the data type to bigint, we jump to 25,630 pages, fragmentation is reduced to 0.06%, and space used is 205,032 KB / 205,064 KB. An online rebuild changes nothing, nor does a reorg. The whole process, including a rebuild, takes about 97 seconds on my machine (the data population took all of 2 seconds).

If we change the data type to bigint using ONLINE, the bump is only to 11,140 pages, fragmentation goes to 85.5%, and space used is 89,088 KB / 89160 KB. Online rebuilds and reorgs still change nothing. This time, whole process only takes about a minute. So the new syntax definitely leads to faster operations and less additional disk space, but high fragmentation. I'll take it.

Up Next

I am sure you are looking at my tests above, and wondering about a few things. Most importantly, why is the table a heap? I wanted to investigate what actually happens to the page structure and page count with no indexes, keys, or constraints fuzzying up the details. You might also wonder why this change was so easy – in a scenario where you have to change a true IDENTITY column, it is probably also the clustered primary key, and has foreign key dependencies in other tables. This definitely introduces some hiccups to the process. We'll take a closer look at these things in the next post in the series.

—

[ Part 1 | Part 2 | Part 3 | Part 4 ]