Getting the Most Out of SQL Server Pages

BANGALORE, INDIA: Providing data driven solutions does not end with writing queries. Rather, writing queries is only the beginning. For you to provide solutions to your customers, you would need to understand how your database server provides you with solutions.

Microsoft SQL Server arranges data in your database in files, and the 'page' is the fundamental unit of data storage for SQL Server. Understanding the architecture of pages is important for designing and developing data driven solutions that perform effectively. Here, we describe the architecture as it applies to Microsoft SQL Server 2008 (Note that the page architecture has not changed significantly since version 7.0).

Every database has a name and an ID, and so has every file. Run these statements to find out for yourself.

SELECT 'The name of this database is: ' + DB_NAME();

SELECT 'The id of the database ' + DB_NAME() + ' is: ' + STR(DB_ID());

SELECT 'Here are the files that make the database: ';

SELECT * FROM sys.database_files;

The disk space allocated to a data file in a database is logically divided into pages. In SQL Server, the page size is 8 KB. Within each file the pages are numbered contiguously from 0 to x. The value of x would depend on the size of the file. You can refer to any page by specifying the database ID, the file ID and the page number.

The space in a database is used for storing tables and indexes. Data rows of a table or index are put on the page serially. Every page begins with a page header. The 96-byte header is used to store system information about the page. This information includes the page number, the amount of free space on the page, and some other details.

Data rows start immediately after the header.

A row offset table starts at the end of the page. The row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.

So far we discussed about pages on the disk. A buffer is an 8-KB page in memory. It is the same size as a data or index page. The main memory component in SQL Server is the buffer cache, which is also called as the buffer pool. The buffer cache is divided into 8-KB buffer pages. The database engine requests one or more pages from the data cache when you execute a query to read data from the database. There are two possibilities:

1. The corresponding page is not available in the buffer cache. A physical read copies the page from disk into the cache.
2. The corresponding page is already available in the buffer cache because of a previous physical read. In this case the current read is only a logical read.
   
   The space on the disk is managed in units called extents. An extent is made up of eight logically contiguous pages, which is 64 KB of space. To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data.

SQL Server has two types of extents:

1. Uniform extents are owned by a single object. All eight pages in the extent can only be used by the owning object.
2. Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.

Amaresh Patnaik