Microsoft SQL Server 2012 Internals

Table of contents

1. Microsoft SQL Server 2012 Internals
2. Introduction
   1. Who should read this book
   2. Organization of this book
   3. Companion content
   4. System requirements
   5. Acknowledgments
   6. Errata & book support
   7. We want to hear from you
   8. Stay in touch
3. 1. SQL Server 2012 architecture and configuration
   1. SQL Server editions
   2. SQL Server installation and tools
   3. SQL Server metadata
      1. Compatibility views
      2. Catalog views
      3. Dynamic Management Objects
      4. Other metadata
         1. Information schema views
         2. System functions
         3. System stored procedures
         4. Metadata wrap-up
   4. Components of the SQL Server engine
   5. 1. Protocols
      2. Query processor
         1. Parsing and binding components
         2. The Query Optimizer
         3. The query executor
      3. The storage engine
         1. Access methods
         2. Transaction services
         3. Other operations
   6. SQL Server 2012 configuration
      1. Using SQL Server Configuration Manager
         1. Configuring network protocols
         2. Implementing a default network configuration
      2. Managing services
   7. SQL Server system configuration
      1. Operating system configuration
         1. Task management
         2. System paging file location
         3. Nonessential services
         4. Connectivity
         5. Firewall setting
      2. Trace flags
      3. SQL Server configuration settings
         1. Memory options
         2. Scheduling options
         3. Disk I/O options
         4. Query processing options
   8. Conclusion
4. 2. The SQLOS
   1. NUMA architecture
   2. The scheduler
      1. Understanding SQL Server schedulers
         1. SQL Server workers
         2. SQL Server tasks
         3. Threads vs. fibers
         4. NUMA and schedulers
         5. Dynamic affinity
      2. Binding schedulers to CPUs
      3. 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
      4. Understanding the Dedicated Administrator Connection (DAC)
   3. Memory
      1. The buffer pool and the data cache
      2. Column store object pool
      3. Access to in-memory data pages
      4. Page management in the data cache
      5. The free buffer list and the lazywriter
      6. Checkpoints
      7. Memory management in other caches
      8. The Memory Broker
      9. Memory sizing
      10. Buffer pool sizing
          1. DMVs for memory internals
          2. NUMA and memory
          3. Read-ahead
   4. SQL Server Resource Governor
      1. Resource Governor overview
         1. Classifier function
         2. Resource pools
         3. Pool sizing
         4. Workload groups
         5. Code example
      2. Resource Governor controls
      3. Resource Governor metadata
   5. Extended Events
      1. Extended Events architecture
      2. Event execution life cycle
      3. Core concepts
         1. Events
         2. Actions
         3. Predicates
         4. Types and maps
         5. Targets
         6. Event sessions
      4. Extended Events DDL and querying
         1. Creating an event session
         2. Querying event data
         3. Stopping and removing the event session
      5. Extended Events UI
         1. Creating and managing event sessions
         2. Viewing Target Data
         3. Session catalog metadata
         4. Session-scoped configuration options
   6. Conclusion
5. 3. Databases and database files
   1. Working with sample databases
      1. AdventureWorks
      2. pubs
      3. Northwind
   2. Understanding database files
      1. Creating a database
      2. Using CREATE DATABASE: an example
   3. 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
   4. Using database filegroups
      1. The default filegroup
      2. A FILEGROUP CREATION example
      3. Filestream filegroups
   5. Altering a database
      1. ALTER DATABASE examples
      2. Databases under the hood
      3. Space allocation
   6. Setting database options
      1. State options
         1. SINGLE_USER | RESTRICTED_USER | MULTI_USER
         2. OFFLINE | ONLINE | EMERGENCY
         3. READ_ONLY | READ_WRITE
         4. Termination options
      2. Cursor options
      3. Auto options
      4. SQL options
      5. Database recovery options
      6. Other database options
   7. Understanding database security
      1. Database access
      2. Database security
      3. Databases vs. schemas
      4. Principals and schemas
      5. Default schemas
   8. Moving or copying a database
      1. Detaching and reattaching a database
      2. Backing up and restoring a database
   9. Understanding compatibility levels
   10. Conclusion
6. 4. Special databases
   1. System databases
      1. Understanding the master database
      2. Understanding the model database
      3. Introducing the tempdb database
      4. Understanding the resource database
      5. Understanding the msdb database
      6. Moving system databases
      7. Moving the master database
   2. 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 contention
         1. DML contention
         2. DDL contention
      5. tempdb space monitoring
   3. Database snapshots
      1. Creating a database snapshot
      2. Understanding space used by database snapshots
      3. Managing your snapshots
   4. Partially contained databases
      1. Configuring a contained database
      2. Creating contained users
      3. Understanding database collation changes
      4. Detecting uncontained features
   5. Conclusion
7. 5. Logging and recovery
   1. Transaction log internals
      1. Phases of recovery
      2. Page LSNs and recovery
      3. Log reading
      4. The log cache
   2. Changes in log size
      1. Understanding virtual log files
         1. Observing virtual log files
         2. Using multiple log files
         3. Understanding automatic truncation of virtual log files
      2. Maintaining a recoverable log
      3. Automatically shrinking the log
      4. Viewing the log file size
   3. Database backup and restore
      1. Understanding the types of backups
      2. Understanding recovery models
         1. Minimally logged operations
         2. FULL recovery model
         3. BULK_LOGGED recovery model
         4. SIMPLE recovery model
         5. Switching recovery models
      3. Choosing a backup type
      4. Restoring a database
         1. Backing up and restoring files and filegroups
         2. Performing partial backups
         3. Restoring pages
         4. Performing a partial restore
         5. Restoring with standby
   4. Conclusion
8. 6. Table storage
   1. Table creation
      1. Naming tables and columns
      2. Avoiding reserved keywords
      3. Using delimited identifiers
      4. Understanding naming conventions
      5. Choosing a data type
         1. Numeric data types
         2. Date and time data types
         3. Character data types
         4. Character data collation
         5. Special data types
      6. The NULL problem
      7. User-defined data types
   2. IDENTITY property
   3. Sequence object
   4. Internal storage
      1. The sys.indexes catalog view
      2. Data storage metadata
      3. Catalog view queries
      4. 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. How to find a physical page
         1. Creating a function to perform the conversion
         2. Using the sys.dm_db_database_page_allocations DMV
         3. Using the sys.fn_PhysLocFormatter function
      7. Storage of fixed-length rows
      8. Storage of variable-length rows
      9. NULLS and variable-length columns
      10. Storage of date and time data
      11. Storage of sql_variant data
   5. Constraints
      1. Constraint names and catalog view information
      2. Constraint failures in transactions and multiple-row data modifications
   6. 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. Internals of altering tables
   7. Heap modification internals
      1. Allocation structures
      2. Inserting rows
      3. Deleting rows
         1. Deleting rows from a heap
         2. Reclaiming pages
      4. Updating rows
         1. Moving rows
         2. Managing forward pointers
         3. Updating in place
         4. Updating not in place
   8. Conclusion
9. 7. Indexes: internals and management
   1. Overview
   2. SQL Server B-tree indexes
      1. Example 1: An index with a large key column
      2. Example 2: An index with a very narrow key column
   3. Tools for analyzing indexes
      1. Using the dm_db_index_physical_stats DMV
      2. Using sys.dm_db_database_page_allocations
   4. Understanding B-tree index structures
      1. Clustering key dependency
      2. Nonclustered B-tree indexes
      3. Constraints and indexes
   5. Index creation options
      1. IGNORE_DUP_KEY
      2. STATISTICS_NORECOMPUTE
      3. MAXDOP
      4. Index placement
   6. Physical index structures for B-trees
      1. Index row formats
      2. Clustered index structures
      3. Non-leaf level(s) of a clustered index
      4. Analyzing a clustered index structure
      5. Nonclustered index structures
         1. Nonclustered index rows on a heap
         2. Nonclustered index rows on a clustered table
         3. Nonunique nonclustered index rows
         4. Nonclustered index rows with included columns (using INCLUDE)
         5. Nonclustered index rows with filters (filtered indexes)
   7. Indexes on computed columns and indexed views
      1. SET options
      2. Permissible functions
      3. Schema binding
      4. Indexes on computed columns
      5. Implementation of a computed column
      6. Persisted columns
      7. Indexed views
      8. Additional requirements
      9. Creating an indexed view
      10. Using an indexed view
   8. Data modification internals
      1. Inserting rows
      2. Splitting pages
         1. Splitting the root page of an index
         2. Splitting an intermediate index page
         3. Splitting a leaf-level page
      3. Deleting rows
         1. Deleting rows from a heap
         2. Deleting rows from a B-tree
         3. Deleting rows in the non-leaf levels of an index
         4. Reclaiming pages
      4. Updating rows
         1. Moving rows
         2. Managing forwarding pointers
         3. Updating in place
         4. Updating not in place
      5. Table-level vs. index-level data modification
      6. Logging
      7. Locking
      8. Fragmentation
   9. Managing B-tree index structures
      1. Dropping indexes
      2. Using the ALTER INDEX command
         1. Rebuilding an index
         2. Disabling an index
         3. Changing index options
         4. Reorganizing an index
      3. Detecting fragmentation
      4. Removing fragmentation
      5. Rebuilding an index
      6. Online index building
   10. Columnstore indexes
       1. Creation of columnstore indexes
       2. Storage of columnstore indexes
       3. Columnstore index metadata
   11. Conclusion
10. 8. Special storage
    1. Large object storage
       1. Restricted-length large object data (row-overflow data)
       2. Unrestricted-length large object data
          1. Storing LOB data in the data row
          2. Storing MAX-length data
          3. Appending data into a LOB column
    2. FILESTREAM and FileTable data
       1. Enabling FILESTREAM data for SQL Server
       2. Creating a FILESTREAM-enabled database
       3. Creating a table to hold FILESTREAM data
       4. Manipulating FILESTREAM data
          1. Inserting FILESTREAM data
          2. Updating FILESTREAM data
          3. Deleting FILESTREAM data
          4. Manipulating FILESTREAM data and transactions
          5. Logging FILESTREAM changes
          6. Using garbage collection for FILESTREAM data
       5. Exploring metadata with FILESTREAM data
       6. Creating a FileTable
       7. Considering performance for FILESTREAM data
       8. Summarizing FILESTREAM and FileTable
    3. Sparse columns
       1. Management of sparse columns
          1. Creating a table
          2. Altering a table
       2. Column sets and sparse column manipulation
       3. Physical storage
       4. Metadata
       5. Storage savings with sparse columns
    4. Data compression
       1. Vardecimal
       2. Row compression
          1. Enabling row compression
          2. New row format
       3. Page compression
          1. Column prefix compression
          2. Dictionary compression
          3. Physical storage
          4. Page compression analysis
          5. CI record rebuilding
          6. Compression metadata
          7. Performance issues
          8. Backup compression
    5. Table and index partitioning
       1. Partition functions and partition schemes
       2. Metadata for partitioning
       3. The sliding window benefits of partitioning
       4. Partitioning a columnstore index
    6. Conclusion
11. 9. Special indexes
    1. Special indexes vs. ordinary indexes
    2. XML indexes
       1. Creating and maintaining XML indexes
          1. Primary XML index
          2. Secondary XML indexes
       2. Using XQuery in SQL Server: internals
       3. Understanding how a query plan uses an XML index
       4. Using secondary XML indexes
       5. Working with XML indexes and schema-validated columns
       6. Using XML-specific information in query plans
    3. Spatial indexes
       1. Purpose of spatial indexes
       2. Composition of the spatial index
       3. How a spatial query uses a spatial index
       4. How to ensure that your spatial index is being used
       5. Spatial query plans and spatial indexes
       6. Nearest neighbor optimization in SQL Server 2012
       7. Spatial index diagnostic stored procedures
       8. Diagnostics with the SQL Server 2012 spatial functions
    4. Full-text indexes
       1. Internal tables created by the full-text index
          1. ifts_comp_fragment_[t_objectid]_[ordinal]
          2. fulltext_index_docidstatus_[t_objectid]
          3. fulltext_docidfilter_[t_objectid]
          4. fulltext_indexeddocid_[t_objectid]
          5. fulltext_avdl_[t_objectid]
          6. fulltext_index_docidmap_[t_objectid]
          7. Other internal tables
       2. Full-text index metadata views
       3. Full-text index creation
       4. Maintenance of a full-text index
       5. Full-text status metadata, configuration, and diagnostic information
       6. How a full-text index is used in a query
       7. A full-text query plan
       8. Extended event information for full-text queries
    5. Semantic indexes
    6. Conclusion
12. 10. Query execution
    1. Introducing query processing and execution
       1. Iterators
       2. Properties of iterators
          1. Memory consumption
          2. Nonblocking vs. blocking iterators
          3. Dynamic cursor support
    2. Reading query plans
       1. Graphical plans
       2. Text plans
       3. XML plans
       4. Estimated vs. actual query plans
       5. Query plan display options
    3. Analyzing plans
       1. Scans and seeks
       2. Seekable predicates and covered columns
          1. Single-column indexes
          2. Composite indexes
          3. Identifying index keys
       3. Bookmark lookup
       4. Joins
          1. Nested loops join
          2. Merge join
          3. Hash join
          4. Summary of join properties
       5. Aggregations
          1. Scalar aggregation
          2. Stream aggregation
          3. Hash aggregation
       6. Unions
       7. Advanced index operations
          1. Dynamic index seeks
          2. Index unions
          3. Index intersections
       8. Subqueries
          1. Noncorrelated scalar subqueries
          2. Correlated scalar subqueries
          3. Removing correlations
          4. Subqueries in CASE expressions
       9. Parallelism
          1. Degree of parallelism (DOP)
          2. Parallelism operator (also known as exchange)
          3. Parallel scan
          4. Load balancing
          5. Parallel nested loops join
          6. Round-robin exchange
          7. Parallel nested loops join performance
          8. Inner-side parallel execution
          9. Parallel merge join
          10. Parallel hash join
          11. Hash partitioning
          12. Broadcast partitioning
          13. Bitmap filtering
       10. Inserts, updates, and deletes
    4. Understanding data warehouses
    5. Using columnstore indexes and batch processing
       1. Adding new data
       2. Hints
    6. Conclusion
13. 11. The Query Optimizer
    1. Overview
       1. Understanding the tree format
       2. Understanding optimization
    2. Search space and heuristics
       1. Rules
       2. Properties
       3. Storage of alternatives: the Memo
       4. Operators
          1. Compute Scalar: Project
          2. Compute Sequence: Sequence Project
          3. semi-join
          4. Apply
          5. Spools
          6. Exchange
    3. Optimizer architecture
       1. Before optimization
       2. Simplification
       3. Trivial plan/auto-parameterization
       4. Limitations
       5. The Memo: exploring multiple plans efficiently
    4. Statistics, cardinality estimation, and costing
       1. Statistics design
       2. Density/frequency information
       3. Filtered statistics
       4. String statistics
       5. Cardinality estimation details
       6. Limitations
       7. Costing
    5. Index selection
       1. Filtered indexes
       2. Indexed views
    6. Partitioned tables
       1. Partition-aligned index views
    7. Windowing functions
    8. Data warehousing
       1. Columnstore indexes
       2. Batch mode processing
          1. Grouping rows for repeated operations
          2. Column orientation within batches
          3. Data encoding
          4. Logical database design best practices
       3. Plan shape
       4. Columnstore limitations and workarounds
    9. Updates
       1. Halloween Protection
       2. Split/Sort/Collapse
       3. Merge
       4. Wide update plans
       5. Non-updating updates
       6. Sparse column updates
       7. Partitioned updates
       8. Locking
       9. Partition-level lock escalation
    10. Distributed query
    11. Extended indexes
    12. Plan hinting
        1. Debugging plan issues
        2. {HASH | ORDER} GROUP
        3. {MERGE | HASH | CONCAT} UNION
        4. FORCE ORDER, {LOOP | MERGE | HASH} JOIN
        5. INDEX=<indexname> | <indexid>
        6. FORCESEEK
        7. FAST <number_rows>
        8. MAXDOP <N>
        9. OPTIMIZE FOR
        10. PARAMETERIZATION {SIMPLE | FORCED}
        11. NOEXPAND
        12. USE PLAN
    13. Hotfixes
    14. Conclusion
14. 12. Plan caching and recompilation
    1. The plan cache
       1. Plan cache metadata
       2. Clearing plan cache
    2. Caching mechanisms
       1. Ad hoc query caching
       2. Optimizing for ad hoc workloads
          1. Controlling the optimize for ad hoc workloads setting
          2. The compiled plan stub
       3. Simple parameterization
          1. Forced parameterization
          2. Drawbacks of simple parameterization
       4. Prepared queries
          1. The sp_executesql procedure
          2. The prepare-and-execute method
          3. Caching prepared queries
       5. Compiled objects
          1. Stored procedures
          2. Functions
       6. Causes of recompilation
          1. Correctness-based recompiles
          2. Optimality-based recompiles
          3. Skipping the recompilation step
          4. Multiple recompilations
          5. Removing plans from cache
    3. Plan cache internals
       1. Cache stores
       2. Compiled plans
       3. Execution contexts
       4. Plan cache metadata
          1. Handles
          2. sys.dm_exec_sql_text
          3. sys.dm_exec_query_plan
          4. sys.dm_exec_text_query_plan
          5. sys.dm_exec_cached_plans
          6. sys.dm_exec_cached_plan_dependent_objects
          7. sys.dm_exec_requests
          8. sys.dm_exec_query_stats
          9. sys.dm_exec_procedure_stats
       5. Cache size management
          1. Local memory pressure
          2. Global memory pressure
       6. Costing of cache entries
    4. Objects in plan cache: the big picture
       1. Multiple plans in cache
       2. When to use stored procedures and other caching mechanisms
       3. Troubleshooting plan cache issues
          1. Wait statistics indicating plan cache problems
          2. Other caching issues
          3. Handling problems with compilation and recompilation
    5. Optimization hints and plan guides
       1. Optimization hints
          1. RECOMPILE
          2. OPTIMIZE FOR
          3. KEEP PLAN
          4. KEEPFIXED PLAN
          5. PARAMETERIZATION
          6. USE PLAN
       2. Purpose of plan guides
       3. Types of plan guides
          1. Object plan guides
          2. SQL plan guides
          3. Template plan guides
       4. Managing plan guides
       5. Plan guide considerations
          1. Plan guide validation
          2. Freezing a plan from plan cache
    6. Conclusion
15. 13. Transactions 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. Transaction dependencies
          1. Lost updates
          2. Dirty reads
          3. Nonrepeatable reads
          4. Phantoms
       3. Isolation levels
          1. Read Uncommitted
          2. Read Committed
          3. Repeatable Read
          4. Snapshot
          5. Serializable
    3. Locking
       1. Locking basics
       2. Spinlocks
       3. Lock types for user data
          1. Lock modes
          2. Lock granularity
          3. Lock duration
          4. Lock ownership
       4. Viewing locks
          1. resource_ columns
          2. request_ columns
       5. Locking examples
       6. Lock compatibility
       7. Internal locking architecture
          1. Lock partitioning
          2. Lock blocks
          3. Lock owner blocks
          4. syslockinfo table
       8. Row-level locking vs. page-level locking
       9. Lock escalation
       10. Deadlocks
           1. Cycle deadlocks
           2. Conversion deadlocks
           3. Deadlock detection, intervention, and avoidance
    4. Row versioning
       1. Row versioning details
       2. Snapshot-based isolation levels
          1. Read committed snapshot isolation
          2. Snapshot isolation
          3. Snapshot isolation scope
          4. Viewing database state
          5. Update conflicts
          6. DDL and snapshot isolation
          7. Summary of snapshot-based isolation levels
          8. The version store
          9. Management of the version store
          10. Snapshot transaction metadata
       3. Choosing a concurrency model
    5. Controlling locking
       1. Lock hints
          1. Setting a lock timeout
    6. Conclusion
16. 14. DBCC internals
    1. Shrinking files and databases
       1. Data file shrinking
       2. Log file shrinking
       3. DBCC SHRINKFILE
       4. AUTO_SHRINK
    2. Consistency checking
       1. Getting a consistent view of the database
          1. Disk space issues
          2. Alternatives to using a database snapshot
       2. Processing the database efficiently
          1. Performing fact generation
          2. Using the query processor
          3. Processing batches
          4. Reading the pages to process
          5. Enablilng parallelism
       3. Performing primitive system catalog consistency checks
       4. Performing allocation consistency checks
          1. Collecting allocation facts
          2. Checking allocation facts
       5. Performing per-table logical consistency checks
          1. Metadata Consistency Checks
          2. Page audit
          3. Data and index page processing
       6. Processing columns
          1. Computed columns
          2. Text page processing
          3. Cross-page consistency checks
       7. Performing cross-table consistency checks
          1. Service broker consistency checks
          2. Cross-catalog consistency checks
          3. Indexed-view consistency checks
       8. Understanding DBCC CHECKDB output
          1. Regular output
          2. Error reporting to Microsoft
          3. SQL Server error log output
          4. Application event log output
          5. Progress reporting output
       9. Reviewing DBCC CHECKDB options
          1. NOINDEX
          2. Repair options
          3. ALL_ERRORMSGS
          4. EXTENDED_LOGICAL_CHECKS
          5. NO_INFOMSGS
          6. TABLOCK
          7. ESTIMATEONLY
          8. PHYSICAL_ONLY
          9. DATA_PURITY
       10. Performing database repairs
           1. Repair mechanisms
           2. Emergency mode repair
           3. What data was deleted by repair?
       11. Using consistency-checking commands other than DBCC CHECKDB
           1. DBCC CHECKALLOC
           2. DBCC CHECKTABLE
           3. DBCC CHECKFILEGROUP
           4. DBCC CHECKCATALOG
           5. DBCC CHECKIDENT
           6. DBCC CHECKCONSTRAINTS
    3. Conclusion
17. A. About the authors
18. Index
19. About the Authors
20. Copyright