Brian Bonk | Week 4: Data Page Restrictions

Mar 24, 2022 SQL Server t-sql 5 min.

// Week 4: Data Page Restrictions

Week 4: Data Page Restrictions

You have just a few pages ahead of you, and then you have already passed the 1st month of the SQL Server Performance Tuning series - congratulations! Today I’m talking about limitations that you have with data pages, and why there are restrictions that you will love, while you will hate other restrictions.

As you have learned in week 2, a data page is always 8kb large, and you are able to store 8060 bytes of data on it. And the size of your records dictates how many records you can store on one page. When you deal with fixed-length data types (like CHAR, INT, DATETIME, etc.) you have the restriction that the record length can’t exceed the 8060 bytes including the internal overhead that SQL Server uses.

Restrictions - the good ones

When you have a table with less than 8 columns, you need to add 7 bytes for the internal overhead. And for every 8 more columns you have to add 1 additional byte, e.g. with 17 columns you need 9 bytes internal overhead (7 + 1 + 1). If you are trying to create a larger record size, SQL Server will return you during your CREATE TABLE statement an error message. Just have a look on the following table definition.

CREATE TABLE TooLargeTable1
(
   Column1 CHAR(5000),
   Column2 CHAR(3000),
   Column3 CHAR(54)
)
GO

As you can see, every record needs 8061 bytes (5000 + 3000 + 54 + 7 bytes). So in that case, SQL Server returns you the following error message when you try to create the table:

Creating or altering table 'TooLargeTable1' files beacuse the minimum row size would be 8061, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes

When you create a table with more than 8 columns, you have to take into account that SQL Server needs now 8 additional bytes of overhead:

CREATE TABLE TooLargeTable2
(
   Column1 CHAR(1000) NOT NULL,
   Column2 CHAR(1000) NOT NULL,
   Column3 CHAR(1000) NOT NULL,
   Column4 CHAR(1000) NOT NULL,
   Column5 CHAR(1000) NOT NULL,
   Column6 CHAR(1000) NOT NULL,
   Column7 CHAR(1000) NOT NULL,
   Column8 CHAR(1000) NOT NULL,
   Column9 CHAR(53) NOT NULL
)
GO

So this is again an invalid table definition (8000 + 53 + 8 bytes), where SQL Server will return you an error message.

Restrictions - the bad ones

In the previous section I have shown you data page restrictions that you will love, because SQL Server returns you here an error message when you try to create the table. But there are also data page restrictions that you will hate, because SQL Server will allow you to create the table, and sometimes INSERT statements will succeed, and sometimes they will just fail… Let’s have a look on that.

The problem that we are facing here is with variable-length data types, like VARCHAR. When these columns doesn’t fit into the data page itself, SQL Server is able to move them to an off-row location on a separate page. This is a so-called Row-Overflow Page. On the original page SQL Server leaves a pointer that is 24 bytes long that points to the row-overflow page. And in some cases this pointer can exceed in combination with the other columns the limitation of the 8060 bytes. Let’s have a look on the following table definition.

CREATE TABLE TooLargeTable3
(
   Column1 CHAR(5000),
   Column2 CHAR(3000),
   Column3 CHAR(30),
   Column4 VARCHAR(3000)
)
GO

As you can see I’m using here a VARCHAR(3000) data type. SQL Server will give you here also a warning, as you can see. The warning means that you can create the table, but INSERT/UPDATE statements may fail…

Warning: The table 'TooLargeTable3' has been created, but its maximum row size exceeds the allowed maximum of 8060 bytes. INSERT or UPDATE to this table will fail if the resulting row exceeds the size limit.

The following insert in the table will succeed:

INSERT INTO TooLargeTable3 VALUES
(
   REPLICATE('x', 5000),
   REPLICATE('x', 3000),
   REPLICATE('x', 30),
   REPLICATE('x', 19)
)
GO

The record size is here 8056 bytes long (5000 + 3000 + 30 + 19 + 7 bytes). In this scenario SQL Server will also store the data of the 4th column directly on the main data page. But imagine now the following INSERT statement.

INSERT INTO TooLargeTable3 VALUES
(
   REPLICATE('x', 5000),
   REPLICATE('x', 3000),
   REPLICATE('x', 30),
   REPLICATE('x', 3000)
)
GO

In the previous INSERT statement, SQL Server has to move the data of the 4th column to a row-overflow page, because the 3000 bytes can’t fit in any way into the main data page. This also means that SQL Server will leave here a pointer of 245 bytes that points to the different page where the data can be found. Therefore our record size is now 8061 bytes long (5000 + 3000 + 30 + 24 + 7 bytes).

Boom, your record size is larger than 8060 bytes, and the INSERT statement fails!

These are the bad restrictions, because they are hitting you during the operation of your database, the good ones are already facing you when you are defining the table schema. Just think about that…

Summary

When you are designing your table schema, you should really think very carefully what you are doing. As you can see there are plenty of restrictions that you can run into when you deal with data pages in SQL Server. Of course, when SQL Server gives you an error message, and you are not allowed to create a table, everything is almost fine.

But when you get a warning, mostly everyone just ignores them without thinking about it. That’s always a bad best practice, because as you have seen here, your INSERT may fail during runtime, so you can’t predict when problems will arise. I hope that I have given you with this installment of the SQLpassion Performance Tuning Training Plan a good insight why the understanding of the internal structure of data pages is very essential.

In the next installment I will talk in more detail about heap tables in SQL Server, and why they are sometimes good, and sometimes bad. Stay tuned, and enjoy the next 7 days!