Pages are essential building units of every SQL database we deal with. In order to design an efficient database, understanding how data will be stored in a database is a must. This article elaborates on the definition of the SQL Server page and introduces us to different page types within SQL Server. It also gives us an insight into the data storing process within SQL Server and how SQL Server paging is related to it.
What is a SQL Server Page?
SQL Server page is a basic unit for data storage. When we create a database, we actually create a database file. SQL Server allocates particular disk space to our database file. This disk space is logically divided into SQL Server pages that contain our data. Those pages are numbered from 0 to n, in regards to the database size.
What is the SQL Server Page Size?
The SQL Server page size is 8KB. Knowing this, we can easily obtain the number of pages per database. For example, the following steps will guide us through the calculation of the number of pages of a database that is 100MB large.
Step 1. 1MB equals to 1024KB. Therefore, if one SQL Server page is 8KB in size, the number of pages that fits into 1MB is 1024KB / 8KB = 128.
Step 2. Since our database is 100MB in size, the total number of pages for such a database would be 128 * 100 = 12800 pages.
What is the Structure of the SQL Server Page?
A SQL Server page consists of the following elements:
- Page header – placed at the top of the page. The header contains the information like page number, page type, the amount of free space within the page, and the ID of the allocation unit holding the object that occupies the page.
- Data rows – data rows from a particular database table.
- Row offsets – placed at the bottom of the page. It holds one entry for each data row within the page.
What are the Page Types in SQL Server?
There are 8 different page types that are supported within the SQL Server:
- Data – stores data rows that hold all kinds of data excluding text, ntext, varchar(max), nvarchar(max), varbinary(max), image, and xml.
- Text/Image – stores data rows consisting of data that is larger than normal data. It includes the following data types: text, ntext, varchar(max), nvarchar(max), varbinary(max), image, and xml.
- Index Allocation Map (IAM) – stores addresses of database tables
- Global Allocation Map (GAM) and Shared Global Allocation Map (SGAM) – stores information about the allocation of the extents
- Page Free Space (PFS) – stores information about the page allocation, as well as free space within the page
- Bulked Changed Map (BCM) – stores information related to extents that have been changed by bulk operations since the last BACKUP LOG statement executed on an allocation unit
- Differential Changed Map (DCM) – stores information related to extents that have been changed since the last BACKUP DATABASE statement executed on an allocation unit
Paging in SQL Server
As a regular database developer, we will hardly ever deal with SQL Server pages directly. That is because most of the time, we actually manipulate data stored in database objects such as tables. Nonetheless, for query optimization purposes, it is good to know how SQL Server stores the data.
Paging is the process of taking a set of data and storing it in a number of SQL Server pages. It is automatically done in every database we deal with. After we create a database, we will create some tables where we will store our data. Each of the tables will have a particular amount of pages allocated. The amount of pages that will be allocated to each table depends on the size of the data that will be stored within a particular table. The more data we have, the more pages will be created by SQL Server.
Depending on a database configuration property called fill factor, a page could be set to be initially populated in two ways:
- Entirely – if a fill factor is set to 0 or 100. In this case, the entire page will be populated before another one gets created.
- Partially – if a fill factor is set to some other value than 0 or 100, in this case, only the percentage of the page defined by fill factor will be populated. The rest will stay empty for some later use cases. For example, if we set the fill factor to be 70, a page will be 70% populated. When we insert a new data row, a new page will be created, even though the previous one still has some space. This space is reserved for operations like an update that could cause fragmentation, which could be very expensive to perform.
On the other hand, not every page holds the same amount of table rows. That depends on the type of data being stored in a table row. If a table row consists of some numerical and binary values, a page will be able to hold more of such rows. The reason is that such data is not so heavy as string or float data.
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.