Kalen Delaney : rowsize

Geek City: Detecting Overflowing Columns

Kalen Delaney — Tue, 09 Oct 2007 18:27:00 GMT

SQL Server 2005 allows rows to include variable length columns which do not actually fit in the maximum row size of 8060 bytes. I previously posted about row-overflow columns, and discussed the maximum number of such columns that you could have in a single table. You can read that post here.

Just recently I received another question that related to the section about row-overflow columns in Inside SQL Server 2005: The Storage Engine. I described all the bytes that SQL Server needs for overhead in a data row, but I didn't mention how SQL Server knows that a column in an overflowing column or not. I mentioned that if a column is stored off the row, it leaves a 24-byte pointer behind, but how does SQL Server know that the those 24 bytes are a pointer and not an actual data value?

I needed to do a little exploration to figure out the answer to that one. I'll show you some of the tests I ran to find the answer, but first I'll just give the answer, in conjunction with a review of SQL Server's data row structure. The figure below can be found in The Storage Engine book, and the following discussion is not intended to replace the vast amount of information in that book and the complete discussion of the meaning of all the overhead bytes in each row.

The bytes of interest here are the ones called "Column offset array", which are in the second to last position in the figure. If you have variable length columns, each of those columns uses two bytes to keep track of where on the row that column ends. This information has to be stored with each row, because for each row, the variable length columns can be a different length.

Normally, a two byte value is a small int, which can represent values from
-2^15 (-32,768) to 2^15-1 (32,767). Since byte offsets on the row can only be positive, negative numbers are not needed and SQL Server uses the sign bit (i.e. the high-order bit) to indicate that the column represents a row-overflow pointer and not an actual data value. I will show you one of the tests I ran that illustrates this. I actually ran many similar tests, and then got confirmation from someone on the SQL Server team at Microsoft that the high order bit of the column offset value is where the information is stored.

Validation:

First, create a table in a test database, which has the possibility of row-overflow.

USE testdb
GO
CREATE TABLE bigcols
(a int, b varchar(8000), c varchar(8000) )

Now insert one row into the table, that is big, but will not need to overflow. The first variable length column is 8000 bytes, and the second is 24 bytes.

INSERT INTO bigcols
SELECT 1, replicate('b', 8000), '123456789012345678901234'

USE DBCC IND to find the page number for the data page, and then use DBCC PAGE to look at the page. In my testdb, DBCC IND indicated that the data page for this table was on file 1, page 1001. You will most likely get a different page number, so use that in your DBCC PAGE command.

DBCC IND (testdb, bigcols, -1)
DBCC TRACEON(3604)
DBCC PAGE (testdb, 1, 1001,1)

Here is the first part of the row on page 1001:

30000800 01000000 03006002 00511f69
1f626262 ....

The red bytes are number of variable length columns (2), the first variable length column offset is in green, and the second column offset is in blue.

We have to reverse the bytes before translating, and there are two characters per byte, so the first column offset value is 0x1f51 or 8017. The second column offset is 0x1f69 or 8041, which is 24 bytes after the first. This is expected, as I inserted 24 bytes into the second variable length column.

I now insert another row, with a large value in the third column that will not fit on the page.

INSERT INTO bigcols
SELECT 1, replicate('b', 8000), replicate('c', 8000)

Looking at DBCC IND I see that the row-overflow page is 1002 and the new data page is 1003. Using DBCC PAGE to look at 1002 I see that it contains the c's (the last column). I then use DBCC PAGE to look at 1003.

DBCC IND (testdb, bigcols, -1)
DBCC PAGE (testdb, 1, 1002,1)
DBCC PAGE (testdb, 1, 1003,1)

Here is the first part of the row on page 1003:

30000800 01000000 03006002 00511f69
9f626262 ....

The only difference from the first row, with no row-overflow columns, is the offset of the second variable length column. Interpreting 0x9f69 as an unsigned integer gives us 40809, which is obviously not an offset in a row, since it is 5 times more than the number of bytes allowed in a row. So instead, convert to binary 1001111101101001 and notice the high order 1. Now change that 1 to 0 giving 0001111101101001 and convert that to decimal. It gives us 8041, again indicating that the second variable length column ends 24 bytes after the first. The only difference is that SQL Server will interpret those 24 bytes in the second row as a pointer to row-overflow data and will interpret those 24 bytes in the first row as the real data in the column.

For more information about using DBCC IND and DBCC PAGE, you can read Inside SQL Server or some of my articles in SQL Server Magazine. Or you can read the Microsoft SQL Server Storage Engine blog. Here is a post by Paul Randal, showing the use of DBCC IND and DBCC PAGE. The post explains the output of DBCC IND, and includes links to other posts that explain DBCC PAGE.

Have fun!

~Kalen

Geek City: Maximum Number of Row-overflow columns

Kalen Delaney — Mon, 16 Jul 2007 22:45:00 GMT

I received a question from one of my readers that concerned this paragraph in Inside SQL Server 2005: The Storage Engine.

The number of large variable-length columns that a table can have is not
unlimited, although it is quite large. There is a limit of 1,024 columns
in any table, so that is definitely a limit there. But another limit will
be reached before that. When a column has to be moved off a regular page
onto a row-overflow page, SQL Server keeps a pointer to the row-overflow
information as part of the original row. The pointer is always 24 bytes,
and the row still needs 2 bytes in the row for each variable-length
column, whether or not the variable-length column is stored in the row. So
it turns out that 308 is the maximum number of overflowing columns we can
have, and such a row needs 8,008 bytes just for the 26 overhead bytes for
each overflowing column in the row.

Here is the question: I understood that 26 bytes overhead is required for each overflowing column
in the row but not able to understand how you calculated 308 figure. Please explain.

Ok, I will.

In SQL Server 2005, the maximum number of bytes for any row, including the constant overhead bytes, is 8060. (A row might actually have 14 additional overhead bytes if it has been modified under one of the snapshot-based isolation levels, and those 14 bytes increase the maximum to 8074.) In addition to the bytes mentioned above, every row needs a few other overhead bytes:

2 bytes: row status information
2 bytes: length of the fixed length portion of the row
2 bytes: number of columns
1 bit per column: NULL bitmap (rounded up to whole number of bytes)
2 bytes: row-offset location (stored at the end of the page)
2 bytes: number of variable length columns (only IF there are any, otherwise these bytes aren't used)

If we subtract 11 bytes from 8060 and divide by the 26 bytes each row-overflow column needs, the result is 309 and a fraction. However, if we actually had 309 columns in the table, each would need a bit in the bitmap and 309 bits needs 39 bytes.

So for 309 overflowing columns, we would need 309 *26 + 10 overhead bytes + 39 bytes for the NULL bitmap, which is 8083 bytes, and is longer than the maximum row size.

For 308 overflowing columns, we would need 308 *26 + 10 overhead bytes + 39 bytes, which is 8057 bytes, and that will fit in a single row.

More details can be found in Inside SQL Server 2005: The Storage Engine.

Have fun!

Did you know? -- Altering the length of a fixed-length column

Kalen Delaney — Fri, 13 Oct 2006 16:33:00 GMT

You may be well aware that if you alter the length of a column to a larger value, that SQL Server doesn’t go through all the rows at the time you issue the ALTER. The ALTER is a metadata only change, and the actual movement of data doesn’t happen until a row is updated.

However, SQL Server does not reuse the original bytes in the row when a row’s length is altered. You may end up not being able to use the full allowable row length if you’ve altered column lengths.

Here’s the example. Create a table with two large char columns and a smallint. The initial length of each row will be just a bit more than 3000 bytes, out of a maximum row length of 8060 bytes.

CREATE TABLE bigchange

(col1 smallint, col2 char(2000), col3 char(1000));

Now alter the second column to increase its length to 3000 bytes. The length of a row should now be just a bit more than 4000 bytes.

ALTER TABLE bigchange

ALTER COLUMN col2 char(3000);

Now try and add another 3000 byte column, which should bring the length to just over 7000 bytes. This will fail:

ALTER TABLE bigchange

ADD col4 char(3000);

Msg 1701, Level 16, State 1, Line 1

Creating or altering table 'bigchange' failed because the minimum row size would be 9009, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

However, if you just create a table with two 3000-byte columns and a 1000-byte column, there will be no problem.

CREATE TABLE nochange

(col1 smallint, col2 char(3000), col3 char(1000), col4 char(3000));

Here’s a query you can run to see the metadata for where each column will start. Run it against the bigchange table:

SELECT c.name AS column_name, column_id, max_inrow_length,
pc.system_type_id, leaf_offset

FROM sys.system_internals_partition_columns pc

JOIN sys.partitions p

ON p.partition_id = pc.partition_id

JOIN sys.columns c

ON column_id = partition_column_id

AND c.object_id = p.object_id

WHERE p.object_id=object_id('bigchange');

Here’s my results:

column_name column_id max_inrow_length system_type_id leaf_offset

------------- ----------- ---------------- -------------- -----------

col1 1 2 52 4

col2 2 3000 175 3006

col3 3 1000 175 2006

Note that col1 is only 2 bytes long, and starts at offset 4 (after the row header information) but that the next column doesn’t start until offset 2006. Bytes 6 – 2005, the original 2000 bytes for col2, are not used for anything now.

So be careful when using large datatypes, especially if you want to make them fixed length instead of variable length.

-- Kalen