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, 2009 at 12:14 PM in Default

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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, 2009 at 01:08 PM

avatar image

Bob Hovious
1.6k 5 9 13

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, 2009 at 12:01 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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Oct 26, 2009 at 12:14 PM

Seen: 1502 times

Last Updated: Oct 26, 2009 at 12:14 PM

Copyright 2017 Redgate Software. Privacy Policy