Chapter 5: Understanding System and User Databases

1
Chapter 5 Understanding System and User Databases
2
Chapter Overview
P121

• Understanding the Database Architecture
• Understanding the Transaction Log Architecture
• Understanding and Querying System and Database
  Catalogs

3
Data Files
P122-123

• One primary and optional secondary data files for
  each database.
• Each data file is a separate operating system
  file.
• Primary data file contains all system tables for
  a database.
• Database file locations are stored in the master
  database and in the primary data file for each
  database.
• Each file has properties, such as file ID,
  initial file size, growth increment (if any), and
  maximum file size.

4
Space Allocation
P124

• Data file space is allocated in extents and
  pages.
• Extents are either uniform or mixed.
• SGAM pages are used to locate allocated mixed
  extents with free space.
• GAM pages are used to locate unallocated extents
  for allocation to objects.
• IAM pages are used to locate allocated pages
  belonging to a table or index.
• PFS pages are used to locate allocated pages for
  an object with free space.

5
Index and Data Storage
P125

• Data is stored in a heap unless a clustered index
  organizes table data.
• Indexes store key values from a table in a B-tree
  structure.
• If a clustered index is present, inserts can
  require data page reorganization.

6
Structure of a Clustered Index
P126
7
Structure of a Nonclustered Index
P127
8
Transaction Log Files
P128

• Each database has one or more transaction log
  files.
• Each transaction log file is a separate operating
  system file.
• Transaction log files contain sequentially
  numbered log records containing database
  transactions.
• Each transaction log file and each growth
  increment contain two or more VLFs.
• Avoid too many VLFs by avoiding frequent small
  growth increments.

9
How the Transaction Log Works
P129

• Data modifications are written to the transaction
  log before the data file.
• Transaction log records contain sufficient
  information to roll database modifications
  backward or forward if necessary.
• Dirty pages are written to disk by worker
  threads, a lazywriter thread, or the checkpoint
  process.

10
Checkpoint Process
P129-130

• Ensures dirty pages in the memory buffer are
  periodically written to disk
• Writes all transactions older than MinLSN to disk
• Allows space in the transaction log to be reused
• Either truncates the transaction log or allows a
  transaction log backup to truncate the log

11
Log Truncation and Log Wrapping
P131
12
Operating System Threads and Dirty Pages
P131

• Ensure dirty pages in the buffer cache are
  periodically written to disk.
• Free memory buffer pages for reuse.
• Worker threads perform these tasks while waiting
  on their primary task.
• Lazywriter thread performs these tasks when free
  memory buffers run low.
• These two processes and the checkpoint process
  spread out disk writes and avoid disk bottlenecks.

13
Recovery Models
P132

• Full Recovery model
• Bulk-Logged Recovery model
• Simple Recovery model

14
System Tables
P134-135
15
System Table Query Methods
P136

• Directly using Transact-SQL
• Indirectly using system stored procedures
• Indirectly using system functions
• Indirectly using information schema views

16
Chapter Summary

• Microsoft SQL Server databases consist of data
  files and transaction log files.
• Data files are organized using pages and extents,
  and specialty pages track allocated pages and
  extents.
• Clustered indexes order data otherwise, data is
  unordered.
• Transaction logs ensure data consistency and
  provide levels of recoverability based on the
  recovery model.
• System tables record system and database object
  information.

17
Lab

• Lab 3 Viewing Data File and Transaction Log File
  Properties
• 15 minutes