Home • About • Contact
Login • Page Content • Site Map

Search
GH.com   GH.com dir
Wikipedia   Web

• Intro
• File Group
• File
• Extent
• Page

Intro

The data in each database in SQL Server is stored with this structure:

• File Group
  • File
    • Extent
      • Page

To maximize performance via parallel reads and writes, either distribute file groups, files, or objects (tables or indexes) on different drives, or use an appropriate RAID. Members of the db_owner group can manipulate the physical file structure. Databases can be detached from one server and attached to another by the physical files.

File Group

A file group is actually a logical division of the files that compose the database.

• A file group or file can only be used by one database.
• Each file must belong to one file group.
• Only one file group can be the default file group where newly created objects go to by default.
• Log files are never part of file groups.
• A file group can be made read-only, usu. for archival purposes.

File

Each database has at least two files: A primary data file (*.mdf) and a log file (*.ldf). If a database has additional files, then those are classified as secondary data files (*.ndf). Each file has a logical name (system name used in T-SQL statement) and a physical name (file name used by the OS).

Extent

An extent the basic allocated to tables (aka heaps in this context) and indexes. An extent is composed of 8 contiguous pages. If all the pages of an extent describe the same object, then the extent is uniform. If the pages describe one or more objects, then the extent is mixed. This combo of uniform and mixed extents, plus the various tracking pages, allows SQL Server to make efficient use of hard disk space.

Page

A page is the smallest unit in SQL Server. A page is a unit of 8 KB. A page can only describe one object. Pages are numbered sequentially.

• Header . The 96 B header has IDs (for object, file,  page, etc.) and pointers to the previous and next pages.
• Body . The body has the data. The end of the body has one entry for each row of data in the page to demarcate the the position of the start of the row on the page.

There are eight kinds of pages in SQL Server:

• Data pages
  • Data. Data rows except for these data types: text, ntext, and image .
  • Text/Image. Data rows for these data types: text, ntext, and image .
  • Index. Table index info.
• Tracking pages
  • GAM/SGAM (Global Allocation Map/Secondary GAM). Each covers 64,000 extents. Using this logic:

    Current use of extent	GAM bit setting	SGAM bit setting
    Free, not in use	1	0
    Uniform extent, or full mixed extent	0	0
    Mixed extent with free pages	0	1

  • PFS (Page Free Space). Covers 8,000 pages and know how much free space is on each.
  • IAM (Index Allocation Map). There is one or more IAM pages per object per file. This is how SQL Server tracks an object that may be distributed amongst multiple files. An IAM denotes a range and where the previous and next IAM section is.
  • BCM (Bulk Changed Map). Each covers 64,000 extents. A bit with a 1 value indicates that the extent was modified by bcp since last BACKUP LOG statement.
  • DCM (Differential Changed Map). Each covers 64,000 extents.  bit with a 1 value indicates that the extent was changed since last BACKUP LOG statement.

Pages appear in this order in each file. The PFS pages appear more frequently since they only cover 8,000 pages where as pages such as GAM cover 64,000 extents or 512,000 pages. Note that IAM pages appear randomly as needed.

1. File Header Page
2. PFS
3. GAM
4. SGAM
5. BCM
6. DCM
7. Data, Text, and Index
8. PFS
9. Data, Text, and Index
10. ...
11. PFS
12. GAM
13. SGAM
14. BCM
15. DCM
16. Data, Text, and Index
17. ...

Page Modified: (Hand noted: 2007-10-13 16:10:20Z) (Auto noted: 2007-11-17 06:43:47Z)