Tuning the Schema - Professional SQL Server 2005 Performance Tuning - page 221

Data Performance
The performance of data simply means how well the data will perform under a load. Obviously, you
want to have as much performance as possible. This section will cover pages, partitioning, indexing, and
other topics that have a great impact on data performance.
Pages
Most everything covered so far culminates here with a discussion about pages. SQL Server 2005 utilizes
eight different page types; here we'll only discuss data pages. Intimately understanding data pages is a
key for optimizing performance. The reason it's key is that the page is the fundamental unit of storage
within SQL Server. Disk I/O occurs at the page level. SQL Server's locking mechanism defaults to the
page level.
DataPages
Data pages will be described first. These pages contain all the data, with the possible exception of the
large value data types, described earlier, which may be stored on Text/Image pages (described later).
Data pages are 8 KB (8,192 bytes) in size. The data page is divided into three main sections. The first
section is a 96-byte header, which contains information about the page. The next section contains the
data rows themselves. The final section is a row offset array (see Figure 8-18).
Page header. 96 bytes
Data rows
Data row offset.
Figure 8-18
Page Headers
Table 8-4 describes the content of the page header.
Data Rows
The table's actual data rows are next on a page. A single data row can't be more than 8,060 bytes. A data
row doesn't usually span a page. The exception to this is the large value data types. Those elements can
be relegated to other pages. The number of rows stored on a given page will vary.
A table that doesn't have any variable-length columns will store the same number of rows on each page,
assuming the pages are fully packed. However, a table that has some variable-length columns will likely