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

store a different number of rows on each page. The idea to keep in mind when designing a table is to
make the pages hold as many rows as possible. Keeping row lengths as short as possible is the way
to achieve this goal. I refer to the number of rows per page as page density. The higher the density, the
better. That's because more rows per page translates into fewer pages. That means that disk I/O can be
reduced and the cache-hit ratio should improve.

Table 8-4: Page Header Content Details

Column Purpose

pageID File and page number of the current page in the database.

nextPage File and page number of the next page, if the current page is part of a chain of pages.

prevPage File and page number of the previous page, if the current page is part of a chain of

pages.

objID ID of the object to which the page belongs.

lsn Log sequence number (LSN) value used for changes and updates to the page.

slotCnt Total number of slots used on the page.

level Level of the page in an index (always 0 for leaf pages).

indexId Index ID of the page (always 0 for data pages).

freeData Byte offset of the first free space on this page.

Pminlen Number of bytes in fixed-length portion of rows.

freeCnt Number of free bytes on page.

reservedCnt Number of bytes reserved by all transactions.

xactreserved Number of bytes reserved by the most recently started transaction.

tornBits 1 bit per sector for detecting torn page writes.

flagBits 2-byte bitmap that contains additional information about the page.

Data Row Structure
There's more to a data row than just the data. Each row has its own collection of ''housekeeping'' items.
Table 8-5 provides the general structure of a data row.

Note that for a table that only has fixed-length columns the last three segments won't exist.

Page Row Offset Arrays
The row offset array is a series of 2-byte entries. Each entry indicates where on the page the corresponding
data row begins, and every row has an entry. Even though these bytes aren't stored with the data row,
they do play a part in determining how many rows will fit on a page.