How SQL Server Store and Manages Data Internally

Back to: SQL Server Tutorial

How SQL Server Store and Manages Data Internally

In this article, I am going to discuss How SQL Server Store and Manages Data Internally in detail. Please read our previous article, where we discussed how to schedule a job in SQL Server using SQL Server Agent. As part of this article, we are going to discuss the following important pointers in detail.

1. How SQL Server Store and Manages Data Internally?
2. How SQL Server Reads and Writes Data internally?
3. SQL Server Pages type
4. How we can see the page internal data?
5. Understanding Page Header, Data and OffSet Table
6. Analyzing the Memory of SQL Server Data Page
7. What happens when the 8KB page size exceeds?

Understanding How SQL Server Store and Manages Data Internally:

In order to understand how SQL Server manages the data internally, let us first create a simple database with a simple table having only one column. So, please execute the following SQL Script to create the TestDB database, Employee table with one column i.e. EmployeeName which is of data type char and length of 1000.

CREATE DATABASE TestDB;
GO

USE TestDB;
GO

CREATE TABLE Employee
(
  EmployeeName CHAR(1000)
)
GO

Whenever we perform any DML operations like Insert, Update, or Delete on this Employee table or any table of SQL Server, at the end of the day, the data is stored in the MDF file which is the physical file in SQL Server. If you go to the Data Folder of MSSQL server (C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA) then you can find MDF file located in the data folder as shown in the below image. Here, you can find two files, one is Primary Data File (MDF) and the other one is a Transaction Log File (LDF).

This process is not that simple. In other words, it’s not that you just write a record or read a record from SQL Server, it directly goes to the MDF file and starts reading from there.

How SQL Server Reads and Writes Data internally?

Whenever you write a record or read a record, SQL Server actually writes it in an 8kb chunk size called a page. In other words, whenever you insert a row, it actually sends it to the 8kb chunk size or page. Whenever you read a record, it actually locates the page where your data is stored and read it from there. In order to understand this better please have a look at the following image.

So, whenever we perform any DML operations (Insert, Update, or Delete) on SQL Server tables, at the end of the day, the data get stored on the 8KB page.

SQL Server Pages type:

In SQL Server there are different kinds of data such as user data (the data which is stored on the database table) and configuration data or Metadata. So, based on the data (Configuration data or user data), in SQL Server there are different kinds of Pages. For example, the user-defined data will get stored something called Data Page. The system configuration data such as where your pages located, how much memory they allocated this type of configuration is stored under the IAM (Index Allocation Map) page. Index data gets stored on the Index Page. Like this, there is a total of 14 different types of pages in SQL Server. Please have a look at the following image to understand the different types of data and their respective pages.

In this article, we are not going to discuss all the data pages instead we are going to discuss two pages i.e. the Data Page (where the actual user data stored) and the IAM (Index Allocation Map) page. The IAM page has the information where all the SQL Server pages are stored.

Let us deep dive and see how this 8KB page looks like with an example:

Let us first insert one simple record called Record1 into the Employee and then let us try to locate this record in the 8KB page. Please the below SQL Script to insert a record into the Employee table.

INSERT INTO Employee VALUES (‘Record1’);

Now, I would like to locate this record (Record1) in the 8KB Page and let see how the page looks and where exactly this record store. In order to know, for the table Employee what kind of pages are used, we need to use Database Console Command (DBCC) as shown below.

Here, the above DBCC IND command will give you all the indexes including the pages (here -1 specifies the page (Data page, Index page, IAM page)) from the Employee table of the TestDB database. So, please execute the following DBCC command.

DBCC IND (‘TestDB’, Employee, -1)

Once you execute the above command, it will give you the following result which specifies that for the above Employee table there are two pages created by SQL Server.

Please have a look at the PageType column and you can see the values as 10 and 1. Here, 10 means it is the IAM page and value 1 means it is a data page. The IAM page has records that exactly tell you where exactly your data pages are.

So, what will happen, when you insert a record into the Employee table or when you want to select a record from the Employee table?

The first thing SQL Server will do is, it will query the IAM page and from the IAM page, it will get the data page where exactly the data is going to be stored or from where the data needs to be retrieved. Please have a look at the following image. The IAM (PageType 10) Page PagePID column value is 34 and this value is mapped with the Data Page (PageType 1) IAMPID column value (here the value is 34) and by linking this two columns, it will get the Data Page (PageType 1) i.e. PagePID column value (here the PagePID value is 232).

So, the Record1 (the only record of the Employee table) is stored on the data page 232. Now we will see the data page 232 and the record (Record1) inside it.

How we can see the page internal data?

Again we need to use the following DBCC command to see the internal data of a page.

First, we need to on the trace by executing the DBCC TRACEON (3604) command. On the Trace means we would like to see all the details of the page on the SQL Server Management Studio itself.

Next, we need to execute the DBCC PAGE (‘TestDB’, 1, 232, 1) command. This command tells you the details of the Page which is located in TestDB database, the page is located in file number 1 (this is the IAMFID column value of Data page) and the page number is 232 (this is the PagePID column value) and the last parameter specifies what details you want to see. If you specify the value as 1, then you will get an overall view and if you specify the value as 3, then you will get a more detail view.

So, please execute the following command in a new query window.

DBCC TRACEON (3604)
DBCC PAGE (‘TestDB’, 1, 232, 1)
GO

Once you execute the above command then it will give the following output. Here, the output is nothing but the full page details. If you scroll down the output, then you will see that the full page detail is divided into three sections. They are as follows:

1. Page Header
2. Data
3. OFFSET Table

Page Header:

The Page header tells you what kind of page it is. Please have a look at the following image which shows the Page Header data. As you can see the type is 1 which means it is a Data page. It also gives information about the next page and the previous page and so on. The page header size is fixed which is 96 bytes irrespective of the number of records.

Data:

The data section actually holds the actual data. Please have a look at the following image. If you remember the Employee table contains only one record i.e. Record1. It also specifies that it is a Primary record. Primary Record means it is actual data.

OFFSET Table:

Please have a look at the following image. The Row Offset tells you where exactly the record is stored. The record1 is lying on 060 memory dump which you can find in the data section. So, here the Row Offset saying there is one record in 0*60 memory dump. The Row Offset will take 2 Bytes per records.

Analyzing the Memory of SQL Server Data Page:

Now, as we understand the page is divided into three sections, now let us see and analyze each section gets how much byte.

Please have a look at the following image. Total Page size is 8KB which means (8 * 1024 ) 8192 Bytes. Currently, we have only one record in the Employee table. So, the number of records is 1. Each record size is 1000 bytes as the Employee table having only one column which is of a char data type with length 1000. The total records size is (1 * 1000) 1000 bytes.

Each record is 1000 bytes. Now, if you go to the Data section, then you will find that the actual record size is 1007 bytes as shown below. So, we have an extra 7 bytes.

So, let us add this extra 7 bytes to each record as shown below.

Each page is divided into three sections such as Page Header, Data (data rows) and the OFFSET table (Row Offset). Irrespective of the number of records, the Page header size is fixed and it is going to be 96 Bytes.

The Data row size is the number of records multiplies by the record size (1 * 1007) which is in our example1007 bytes.

The Row Offset takes 2 bytes per each record. As we have only one record, so the row offset will take 2 bytes. So, the total bytes free at the moment is (8192 – 1105) 7087 which is shown in the below image.

So, as per our calculation, the total free bytes are 7087. Now, go the Page header section and look at the m_freecnt value which is exactly 7087as shown below.

Add one more record:

Now add one more record into the Employee table by executing the following query.

INSERT INTO Employee VALUES (‘Record2’);

Once you insert the above record, now the memory calculation changes as shown below.

As you can see, the free bytes are 6078. Now, let us execute the following DBCC command to check the exact free bytes.

DBCC TRACEON (3604)
DBCC PAGE (‘TestDB’, 1, 232, 1)
GO

Once you execute the above command, now check the free bytes in the Page header section as shown below which shows 6078 as expected.

Now, you can also verify the new record (i.e. Record2) in the data section as well as row offset in the Offset Table section.

What happens when the 8KB page size exceeds?

Let us see this practically. Let us add 6 more records into the Employee table by executing the following insert statements.

INSERT INTO Employee VALUES ('Record3');
INSERT INTO Employee VALUES ('Record4');
INSERT INTO Employee VALUES ('Record5');
INSERT INTO Employee VALUES ('Record6');
INSERT INTO Employee VALUES ('Record7');
INSERT INTO Employee VALUES ('Record8');

Now, let us recalculate the free memory after inserting a total of 8 records into the employee table.

As you can see, the total free bytes are 24 bytes.

Now, let us add another record into the Employee table by executing the following insert statement.

INSERT INTO Employee VALUES (‘Record9’);

Once you insert the 9^th record into the Employee table, next execute the following DBCC IND command to see all the data pages.

DBCC IND (‘TestDB’, Employee, -1)

Once you execute the above DBCC command, it will give you the following output. As you can see we have three pages now. The IAM Page (PageType 10) is now pointing to two Data Pages (PageType 1).

So, when the 8KB Data Page is filled, then it creates a new Data Page. The new Data Page wills exactly the same structure i.e. having the same 96-byte page header, data rows and row offset.

In the next article, I am going to discuss Change Data Capture in SQL Server with examples. Here, in this article, I try to explain How SQL Server Store and Manages Data Internally in detail. I hope you enjoy this article. Please give your feedback, comments, and questions if you have in the comments section.