Microsoft SQL Server 2012 Internals by Delaney, Kalen -- Read -- Imperial Library of Trantor

Index

Microsoft SQL Server 2012 Internals Introduction

Who should read this book Organization of this book Companion content System requirements Acknowledgments Errata & book support We want to hear from you Stay in touch

1. SQL Server 2012 architecture and configuration

SQL Server editions SQL Server installation and tools SQL Server metadata

Compatibility views Catalog views Dynamic Management Objects Other metadata

Information schema views System functions System stored procedures Metadata wrap-up

Components of the SQL Server engine

Protocols Query processor

Parsing and binding components The Query Optimizer The query executor

The storage engine

Access methods Transaction services Other operations

SQL Server 2012 configuration

Using SQL Server Configuration Manager

Configuring network protocols Implementing a default network configuration

Managing services

SQL Server system configuration

Operating system configuration

Task management System paging file location Nonessential services Connectivity Firewall setting

Trace flags SQL Server configuration settings

Memory options Scheduling options Disk I/O options Query processing options

Conclusion

2. The SQLOS

NUMA architecture The scheduler

Understanding SQL Server schedulers

SQL Server workers SQL Server tasks Threads vs. fibers NUMA and schedulers Dynamic affinity

Binding schedulers to CPUs Observing scheduler internals

sys.dm_os_schedulers sys.dm_os_workers sys.dm_os_threads sys.dm_os_tasks sys.dm_os_waiting_tasks

Understanding the Dedicated Administrator Connection (DAC)

Memory

The buffer pool and the data cache Column store object pool Access to in-memory data pages Page management in the data cache The free buffer list and the lazywriter Checkpoints Memory management in other caches The Memory Broker Memory sizing Buffer pool sizing

DMVs for memory internals NUMA and memory Read-ahead

SQL Server Resource Governor

Resource Governor overview

Classifier function Resource pools Pool sizing Workload groups Code example

Resource Governor controls Resource Governor metadata

Extended Events

Extended Events architecture Event execution life cycle Core concepts

Events Actions Predicates Types and maps Targets Event sessions

Extended Events DDL and querying

Creating an event session Querying event data Stopping and removing the event session

Extended Events UI

Creating and managing event sessions Viewing Target Data Session catalog metadata Session-scoped configuration options

Conclusion

3. Databases and database files

Working with sample databases

AdventureWorks pubs Northwind

Understanding database files

Creating a database Using CREATE DATABASE: an example

Expanding or shrinking a database

Automatic file expansion Manual file expansion Fast file initialization Automatic shrinkage Manual shrinkage

DBCC SHRINKFILE DBCC SHRINKDATABASE

Using database filegroups

The default filegroup A FILEGROUP CREATION example Filestream filegroups

Altering a database

ALTER DATABASE examples Databases under the hood Space allocation

Setting database options

State options

Cursor options Auto options SQL options Database recovery options Other database options

Understanding database security

Database access Database security Databases vs. schemas Principals and schemas Default schemas

Moving or copying a database

Detaching and reattaching a database Backing up and restoring a database

Understanding compatibility levels Conclusion

4. Special databases

System databases

Understanding the master database Understanding the model database Introducing the tempdb database Understanding the resource database Understanding the msdb database Moving system databases Moving the master database

The tempdb database

Objects in tempdb

User objects Internal objects Version store

Optimizations in tempdb

Logging optimizations Allocation and caching optimizations

Best practices tempdb contention

DML contention DDL contention

tempdb space monitoring

Database snapshots

Creating a database snapshot Understanding space used by database snapshots Managing your snapshots

Partially contained databases

Configuring a contained database Creating contained users Understanding database collation changes Detecting uncontained features

Conclusion

5. Logging and recovery

Transaction log internals

Phases of recovery Page LSNs and recovery Log reading The log cache

Changes in log size

Understanding virtual log files

Observing virtual log files Using multiple log files Understanding automatic truncation of virtual log files

Maintaining a recoverable log Automatically shrinking the log Viewing the log file size

Database backup and restore

Understanding the types of backups Understanding recovery models

Minimally logged operations FULL recovery model BULK_LOGGED recovery model SIMPLE recovery model Switching recovery models

Choosing a backup type Restoring a database

Backing up and restoring files and filegroups Performing partial backups Restoring pages Performing a partial restore Restoring with standby

Conclusion

6. Table storage

Table creation

Naming tables and columns Avoiding reserved keywords Using delimited identifiers Understanding naming conventions Choosing a data type

Numeric data types Date and time data types Character data types Character data collation Special data types

The NULL problem User-defined data types

IDENTITY property Sequence object Internal storage

The sys.indexes catalog view Data storage metadata Catalog view queries Data pages

Page header Data rows for in-row data Row offset array Examining data pages

The structure of data rows How to find a physical page

Creating a function to perform the conversion Using the sys.dm_db_database_page_allocations DMV Using the sys.fn_PhysLocFormatter function

Storage of fixed-length rows Storage of variable-length rows NULLS and variable-length columns Storage of date and time data Storage of sql_variant data

Constraints

Constraint names and catalog view information Constraint failures in transactions and multiple-row data modifications

Altering a table

Changing a data type Adding a new column Adding, dropping, disabling, or enabling a constraint Dropping a column Internals of altering tables

Heap modification internals

Allocation structures Inserting rows Deleting rows

Deleting rows from a heap Reclaiming pages

Updating rows

Moving rows Managing forward pointers Updating in place Updating not in place

Conclusion

7. Indexes: internals and management

Overview SQL Server B-tree indexes

Example 1: An index with a large key column Example 2: An index with a very narrow key column

Tools for analyzing indexes

Using the dm_db_index_physical_stats DMV Using sys.dm_db_database_page_allocations

Understanding B-tree index structures

Clustering key dependency Nonclustered B-tree indexes Constraints and indexes

Index creation options

IGNORE_DUP_KEY STATISTICS_NORECOMPUTE MAXDOP Index placement

Physical index structures for B-trees

Index row formats Clustered index structures Non-leaf level(s) of a clustered index Analyzing a clustered index structure Nonclustered index structures

Nonclustered index rows on a heap Nonclustered index rows on a clustered table Nonunique nonclustered index rows Nonclustered index rows with included columns (using INCLUDE) Nonclustered index rows with filters (filtered indexes)

Indexes on computed columns and indexed views

SET options Permissible functions Schema binding Indexes on computed columns Implementation of a computed column Persisted columns Indexed views Additional requirements Creating an indexed view Using an indexed view

Data modification internals

Inserting rows Splitting pages

Splitting the root page of an index Splitting an intermediate index page Splitting a leaf-level page

Deleting rows

Deleting rows from a heap Deleting rows from a B-tree Deleting rows in the non-leaf levels of an index Reclaiming pages

Updating rows

Moving rows Managing forwarding pointers Updating in place Updating not in place

Table-level vs. index-level data modification Logging Locking Fragmentation

Managing B-tree index structures

Dropping indexes Using the ALTER INDEX command

Rebuilding an index Disabling an index Changing index options Reorganizing an index

Detecting fragmentation Removing fragmentation Rebuilding an index Online index building

Columnstore indexes

Creation of columnstore indexes Storage of columnstore indexes Columnstore index metadata

Conclusion

8. Special storage

Large object storage

Restricted-length large object data (row-overflow data) Unrestricted-length large object data

Storing LOB data in the data row Storing MAX-length data Appending data into a LOB column

FILESTREAM and FileTable data

Enabling FILESTREAM data for SQL Server Creating a FILESTREAM-enabled database Creating a table to hold FILESTREAM data Manipulating FILESTREAM data

Inserting FILESTREAM data Updating FILESTREAM data Deleting FILESTREAM data Manipulating FILESTREAM data and transactions Logging FILESTREAM changes Using garbage collection for FILESTREAM data

Exploring metadata with FILESTREAM data Creating a FileTable Considering performance for FILESTREAM data Summarizing FILESTREAM and FileTable

Sparse columns

Management of sparse columns

Creating a table Altering a table

Column sets and sparse column manipulation Physical storage Metadata Storage savings with sparse columns

Data compression

Vardecimal Row compression

Enabling row compression New row format

Page compression

Column prefix compression Dictionary compression Physical storage Page compression analysis CI record rebuilding Compression metadata Performance issues Backup compression

Table and index partitioning

Partition functions and partition schemes Metadata for partitioning The sliding window benefits of partitioning Partitioning a columnstore index

Conclusion

9. Special indexes

Special indexes vs. ordinary indexes XML indexes

Creating and maintaining XML indexes

Primary XML index Secondary XML indexes

Using XQuery in SQL Server: internals Understanding how a query plan uses an XML index Using secondary XML indexes Working with XML indexes and schema-validated columns Using XML-specific information in query plans

Spatial indexes

Purpose of spatial indexes Composition of the spatial index How a spatial query uses a spatial index How to ensure that your spatial index is being used Spatial query plans and spatial indexes Nearest neighbor optimization in SQL Server 2012 Spatial index diagnostic stored procedures Diagnostics with the SQL Server 2012 spatial functions

Full-text indexes

Internal tables created by the full-text index

ifts_comp_fragment_[t_objectid]_[ordinal] fulltext_index_docidstatus_[t_objectid] fulltext_docidfilter_[t_objectid] fulltext_indexeddocid_[t_objectid] fulltext_avdl_[t_objectid] fulltext_index_docidmap_[t_objectid] Other internal tables

Full-text index metadata views Full-text index creation Maintenance of a full-text index Full-text status metadata, configuration, and diagnostic information How a full-text index is used in a query A full-text query plan Extended event information for full-text queries

Semantic indexes Conclusion

10. Query execution

Introducing query processing and execution

Iterators Properties of iterators

Memory consumption Nonblocking vs. blocking iterators Dynamic cursor support

Reading query plans

Graphical plans Text plans XML plans Estimated vs. actual query plans Query plan display options

Analyzing plans

Scans and seeks Seekable predicates and covered columns

Single-column indexes Composite indexes Identifying index keys

Bookmark lookup Joins

Nested loops join Merge join Hash join Summary of join properties

Aggregations

Scalar aggregation Stream aggregation Hash aggregation

Unions Advanced index operations

Dynamic index seeks Index unions Index intersections

Subqueries

Noncorrelated scalar subqueries Correlated scalar subqueries Removing correlations Subqueries in CASE expressions

Parallelism

Degree of parallelism (DOP) Parallelism operator (also known as exchange) Parallel scan Load balancing Parallel nested loops join Round-robin exchange Parallel nested loops join performance Inner-side parallel execution Parallel merge join Parallel hash join Hash partitioning Broadcast partitioning Bitmap filtering

Inserts, updates, and deletes

Understanding data warehouses Using columnstore indexes and batch processing

Adding new data Hints

Conclusion

11. The Query Optimizer

Overview

Understanding the tree format Understanding optimization

Search space and heuristics

Rules Properties Storage of alternatives: the Memo Operators

Compute Scalar: Project Compute Sequence: Sequence Project semi-join Apply Spools Exchange

Optimizer architecture

Before optimization Simplification Trivial plan/auto-parameterization Limitations The Memo: exploring multiple plans efficiently

Statistics, cardinality estimation, and costing

Statistics design Density/frequency information Filtered statistics String statistics Cardinality estimation details Limitations Costing

Index selection

Filtered indexes Indexed views

Partitioned tables

Partition-aligned index views

Windowing functions Data warehousing

Columnstore indexes Batch mode processing

Grouping rows for repeated operations Column orientation within batches Data encoding Logical database design best practices

Plan shape Columnstore limitations and workarounds

Updates

Halloween Protection Split/Sort/Collapse Merge Wide update plans Non-updating updates Sparse column updates Partitioned updates Locking Partition-level lock escalation

Distributed query Extended indexes Plan hinting

Debugging plan issues {HASH | ORDER} GROUP {MERGE | HASH | CONCAT} UNION FORCE ORDER, {LOOP | MERGE | HASH} JOIN INDEX=<indexname> | <indexid> FORCESEEK FAST <number_rows> MAXDOP <N> OPTIMIZE FOR PARAMETERIZATION {SIMPLE | FORCED} NOEXPAND USE PLAN

Hotfixes Conclusion

12. Plan caching and recompilation

The plan cache

Plan cache metadata Clearing plan cache

Caching mechanisms

Ad hoc query caching Optimizing for ad hoc workloads

Controlling the optimize for ad hoc workloads setting The compiled plan stub

Simple parameterization

Forced parameterization Drawbacks of simple parameterization

Prepared queries

The sp_executesql procedure The prepare-and-execute method Caching prepared queries

Compiled objects

Stored procedures Functions

Causes of recompilation

Correctness-based recompiles Optimality-based recompiles Skipping the recompilation step Multiple recompilations Removing plans from cache

Plan cache internals

Cache stores Compiled plans Execution contexts Plan cache metadata

Handles sys.dm_exec_sql_text sys.dm_exec_query_plan sys.dm_exec_text_query_plan sys.dm_exec_cached_plans sys.dm_exec_cached_plan_dependent_objects sys.dm_exec_requests sys.dm_exec_query_stats sys.dm_exec_procedure_stats

Cache size management

Local memory pressure Global memory pressure

Costing of cache entries

Objects in plan cache: the big picture

Multiple plans in cache When to use stored procedures and other caching mechanisms Troubleshooting plan cache issues

Wait statistics indicating plan cache problems Other caching issues Handling problems with compilation and recompilation

Optimization hints and plan guides

Optimization hints

RECOMPILE OPTIMIZE FOR KEEP PLAN KEEPFIXED PLAN PARAMETERIZATION USE PLAN

Purpose of plan guides Types of plan guides

Object plan guides SQL plan guides Template plan guides

Managing plan guides Plan guide considerations

Plan guide validation Freezing a plan from plan cache

Conclusion

13. Transactions and concurrency

Concurrency models

Pessimistic concurrency Optimistic concurrency

Transaction processing

ACID properties

Atomicity Consistency Isolation Durability

Transaction dependencies

Lost updates Dirty reads Nonrepeatable reads Phantoms

Isolation levels

Read Uncommitted Read Committed Repeatable Read Snapshot Serializable

Locking

Locking basics Spinlocks Lock types for user data

Lock modes Lock granularity Lock duration Lock ownership

Viewing locks

resource_ columns request_ columns

Locking examples Lock compatibility Internal locking architecture

Lock partitioning Lock blocks Lock owner blocks syslockinfo table

Row-level locking vs. page-level locking Lock escalation Deadlocks

Cycle deadlocks Conversion deadlocks Deadlock detection, intervention, and avoidance

Row versioning

Row versioning details Snapshot-based isolation levels

Read committed snapshot isolation Snapshot isolation Snapshot isolation scope Viewing database state Update conflicts DDL and snapshot isolation Summary of snapshot-based isolation levels The version store Management of the version store Snapshot transaction metadata

Choosing a concurrency model

Controlling locking

Lock hints

Setting a lock timeout

Conclusion

14. DBCC internals

Shrinking files and databases

Data file shrinking Log file shrinking DBCC SHRINKFILE AUTO_SHRINK

Consistency checking

Getting a consistent view of the database

Disk space issues Alternatives to using a database snapshot

Processing the database efficiently

Performing fact generation Using the query processor Processing batches Reading the pages to process Enablilng parallelism

Performing primitive system catalog consistency checks Performing allocation consistency checks

Collecting allocation facts Checking allocation facts

Performing per-table logical consistency checks

Metadata Consistency Checks Page audit Data and index page processing

Processing columns

Computed columns Text page processing Cross-page consistency checks

Performing cross-table consistency checks

Service broker consistency checks Cross-catalog consistency checks Indexed-view consistency checks

Understanding DBCC CHECKDB output

Regular output Error reporting to Microsoft SQL Server error log output Application event log output Progress reporting output

Reviewing DBCC CHECKDB options

NOINDEX Repair options ALL_ERRORMSGS EXTENDED_LOGICAL_CHECKS NO_INFOMSGS TABLOCK ESTIMATEONLY PHYSICAL_ONLY DATA_PURITY

Performing database repairs

Repair mechanisms Emergency mode repair What data was deleted by repair?

Using consistency-checking commands other than DBCC CHECKDB

DBCC CHECKALLOC DBCC CHECKTABLE DBCC CHECKFILEGROUP DBCC CHECKCATALOG DBCC CHECKIDENT DBCC CHECKCONSTRAINTS

Conclusion

A. About the authors Index About the Authors Copyright

← Prev
Back
Next →

← Prev
Back
Next →