Page 52 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes To access SQL definitions of system objects, use the OBJECT_DEFINITION function:
SELECT OBJECT_DEFINITION(OBJECT_ID(‘sys.objects’));
GO

4.2 Database Physical Structure

The topics in this section describe how SQL Server databases and files are organized.

The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data
file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to
n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole
data pages.

Extents are a collection of eight physically contiguous pages and are used to efficiently manage
the pages. All pages are stored in extents.

4.2.1 Pages

In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per
megabyte. Each page begins with a 96-byte header that is used to store system information
about the page. This information includes the page number, page type, the amount of free space
on the page, and the allocation unit ID of the object that owns the page.
The following table shows the page types used in the data files of a SQL Server database.

Table 4.1
Page type Contents
Data rows with all data, except text, ntext, image, nvarchar(max),
Data varchar(max), varbinary(max), and xml data, when text in row is set
to ON.
Index Index entries.
Large object data types:
text, ntext, image, nvarchar(max), varchar(max), varbinary(max),
Text/Image and xml data
Variable length columns when the data row exceeds 8 KB:
varchar, nvarchar, varbinary, and sql_variant
Global Allocation Map, Information about whether extents are allocated.
Shared Global Allocation Map
Page Free Space Information about page allocation and free space available on pages.
Information about extents used by a table or index per allocation
Index Allocation Map
unit.
Information about extents modified by bulk operations since the last
Bulk Changed Map
BACKUP LOG statement per allocation unit.
Information about extents that have changed since the last BACKUP
Differential Changed Map
DATABASE statement per allocation unit.

Notes Log files do not contain pages; they contain a series of log records.

46 LOVELY PROFESSIONAL UNIVERSITY