PAGES/EXTENTS

PAGES/EXTENTS:

1. What is stolen page?

Stolen pages are used store miscellaneous pages like proc cache etc,

2. What is the boot page?

In a database the primary file holds the critical information in a page called the boot page. It is the 9th page of the primary file in the PRIMARY file group

3. Explain the following:

a.) Dirty pages.
These are the buffer pages that contain modifications which have not been written to disk.

b.) ETL - Extraction, Transformation, and Loading.
- It is the process of copying and cleaning data from heterogeneous sources.
- It is an important part of development projects for data warehousing and business intelligence.

4. How pages are arranged in data file?

File Header>PFS>GSM>SGAM>BCM>DCM>Data and Index pages

What are page, extents, page header and row offset in SQL Server?

Extent and pages define how data is stored in SQL Server. The actual data is stored in pages. Each one of these pages are of 8 KB size. You can also visualize pages as the fundamental unit to store data. In other words your table row data gets stored actually in pages.

Pages are further grouped in to extent. One extent is collection of eight pages. So the size of each extent is 64 KB (i.e. 8 pages x 8 KB size).

Finally these extents sum up in to the MDF physical file which you see on your hard disk

Now in reality the complete 8 KB size is not allocated only to pure end user data.

Page is divided in to 3 sections Page header, Data row and row offset, see the below figure for visual's.

 

Page header stored system information about the page. After the page header data row is stored serially. Row offset information is stored at the end of the page, after the data rows. Every data row has a row offset. Row offset stores information about how far the row is from the start of the page.

Putting in simple words the complete page equation comes as shown below.

Page (8 KB/8192 bytes) = Page header (96 bytes) + Actual data (8060 bytes) + Row offset (36 bytes).