Inside Microsoft® SQL Server™ 2005, Fourth Edition

Inside Microsoft® SQL Server™ 2005, Fourth Edition

by Kalen Delaney
Released October 2006
Publisher(s): Microsoft Press
ISBN: 9780735621053

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

This practical, hands-on book offers deep, thorough coverage of the internals of architecture and resource management in SQL Server 2005, focusing on the Storage Engine. The book features extensive code samples and table examples.

Table of contents

  1. Inside Microsoft® SQL Server™ 2005: The Storage Engine
  2. Foreword
  3. Acknowledgments
  4. Introduction
    1. A History of Inside Microsoft SQL Server
    2. Series Structure
      1. Inside Microsoft SQL Server 2005: T-SQL Querying
      2. Inside Microsoft SQL Server 2005: T-SQL Programming
      3. Inside Microsoft SQL Server 2005: The Storage Engine
      4. Inside Microsoft SQL Server 2005: Query Tuning and Optimization
    3. Examples and Scripts
    4. Topics Not Covered
    5. Caveats and Disclaimers
    6. How to Get Support
      1. Companion Web Site
      2. Microsoft Learning
  5. 1. Installing and Upgrading to SQL Server 2005
    1. SQL Server 2005 Prerequisites
      1. SQL Server 2005 Editions
      2. Software Requirements
      3. Hardware Requirements
        1. Processors
        2. Disks
        3. Memory
    2. Pre-Installation Decisions
      1. Security and the User Context
      2. Characters and Collation
      3. Sort Orders
        1. Sort Order Semantics
        2. Binary Sorting
        3. Specifying a Collation
        4. Performance Considerations
      4. Installing Multiple Instances of SQL Server
      5. Installing Named Instances of SQL Server
    3. Getting Ready to Install
      1. SQL Server 2005 Upgrade Advisor
    4. To Migrate or Upgrade?
      1. Migrating
      2. Upgrading
        1. Upgrade Internals
        2. Post-Upgrade Operations
    5. Selecting Components
      1. SQL Server Database Services (Database Engine)
      2. Analysis Services
      3. Reporting Services
      4. Notification Services
      5. Integration Services
      6. Workstation Components, Books Online, and Development Tools
    6. Summary
  6. 2. SQL Server 2005 Architecture
    1. Components of the SQL Server Engine
      1. Observing Engine Behavior
      2. Protocols
        1. Tabular Data Stream Endpoints
      3. The Relational Engine
        1. The Command Parser
        2. The Query Optimizer
        3. The SQL Manager
        4. The Database Manager
        5. The Query Executor
      4. The Storage Engine
        1. Access Methods
          1. The Row and Index Operations
          2. Page Allocation Operations
          3. Versioning Operations
        2. Transaction Services
          1. Locking Operations
        3. Other Operations
      5. The SQLOS
        1. NUMA Architecture
        2. The Scheduler
          1. SQL Server Workers
          2. SQL Server Schedulers
          3. SQL Server Tasks
          4. Threads vs. Fibers
          5. NUMA and Schedulers
          6. Dynamic Affinity
          7. Binding Schedulers to CPUs
          8. Observing Scheduler Internals
            1. sys.dm_os_schedulers
            2. sys.dm_os_workers
            3. sys.dm_os_threads
            4. sys.dm_os_tasks
            5. sys.dm_os_waiting_tasks
        3. The Dedicated Administrator Connection
    2. Memory
      1. The Buffer Pool and the Data Cache
      2. Access to In-Memory Data Pages
      3. Managing Pages in the Data Cache
      4. Checkpoints
      5. Managing Memory in Other Caches
        1. The Memory Broker
      6. Sizing Memory
      7. Sizing the Buffer Pool
        1. Observing Memory Internals
          1. sys.dm_os_memory_clerks
          2. sys.dm_os_memory_cache_counters
          3. sys.dm_os_memory_cache_hash_tables
          4. sys.dm_os_memory_cache_clock_hands
        2. NUMA and Memory
        3. Read-Ahead
    3. Final Words
  7. 3. SQL Server 2005 Configuration
    1. Using SQL Server Configuration Manager
      1. Configuring Network Protocols
      2. Default Network Configuration
      3. Managing Services
    2. System Configuration
      1. Task Management
      2. Resource Allocation
      3. System Paging File Location
      4. Nonessential Services
      5. Network Protocols
      6. Compatibility with Earlier Versions of SQL Server
      7. Trace Flags
      8. SQL Server Configuration Settings
        1. Memory Options
          1. Min Server Memory and Max Server Memory
          2. Set Working Set Size
          3. AWE Enabled
          4. User Connections
          5. Locks
        2. Scheduling Options
          1. Affinity Mask and Affinity64 Mask
          2. Lightweight Pooling
          3. Priority Boost
          4. Max Worker Threads
        3. Disk I/O Options
          1. Recovery Interval
          2. Affinity I/O Mask and Affinity64 I/O Mask
        4. Query Processing Options
          1. Min Memory Per Query
          2. Query Wait
          3. Blocked Process Threshold
          4. Index Create Memory
          5. Query Governor Cost Limit
          6. Max Degree Of Parallelism and Cost Threshold For Parallelism
      9. The Default Trace
    3. Final Words
  8. 4. Databases and Database Files
    1. System Databases
      1. master
      2. model
      3. tempdb
      4. mssqlsystemresource
      5. msdb
    2. Sample Databases
      1. AdventureWorks
      2. pubs
      3. Northwind
    3. Database Files
    4. Creating a Database
      1. A CREATE DATABASE Example
    5. Expanding or Shrinking a Database
      1. Automatic File Expansion
      2. Manual File Expansion
      3. Fast File Initialization
      4. Automatic Shrinkage
      5. Manual Shrinkage
        1. DBCC SHRINKFILE
        2. DBCC SHRINKDATABASE
    6. Using Database Filegroups
      1. The Default Filegroup
      2. A FILEGROUP CREATION Example
    7. Altering a Database
      1. ALTER DATABASE Examples
    8. Databases Under the Hood
      1. Space Allocation
      2. Checking Database Consistency
        1. Performing Validation Checks
        2. Validation Checks
        3. DBCC Repair Options
        4. Progress Reporting
        5. DBCC Best Practices
    9. Setting Database Options
      1. State Options
        1. SINGLE_USER | RESTRICTED_USER | MULTI_USER
        2. OFFLINE | ONLINE | EMERGENCY
        3. Emergency Mode Repair
        4. READ_ONLY | READ_WRITE
        5. Termination Options
      2. Cursor Options
      3. Auto Options
      4. SQL Options
      5. Database Recovery Options
      6. Other Database Options
    10. Database Snapshots
      1. Creating a Database Snapshot
      2. Space Used by Database Snapshots
      3. Managing Your Snapshots
    11. The tempdb Database
      1. Objects in tempdb
        1. User Objects
        2. Internal Objects
        3. Version Store
      2. Optimizations in tempdb
        1. Logging Optimizations
        2. Allocation and Caching Optimizations
      3. Best Practices
      4. tempdb Space Monitoring
    12. Database Security
      1. Database Access
      2. Managing Database Security
      3. Databases vs. Schemas
      4. Separation of Principals and Schemas
      5. Default Schemas
    13. Moving or Copying a Database
      1. Detaching and Reattaching a Database
      2. Backing Up and Restoring a Database
      3. Moving System Databases
      4. Moving the master Database and Resource Database
    14. Compatibility Levels
    15. Summary
  9. 5. Logging and Recovery
    1. Transaction Log Basics
      1. Phases of Recovery
        1. Page LSNs and Recovery
    2. Changes in Log Size
      1. Virtual Log Files
      2. Observing Virtual Log Files
        1. Multiple Log Files
      3. Automatic Truncation of Virtual Log Files
      4. Maintaining a Recoverable Log
      5. Automatic Shrinking of the Log
      6. Log File Size
    3. Reading the Log
    4. Backing Up and Restoring a Database
      1. Types of Backups
      2. Recovery Models
        1. FULL Recovery Model
        2. BULK_LOGGED Recovery Model
        3. SIMPLE Recovery Model
        4. Migrating from SQL Server 7.0
      3. Choosing a Backup Type
      4. Restoring a Database
        1. Backing Up and Restoring Files and Filegroups
        2. Partial Backups
        3. Page Restore
        4. Partial Restore
        5. Restoring with Standby
    5. Summary
  10. 6. Tables
    1. System Objects
      1. Compatibility Views
      2. Catalog Views
      3. Other Metadata
        1. Information Schema Views
        2. System Functions
        3. System Stored Procedures
        4. Metadata Wrap-Up
    2. Creating Tables
      1. Naming Tables and Columns
      2. Reserved Keywords
      3. Delimited Identifiers
      4. Naming Conventions
      5. Data Types
        1. Choosing a Data Type
        2. Numeric Data Types
        3. Date and Time Data Types
        4. Character Data Types
        5. Miscellaneous Data Types
          1. Binary Data Types
          2. bit Data Type
          3. Large Object Data Types
          4. cursor Data Type
          5. rowversion Data Type
          6. sql_variant Data Type
          7. table Data Type
          8. xml Data Type
          9. uniqueidentifier Data Type
      6. Much Ado About NULL
    3. User-Defined Data Types
      1. CLR Data Types
    4. IDENTITY Property
    5. Internal Storage
      1. The sys.indexes Catalog View
      2. Data Storage Metadata
        1. Querying the Catalog Views
      3. Data Pages
        1. Page Header
        2. Data Rows for In-Row Data
        3. Row Offset Array
      4. Examining Data Pages
      5. The Structure of Data Rows
      6. Column Offset Arrays
      7. Storage of Fixed-Length Rows
      8. Storage of Variable-Length Rows
      9. Page Linkage
      10. Row-Overflow Data
      11. Large Object Data
        1. LOB Data Stored in the Data Row
      12. Storage of varchar(MAX) Data
      13. Storage of sql_variant Data
    6. Constraints
      1. Constraint Names and Catalog View Information
      2. Constraint Failures in Transactions and Multiple-Row Data Modifications
        1. The Order of Integrity Checks
    7. Altering a Table
      1. Changing a Data Type
      2. Adding a New Column
      3. Adding, Dropping, Disabling, or Enabling a Constraint
      4. Dropping a Column
      5. Enabling or Disabling a Trigger
      6. Internals of Altering Tables
    8. Summary
  11. 7. Index Internals and Management
    1. Index Organization
      1. Clustered Indexes
      2. Nonclustered Indexes
    2. Creating an Index
      1. Included Columns
      2. Index Placement
      3. Constraints and Indexes
    3. The Structure of Index Pages
      1. Clustered Index Rows with a Uniqueifier
      2. Index Row Formats
        1. Clustered Index Node Rows
        2. Nonclustered Index Leaf Rows
        3. Nonclustered Index Node Rows
    4. Index Space Requirements
      1. B-Tree Size
      2. Actual vs. Estimated Size
    5. Special Indexes
      1. Prerequisites
        1. SET Options
        2. Permissible Functions
        3. Schema Binding
      2. Indexes on Computed Columns
        1. Using the COLUMNPROPERTY Function
        2. Implementation of a Computed Column
        3. Persisted Columns
      3. Indexed Views
        1. Additional Requirements
        2. Creating an Indexed View
        3. Using an Indexed View
    6. Table and Index Partitioning
      1. Partition Functions and Partition Schemes
      2. Metadata for Partitioning
      3. Partition Power
    7. Data Modification Internals
      1. Inserting Rows
      2. Splitting Pages
        1. Splitting the Root Page of an Index
        2. Splitting the Intermediate Index Page
        3. Splitting the Data Page
      3. Deleting Rows
        1. Deleting Rows from a Heap
        2. Deleting Rows from a B-Tree
        3. Deleting Rows in the Node Levels of an Index
        4. Reclaiming Pages
      4. Updating Rows
        1. Moving Rows
        2. Managing Forward Pointers
        3. Updating in Place
        4. Updating Not in Place
      5. Table-Level vs. Index-Level Data Modification
      6. Logging
      7. Locking
    8. Managing Indexes
      1. ALTER INDEX
      2. Types of Fragmentation
        1. Detecting Fragmentation
        2. Fragmentation Report
      3. Removing Fragmentation
      4. Rebuilding an Index
        1. Online Index Building
    9. Using Indexes
      1. Looking for Rows
      2. Joining
      3. Sorting
      4. Grouping
      5. Maintaining Uniqueness
    10. Summary
  12. 8. Locking and Concurrency
    1. Concurrency Models
      1. Pessimistic Concurrency
      2. Optimistic Concurrency
    2. Transaction Processing
      1. ACID Properties
        1. Atomicity
        2. Consistency
        3. Isolation
        4. Durability
      2. Isolation Levels
        1. Uncommitted Read
        2. Read Committed
        3. Repeatable Read
        4. Snapshot
        5. Serializable
    3. Locking
      1. Locking Basics
      2. Spinlocks
      3. Lock Types for User Data
      4. Lock Modes
        1. Shared Locks
        2. Exclusive Locks
        3. Update Locks
        4. Intent Locks
        5. Special Lock Modes
        6. Conversion Locks
        7. Key-Range Locks
      5. Lock Granularity
        1. Key Locks
        2. Additional Lock Resources
        3. Application Locks
        4. Identifying Lock Resources
        5. Associated Entity ID
      6. Lock Duration
      7. Lock Ownership
      8. Viewing Locks
        1. sys.dm_tran_locks
          1. Resource Columns
          2. Request Columns
      9. Locking Examples
        1. Example 1: SELECT with Default Isolation Level
        2. Example 2: SELECT with Repeatable Read Isolation Level
        3. Example 3: SELECT with Serializable Isolation Level
        4. Example 4: Update Operations
        5. Example 5: Update with Serializable Isolation Level Using an Index
        6. Example 6: Update with Serializable Isolation Level Not Using an Index
        7. Example 7: Creating a Table
        8. Example 8: Row Locks
    4. Lock Compatibility
    5. Internal Locking Architecture
      1. Lock Partitioning
      2. Lock Blocks
      3. Lock Owner Blocks
      4. syslockinfo Table
    6. Bound Connections
      1. Using Bound Connections
      2. Multiple Active Result Sets
    7. Row-Level Locking vs. Page-Level Locking
      1. Lock Escalation
        1. Disabling Lock Escalation
      2. Deadlocks
    8. Row Versioning
      1. Overview of Row Versioning
      2. Row Versioning Details
      3. Snapshot-Based Isolation Levels
        1. READ COMMITTED SNAPSHOT Isolation
        2. SNAPSHOT Isolation
        3. SNAPSHOT Isolation Scope
        4. Viewing Database State
        5. Update Conflicts
        6. Data Definition Language and SNAPSHOT Isolation
        7. Summary of Snapshot-Based Isolation levels
        8. The Version Store
        9. Management of the Version Store
        10. Snapshot Transaction Metadata
      4. Choosing a Concurrency Model
    9. Other Features That Use Row Versioning
      1. Triggers and Row Versioning
      2. MARS and Row Versioning
    10. Controlling Locking
      1. Lock Hints
        1. Setting a Lock Timeout
    11. Summary
  13. About the Author
  14. Additional Resources for Developers
    1. Visual Basic 2005
    2. Visual C# 2005
    3. Web Development
    4. Data Access
    5. SQL Server 2005
    6. Other Visual Studio 2005 Topics
    7. Other Developer Topics
  15. More Great Developer Resources
    1. Developer Step by Step
    2. Developer Reference
    3. Advanced Topics
  16. Index
  17. About the Author
  18. Copyright

Product information

  • Title: Inside Microsoft® SQL Server™ 2005, Fourth Edition
  • Author(s): Kalen Delaney
  • Release date: October 2006
  • Publisher(s): Microsoft Press
  • ISBN: 9780735621053