Getting the Most Out of SQL Server Pages

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 stores its data at the operating system level in the form of

one or more data files, which by convention have an extension of .mdf or .ndf.

For example, this is what I see when I right click on one of my databases in SQL

Server Management Studio, after clicking on Properties, and then selecting the

Files tab.

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.

Click on Properties and then select the Files tab. Right click on one of the databases in SQL Server Management Studio to get all relevant details.

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.

You can see that every database has a name and an ID, and so has every file. Plus, you can see all relevant 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:

You can see that every database has a name and an ID, and so does each file. Plus, you can see all relevant details in an organized manner.

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