Chapter Overview

1. Chapter Overview • Understanding the Database Architecture • Understanding the Transaction Log Architecture • Understanding and Querying System and Database Catalogs

2. Data Files • 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.

3. Space Allocation • 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.

4. Index and Data Storage • 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.

5. Structure of a Clustered Index

6. Structure of a Nonclustered Index

7. Transaction Log Files • 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.

8. How the Transaction Log Works • 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.

9. Checkpoint Process • 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

10. Log Truncation and Log Wrapping

11. Operating System Threads and Dirty Pages • 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.

12. Recovery Models • Full Recovery model • Bulk-Logged Recovery model • Simple Recovery model

13. System Tables

14. System Table Query Methods • Directly using Transact-SQL • Indirectly using system stored procedures • Indirectly using system functions • Indirectly using information schema views

15. 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.