by May 1, 2009 0 comments

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).

Direct Hit!

Applies To: Database developers
USP: Learn how architecture of SQL pages can be used to mold
data driven solutions
Primary Link: None
Keywords: SQL Server Pages

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
    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

No Comments so far

Jump into a conversation

No Comments Yet!

You can be the one to start a conversation.