SQL Data Page Structure

A page in SQL Server is always 8kb in size, and there are different types of pages – data pages, index pages, system pages, etc. Today we take a more detailed look at data pages, where SQL Server stores our table data. A data page always consists of 3 parts:

  • Page Header
  • Payload
  • Row Offset Array

The page header is always 96 bytes long in SQL Server (independent of the type of the page), and stores general information like the Page ID, Object ID, etc. The most interesting part of the data page is the payload area, because our records are stored in that area. SQL Server gives you from the 8192 bytes (8kb), 8096 bytes for the payload section. Therefore, it’s an easy task to calculate how many rows for a given table fit on a page – just divide 8096 by the record size (incl. the internal overhead of at least 7 bytes). If you round down the result, you have the number of records you can store on a data page.

The goal is always to have as many records as possible on a page because SQL Server must read and write complete pages. SQL Server can’t read a part of a page from your storage or write part of a page out to your storage. I/O operations are always done (at least) on a page level.

And finally at the end of the page you have the so-called Row Offset Array. The Row-Offset Array just stores with 2 bytes for every record the offset on the page at which the record is located. The first record always begins at the decimal offset of 96 – directly after the page header. The following picture gives you an overview about the described structure of the data page.

Data Pages

Data Page Internals

Let’s have a look at a simple table definition, like the following one:

CREATE TABLE Customers
(
   FirstName CHAR(50) NOT NULL,
   LastName CHAR(50) NOT NULL,
   Address CHAR(100) NOT NULL,
   ZipCode CHAR(5) NOT NULL,
   Rating INT NOT NULL,
   ModifiedDate DATETIME NOT NULL,
)
GO

With such a table definition it’s now very easy to calculate how many records we can store on one page. The size of a record is here 224 bytes long (50 + 50 + 100 + 5 + 4 + 8 + 7). When you now divide 8096 by 224, you get 36.14, which means you can store 36 records of that table on one data page. The other remaining space of the table – in our case 32 bytes (8096 – 224 * 36) are just waisted, because a data page always belongs to a specific database object and can’t be shared amoung other objects. In the worst case, when your table definition has a record size of 4031 bytes, you are wasting 4029 bytes on every page. Things will change here when you introduce variable length data types, like VARCHAR, because SQL Server is then able to store these columns on different pages.

If you want to know how much space on your pages is wasted by your table design, you can query the buffer pool through the Dynamic Management View sys.dm_os_buffer_descriptors. Every record from this DMV represents one page that you are currently storing in the buffer pool, so please be aware of this, when you are querying this DMV on machines with a larger amount of RAM. The column free_space_in_bytes tells you how much space is currently free on the specific page. The following query returns you, how much space is wasted by every database on your SQL Server instance.

SELECT
   DB_NAME(database_id), 
   SUM(free_space_in_bytes) / 1024 AS 'Free_KB'
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767
GROUP BY database_id
ORDER BY SUM(free_space_in_bytes) DESC
GO

This is always a query that I’m running on a system to find out which database might have a bad table design.

Summary : I hope that I have given you with this blog posting a better understanding about data pages in SQL Server, and why they are important for performance tuning. As you also have seen, you can directly influence how many data pages a given table needs by concentrating on the table design.

SQL Database restore types

Full Restore:
This is the most basic type of restore and involves restoring an entire database from a full backup file. It overwrites the existing database with the restored data. A full restore is typically used in disaster recovery scenarios when the database has been completely lost or corrupted.

Differential Restore:
It restores a backup of the changes made to the database since the last full backup. It requires a full backup to be restored first and can be used to reduce the recovery time of a database.

Transaction Log Restore:
It restores the transaction log backups made since the last full or differential backup. It can be used to recover the database to a specific point in time.

File Restore:
It is used to recover a single file or filegroup from a backup. It can be used to recover a single table or index, rather than the entire database.

Page Restore:
A page restore is used to recover a single page of data from a database backup. It can be used to recover a damaged or corrupt page.

Piecemeal Restore:
It is used to restore a database in stages. This can be useful in situations where only part of the database is lost or corrupted. It involves restoring the necessary backup files in a specific order to bring the database back to a consistent state.

Online Restore:
It allows a database to remain available for users during the restore process. It is achieved by restoring the database backup to a separate instance of SQL Server and then using log shipping or database mirroring to apply the transaction logs to the original database.

Partial Restore:
It is used to restore only a subset of a database, such as a filegroup or a read-only filegroup. It requires a full backup to be restored first and can be useful in situations where only part of the database is lost or corrupted.

Point-in-time Restore:
A point-in-time restore is used to restore a database to a specific point in time, rather than just the last transaction log backup. It requires a combination of full, differential, and transaction log backups to be restored in a specific order.

Tail-log Backup and Restore:
It captures any transactions that occur after the most recent transaction log backup. A tail-log restore is used to apply these transactions to the restored database after it has been recovered from a backup.

Very important concepts to understand with respect to your DBA interview as well. Now, if you are now confused among most of these, please read my next article detailing about the scenarios when to restore which one!

SQL Always on interview questions

1.What are the prerequisites for setting up AlwaysOn?

2.How do you set up replication in AlwaysOn environment?

3.How do you manage replication during Windows patching or failover if replication has been set up in AlwaysOn?

4.How do you sync logins in AlwaysOn?

5.How do you sync users in AlwaysOn secondary?

6.How do you add database files in AlwaysOn?

7.How do you perform an in-place upgrade of SQL Server in a AlwaysOn environment?

8.What is the procedure for SQL Server patching in AlwaysOn?

9.How do you failover a mirror server if replication has been set up?

10.What is the SPN concept in AlwaysOn?

11.What is file share in AlwaysOn?

12.How do you create multiple AlwaysOn listeners?

13.How do you check latency in AlwaysOn?

14.What is the command used to check latency in replication without using GUI?

15.What are DNS issues in AlwaysOn?

16.If a user is connecting to the primary and not able to connect to the secondary, and the secondary is in read-only mode, how do you fix the issue in AlwaysOn?

17.How do you fix if secondary is not in sync?

18.How do you apply TDE in AlwaysOn?

19.How do you add the databases to availability group when encryption is enabled?

20.How to check the health of AlwaysOn?

21.How to resolve if blockings occur in secondary replica due to reporting server?

22.What are the DMVs used to troubleshoot AlwaysOn?

23.How do you set backup priority in AlwaysOn?

24.How do you restore the database which is part of AG ?