How SQL Server Stores Data in Data Pages: Part 2

Introduction

Hello all, welcome to our second and last part of the article series named “How SQL Server stores data in data pages”.

Before moving further I strongly recommend you read the previous part at:

How SQL Server stores data in Data Pages : Part 1

In our previous part, we learned how SQL Server stores data in Data Pages, we also saw the types of pages available in SQL Server. We also learned how to view the contents of any Data Page and what's the relation between an IAM Page and a Data Page.

In this article we'll learn how SQL allocates space when storing records in data pages and what action it does when a data page capacity gets full, where and how it writes the remaining data. Where can we find the capacity of the data pages?

We'll learn all those things in this article.

As in our last article, we know that a Data Page is divided into 3 sections.



Now, let's try to figure it out how many bytes each section gets.

So, now let's jump to some mathematical calculations.

Total Page Size = 8KB

Since 1 KB = 1024 bytes, 8KB = 8*1024

Therefore, the Total Page Size = 8192 bytes.

Currently we've only 1 record and if you remember, when creating a table, we've defined EmpName as char (500) as shown below.



EmpName's size = 500 bytes. Since it's a char, 1 char = 1 byte.

So currently we've 1 record of size 500 bytes as shown below.



Therefore, Total Record size = 1* 500 = 500 bytes.

But if you see, Record Size is showing 511 bytes instead of 500 bytes, this is because that 11 bytes as added to identify whether the column is null or not. So we'll add these 11 bytes as well.

Therefore, Total Record Data Size= 500 bytes + 11 bytes = 511 bytes.

Since our total page is divided into 3 sections as in the following:

Page Header = 96 bytes.
Data Rows = 511 bytes.
Row Offset = 2 bytes for each record = 2*1 = 2 bytes.

Therefore, Total Used Bytes = 96 bytes + 511 bytes + 2 bytes = 609 bytes.

Total Free Bytes = Total Bytes – Total Used Bytes.

Therefore, Total Free Bytes = 8192 bytes – 609 bytes = 7583 bytes.

If you don't believe my calculations then let's check what the Page Header says.



Oh yes! I'm right, as you can see in the preceding output.

Now let's add 2 more records and again check the free space, but before that take out a pen and paper and first calculate.

INSERT INTO tblEmployees VALUES (2,'Sunny')
INSERT INTO tblEmployees VALUES (3,'Rahul')
GO

Now after addition we've a total of 3 records.

Now when you look in Data Page, you'll get new entries as shown below.



Also we've 3 records in the Table Offset.



Now as per our formula:

Total Free Bytes = Total Bytes – Total Used Bytes.

Total Free Bytes = 8192 bytes – 96 + (511 bytes + 2 bytes) + (511 bytes + 2 bytes) + (511 bytes + 2 bytes).

Total Free Bytes = 8192 bytes – 1635 bytes = 6557 bytes.

Now, if you check your page header for free space, it'll also show the same space.



Now, let's add 15 more records to the table. Currently 6557 bytes are free and if we add another 15 records then it will exceed the current page size because 15 new records means 7695 bytes, in other words 1138 extra bytes.

Now the interesting thing is what will happen with those extra bytes or with those 2 or 3 records. Let's see by adding records.

INSERT INTO tblEmployees VALUES (4,'Azhar')
INSERT INTO tblEmployees VALUES (5,'Raj')
INSERT INTO tblEmployees VALUES (6,'Tyler')
INSERT INTO tblEmployees VALUES (7,'Kavita')
INSERT INTO tblEmployees VALUES (8,'Anup')
INSERT INTO tblEmployees VALUES (9,'Ram')
INSERT INTO tblEmployees VALUES (10,'Sunil')
INSERT INTO tblEmployees VALUES (11,'Sumit')
INSERT INTO tblEmployees VALUES (12,'Bhargav')
INSERT INTO tblEmployees VALUES (13,'Vishal')
INSERT INTO tblEmployees VALUES (14,'Swapnil')
INSERT INTO tblEmployees VALUES (15,'Vinod')
INSERT INTO tblEmployees VALUES (16,'Vaibhav')
INSERT INTO tblEmployees VALUES (17,'Sai')
INSERT INTO tblEmployees VALUES (18,'Jatin')
GO

Now we have a total of 18 records as shown below.



Now let's see what's there in our Data Page.

The following is our additional Data Page.

.
.

.

. 

Out of 18 records, only 14 slots were present in the data page. I also checked the Offset Table, but there I also got only 14 records as shown in the following image.



In the Page Header, the available free space was 401 bytes as shown below.



So, the question is, where are the other 3 records?

If you see the preceding Page Header 153 properly, you'll see an addition in m_nextPage = (1:517). Which means that the next Data Page is 517.



Now, this tells us that there is another Data Page that is added for our table. Let's view that as well.



Oh! Just look at the output, now we have 2 Data Pages and 1 Index Page.

Let's dig into the new Data Page 157 and see whether those 3 records are available or not.

The remaining 3 records are added into the new Data Page as shown below.



Also the Offset Table is showing 3 records.



So what actually happened here is, after adding records to Data Page 153, it is only left with 401 bytes in which the next record was not able to be saved. Hence, a new Data Page is created and SQL Server has written those records in the new Data Page 157.

So once the 8KB page is filled with enough data, a new page is created.



If you see Page Header 157, you'll get the available free bytes.



In the preceding Page Header, the previous PageID is linked to it. Which means the previous Data Page was 153.

If we calculate the bytes for Page Header for 153, we'll get our free bytes as:

Formula = Total Bytes – Total Used Bytes = 8192 – 96+(511 bytes + 2 bytes) * 15 = 7791

Total Free Bytes = 8192 bytes – 7791 bytes = 401 bytes.



If we calculate bytes for Page Header for 157, we'll get our free bytes as:

Formula = Total Bytes – Total Used Bytes => 8192 – 96 + (511 bytes + 2 bytes) * 3 = 7791

Total Free Bytes = 8192 bytes – 1635 bytes = 6557 bytes.

 

So with this I'm winding up this article. I hope all of you now understand the concept of Data Pages and how records are stored in those data pages.

Conclusion

In this article and previous article we learned what Data Pages are and how SQL Server writes data into those data pages. We have also seen how to view the contents of Data Pages. Finally in this article we have seen how to calculate the bytes for Data Pages. I hope you understand the concept. If you have any doubt please let me know, I'll try my best to solve those. Until then keep learning and keep sharing.

Please provide your valuable feedback and comments that enable me to provide a better article the next time.