x

What is a page?

Seeder Question: I have seen a lot of references to 'pages' when reading information about SQL Server - what exactly is a page, and how does it relate to how my databases work?

more ▼

asked Oct 26 '09 at 12:14 PM in Default

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

A page is the fundamental unit of storage in MS-SQL, used by the database engine to manage I/O from disk to RAM and back. Currently the page size is 8k. In other words, whenever data is read from the hard drive or written to the hard drive it is done in 8k increments. Pages are grouped into extents, which are the are the basic unit in which space is allocated to tables and indexes. Each extent consists of 8 pages or 64k.

As a practical matter, we always seek to minimize phsical I/O operations because they take place at mechanical speed, while logical operations in memory run at much faster electronic speed.

Whenever the rows of a result set can be found on the same page, they can be processed optimally, because only one physical I/O operation will be necessary. After the page is in memory, all reads will be fast logical reads from row to row, in memory. Similarly if rows being updated or inserted all lie on the same page, only one physical write is required.

This leads to a number of design considerations. First, we want keep rows as small as possible, so that more rows can fit on a single page. Among other things, this involves normalisation to omit unnecessary columns and selection of appropriate datatypes that take up less space in memory.

Second, some thought must be given to the sequence of the data when it's being retrieved. A classic example is that of an order with a number of line items. A very common way of retrieving this data is to get all line items for a specific order number. If the clustered index is created for the an OrderDetail table on (orderID, lineItemID), the rows of a particular order are more likely to fall together on a single page, or at worst adjacent pages. "Covering" indexes may also be created to group rows logically to satisfy commonly used queries.

However, this grouping can also slow performance down as the pages become fragmented. Fragmentation occurs when a row has to be inserted into a page which is already full, or when an update to a row makes the row too large to fit in the available space on a page.

In such a situation, the page is split into two different pages, so that empty space is available for the new or updated row. This requires additional work by the system to perform the insert or update. This also means that following the insert/update there will be fewer rows per page on average, therefore more physical I/O is required.

For this reason, tables and indexes must be periodically reorganized or rebuilt when the fragmentation percentage gets too high.

more ▼

answered Oct 26 '09 at 01:08 PM

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

This sort of answer is why you're going to be the first to the Gold badge - superb...
Oct 26 '09 at 01:20 PM Matt Whitfield ♦♦
Nice answer, though I'd define a page as the basic level of storage in SQL Server. In previous versions, the page size was 2k. In future versions, it could possibly be larger. Note also that pages are organized into groups, called extents.
Oct 27 '09 at 12:01 AM Steve Jones - Editor ♦♦
Good points, Steve. I'll go edit the original.
Oct 27 '09 at 09:24 AM Bob Hovious
Good edits. I think you've covered it very well.
Oct 27 '09 at 10:33 AM Steve Jones - Editor ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x977
x29

asked: Oct 26 '09 at 12:14 PM

Seen: 914 times

Last Updated: Oct 26 '09 at 12:14 PM