Sql-server – the row overhead when using Page Compression

compressiondata-pagessql-server-2016

I have created a table with 650 Numeric(19,4) columns.
When I switch on Page Compression, by running

ALTER TABLE fct.MyTable REBUILD  WITH (DATA_COMPRESSION = PAGE);

I get

Msg 1975, Level 16, State 1
Index 'PK_Mytable' row length exceeds the maximum permissible length of '8060' bytes.

but 650 times 9 bytes is only 5850 bytes, which is quite far from the stated limit of 8060 bytes.

The server is running Windows 2012 r2 with SQL Server 2016 SP1 CU2

What is the row overhead when using Page Compression?

Here is some code to show what I mean:

/* test script to demo MSG 1975 */
DECLARE @sql NVARCHAR(max)='', @i INT =0
drop table if exists dbo.mytable;

SET @sql = 'Create table dbo.Mytable (MyTableID bigint not null 
  identity(1,1) primary key clustered, '

WHILE @i < 593 BEGIN
    SET @sql += ' Column' + LTRIM(@i) + ' numeric(19,4) null, '
    SET @i +=1
END

SET @sql += ' LastColumn int) '
--SET @sql += ' with (DATA_COMPRESSION = ROW) '
SET @sql += ' with (DATA_COMPRESSION = PAGE) '

SELECT @sql
EXEC sys.sp_executesql @sql

SELECT top 10000 * FROM dbo.MyTable MT

Row compression also fails, but at a different row count.

Best Answer

If you try creating your table without the clustered PK Constraint, and you'll get a slightly different error:

Msg 1701, Level 16, State 1, Line 1 Creating or altering table 'Mytable' failed because the minimum row size would be 8067, including 1530 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

In this error message, you can see that there is 1530 bytes of internal overhead for page compression.

Now, you can do the math:

  • 8 bytes for bigint MyTableID
  • 4 bytes for int LastColumn
  • 9 bytes for each of the 593 numeric(19,4) columns (5337 bytes total)
  • 1530 bytes of compression overhead

So, 8 + 4 + (593*9) + 1530 = 6879. Wait a second.... That's still below 8060. What's up with that?!

The Page Compression algorithm actually stacks several compression algorithms together. The first step is to apply ROW compression. The overhead of row compression is not included in the 1530 bytes of overhead listed in that error message.

You can read more about how row compression works here on my blog and here in BOL. You'll note in the BOL article that it describes the numeric storage as "This storage is exactly same as the vardecimal storage format," but doesn't explain vardecimal. This post covers vardecimal a bit more--essentially, it adds 2 bytes of overhead per column to store the actual length (similar to what varchar does).

Row compression will require an additional 2 bytes for each of the 593 numeric columns, plus the bigint and int will require 1 byte of overhead each.

The row-compressed storage requirements would be:

  • 8 bytes + 1 byte overhead for bigint MyTableID
  • 4 bytes + 1 byte overhead for int LastColumn
  • 9 bytes + 2 bytes overhead for each of the 593 numeric(19,4) columns
  • 1188 bytes of ROW compression overhead

8 + 4 + (593*9) = 5349 bytes data

1 + 1 + (593*2) = 1188 bytes row compression overhead

6537 bytes total for row-compressed schema

Now that we have the row size for the row-compressed schema, we can revisit our math. The page-compressed row size will be the data size + row compression overhead + page compression overhead:

  • 8 bytes for bigint MyTableID
  • 4 bytes for int LastColumn
  • 9 bytes for each of the 593 numeric(19,4) columns
  • 1188 bytes of ROW compression overhead
  • 1530 bytes of PAGE compression overhead
  5349 bytes data 
+ 1188 bytes row compression overhead 
+ 1530 bytes page compression overhead

8067 bytes total

Related Question