SQL Server 2000 Architecture and Allocation

1
SQL Server 2000Architecture and Allocation

• Don Vilen
• Program Manager
• SQL Server Development Team

2
Agenda

• SQL Server Overview
• SQL Server Architecture
• Storage and Access Methods
• Query Processing and Optimization
• Transaction Processing
• Other Topics

3
SQL Server Architecture
4
Server Architecture
TDS RPC Messages become ODS RPC Events
Subsystems call ODS to format return TDS messages
TDS SQL Messages become ODS Language Events
Open Data Services
Parser
T-SQL Compiler
Query Optimizer
SQL Manager
T-SQL Execution
Expression Service
ES works with ODS to format resultset
Normalizer
Query Execution
Not code, but logically part of server
functionality
Relational Engine
System Stored Procedures
UMS
Includes Pseudo System Stored Procedures
Catalog Services
Storage Engine
5
SQLManager

• Drives Compilation and Execution
• Pseudo-System Stored Procedures
• Procedure Cache Management

RPC Messages
Called by Parser to try Auto-parameter-ization
Called by Parser when it sees an Exec Proc
6
Compilation/Optimization

• Batch is parsed and normalized
• Statements are compiled
• Queries are optimized

7
Execution

• Executes compiler/optimizer plan
• Manages per-query resources
• Drives the Storage Engine

8
Storage Allocation
9
Storage Allocation

• Files
• Filegroups
• Pages
• Extents
• Allocation Tracking
• GAM Global Allocation Map
• SGAM Shared Global Allocation Map
• PFS Page Free Space
• IAM Index Allocation Map
• Round Robin Proportional Fill

10
Files 1 of 2

• A database is a collection of OS files
• Files are not shared between databases (or
  instances)
• Each database must have at least one data file
  and one log file
• Up to 32,767 files per database
• File types
• Primary Data File - .mdf One per database
• Catalog table sysfiles1 must be completely
  contained in this file
• Other catalog tables can reside elsewhere in
  PRIMARY filegroup
• Secondary Data File - .ndf zero or more
• Log File - .ldf One or more per database
• Contains transaction log used for recovery
• Files are tracked in sysfiles1 system table
  within the .mdf
• .mdf is tracked in master..sysdatabases table
• All are tracked in master..sysaltfiles for
  emergency use

11
Files 2 of 2

• Initial Size, Maximum Size, and Growth Amount can
  be specified
• Files can grow automatically by fixed amount or
  a percentage of current size
• Files can be shrunk using DBCC ShrinkFile and
  ShrinkDatabase
• Files must be local to server machine
• See KBA Q196904 INF Support for Network
  Database Files for use of trace flag 1807 to
  allow network files
• Drive can be raw, NTFS, or FAT, but can not be
  compressed
• Using a compressed volume can lead to loss of
  data
• Data and log files (and .MDF) should be on
  different drives
• As before, consider mirroring the MDF for safety

12
Filegroups

• Files can be associated in named groups for
  allocation and administration
• Groupid 1 is PRIMARY
• Always exists in every database
• Contains databases system tables
• Log files do not join filegroups
• Filegroups are created with CREATE DATABASE and
  ALTER DATABASE
• Up to 256 filegroups per database (though groupid
  column is a smallint)
• Filegroups can be backed up individually but
  special rules apply
• A filegroup can contain many files a file can
  belong to only one filegroup
• A filegroup can contain many objects an object
  exists on only one filegroup
• CREATE TABLE and CREATE INDEX have an ON-clause
  to specify filegroup
• A tables nonclustered indexes and text/image can
  be separate from the data
• To spread data across disks, RAID should be the
  primary consideration. Filegroups should be a
  secondary consideration

13
Pages

• Data files are divided into 8KB pages
• All pages have a similar structure with header
• Page numbers start with 0 in every file
• Page number is (filepage) like (3124)
• .mdf is always file 1 first log file is file 2
• DB..sysfiles.fileid
• Unique within the database
• Log files do not have this same structure

14
Basic Page Structure

• All pages contain a Header, Body, and Slot Array

Page header 96 bytes (64 bytes in use, 32
reserved)
Page Header
Body containing data rows
8,096 bytes
A row offset table called a slot array
15
Extents

• 8 contiguous Pages is an extent 64 KB
• Always start with a page divisible by 8
• First extent in File 1 contains pages 0 to 7
• Known as Extent 0
• Second extent contains pages 8 to 15
• Known as Extent 8
• Allocation is usually done by extents
• Object grows by 1 extent, 8 pages, 64 KB
• I/O can be by page (8 KB) or by extent (64 KB)

16
Extents

• Uniform Extents
• Contain 8 pages from a single object
• Mixed Extents
• Can contain pages from up to 8 objects
• An objects first 8 pages come from mixed extents

Extent (8 pages 64K)
An 8K page
Mixed Extent
0
8
16
24
32
40
T1
T2
T1
T3
T4
T3
T4
17
GAM Page Global Allocation Map

• A page that tracks which extents in the file have
  been allocated
• Almost all of the data portion of the page is a
  huge bitmap of almost 8000 bytes
• Each bit represents one extent
• Bit 0 for Extent 0 bit 1 for Extent 8 2 for
  Extent 16
• 0 In use
• 1 Available
• Almost 64,000 bits, so can track about 64,000
  extents
• Tracks about 4 GB (3,994 MBs)
• Additional GAMs added if file larger than 4 GB
• First GAM is page 2 of the file
• The Next GAM Is On Page 511,232
• Always on first page of the range it controls

18
SGAM Page Shared Global Allocation Map

• First few pages of an object come from shared
  extents to avoid over allocation
• SGAM is a page that tracks which extents in the
  file have been allocated as a shared extent and
  still have a page available for allocation
• Structure is the same as for a GAM page
• Each bit represents one extent
• Bit 0 for Extent 0 bit 1 for Extent 8 2 for
  Extent 16
• 0 Extent is either not a shared extent or a
  shared extent with no pages available
• 1 Extent is a shared extent and has a page
  available for allocation
• Tracks about 4 GB (3,994 MBs) same range as the
  corresponding GAM
• Additional SGAMs added if file larger than 4 GB
• First SGAM is page 3 of the file
• The Next GAM Is On Page 511,233
• Always on second page of the range it controls

19
PFS Page Page Free Space

• Tracks which pages have been allocated
• PFS page has one byte per page
• Tracks allocated and amount of space available
• About 8,000 bytes per page
• Tracks about 8,000 pages
• One PFS page every 64 MB or so
• First PFS page is on page 1 of the file
• Tracks page 0 to about page 7,999
• Next PFS page is on the first page of its range

20
IAM Page Index Allocation Map 1 of 2

• So far we have seen allocation from the system
  allocation side, but how are the extents and
  pages found for a specific object or index?
• Every table / index has at least one IAM page
• Tracks which extents are owned by this object
• IAM has the same format as GAM / SGAM, but
• Covers the same range as a GAM
• First 8 page pointers from shared extents are
  stored in the Single Page Allocation array on the
  first IAM page for the object
• An object will have one IAM page for every 4 GB
  portion of a file that contains an extent
  allocated to it
• The IAM page itself is not necessarily in the
  file or GAM range

21
IAM Page Index Allocation Map 1 of 2

• IAMs for an object are randomly placed (in mixed
  extents) in the files in the objects filegroup
  and in a doubly-linked list
• sysindexes.firstIAM points to first IAM for the
  object
• IAM chain can be a cross-file structure
• To find first page for an object
• Follow sysindexes.firstIAM to IAM, find an extent
  allocated to the object, then use PFS to see if
  page is allocated
• This is the first page of the object in GAM range
• Need another method if there is some other
  ordering

22
Allocation Round Robin Proportional Fill

• At database startup the number of free extents is
  determined for each file in a filegroup
• Extents are allocated from each file so that all
  files in the filegroup will fill at approximately
  the same rate
• Some number of extents are allocated from file1,
  then some number from file2, file3, then back to
  file1 Round Robin
• Distributes IOs across files
• File in filegroup will only grow if no space left
  in filegroup

23
Extent Allocation

• Extents are allocated proportionally from all
  files

12 extents free
4 extents free
24
Questions?