On the Journey to be a SQL Server Professional: Notes on Microsoft SQL Server 2012 Internals

1. SQL Server 2012 architecture and configuration

a. SQL Server editions

b. SQL Server installation and tools

c. SQL Server metadata

i. Compatibility views

ii. Catalog views

iii. Dynamic Management Objects

iv. Other metadata

1) Information schema views

2) System functions

3) System stored procedures

4) Metadata wrap-up

d. Components of the SQL Server engine

i. Protocols

ii. Query processor

1) Parsing and binding components

2) The Query Optimizer

3) The query executor

iii. The storage engine

1) Access methods

2) Transaction services

3) Other operations

e. SQL Server 2012 configuration

i. Using SQL Server Configuration Manager

1) Configuring network protocols

2) Implementing a default network configuration

ii. Managing services

f. SQL Server system configuration

i. Operating system configuration

1) Task management

2) System paging file location

3) Nonessential services

4) Connectivity

5) Firewall setting

ii. Trace flags

iii. SQL Server configuration settings

1) Memory options

2) Scheduling options

3) Disk I/O options

4) Query processing options

g. Conclusion

2. The SQLOS

a. NUMA architecture

b. The scheduler

i. Understanding SQL Server schedulers

1) SQL Server workers

2) SQL Server tasks

3) Threads vs. fibers

4) NUMA and schedulers

5) Dynamic affinity

ii. Binding schedulers to CPUs

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

iv. Understanding the Dedicated Administrator Connection (DAC)

c. Memory

i. The buffer pool and the data cache

ii. Column store object pool

iii. Access to in-memory data pages

iv. Page management in the data cache

v. The free buffer list and the lazywriter

vi. Checkpoints

vii. Memory management in other caches

viii. The Memory Broker

ix. Memory sizing

x. Buffer pool sizing

1) DMVs for memory internals

2) NUMA and memory

3) Read-ahead

d. SQL Server Resource Governor

i. Resource Governor overview

1) Classifier function

2) Resource pools

3) Pool sizing

4) Workload groups

5) Code example

ii. Resource Governor controls

iii. Resource Governor metadata

e. Extended Events

i. Extended Events architecture

ii. Event execution life cycle

iii. Core concepts

1) Events

2) Actions

3) Predicates

4) Types and maps

5) Targets

6) Event sessions

iv. Extended Events DDL and querying

1) Creating an event session

2) Querying event data

3) Stopping and removing the event session

v. Extended Events UI

1) Creating and managing event sessions

2) Viewing Target Data

3) Session catalog metadata

4) Session-scoped configuration options

f. Conclusion

3. Databases and database files

a. Working with sample databases

i. AdventureWorks

ii. pubs

iii. Northwind

b. Understanding database files

i. Creating a database

ii. Using CREATE DATABASE: an example

c. Expanding or shrinking a database

i. Automatic file expansion

ii. Manual file expansion

iii. Fast file initialization

iv. Automatic shrinkage

v. Manual shrinkage

1) DBCC SHRINKFILE

2) DBCC SHRINKDATABASE

d. Using database filegroups

i. The default filegroup

ii. A FILEGROUP CREATION example

iii. Filestream filegroups

e. Altering a database

i. ALTER DATABASE examples

ii. Databases under the hood

iii. Space allocation

f. Setting database options

i. State options

1) SINGLE_USER | RESTRICTED_USER | MULTI_USER

2) OFFLINE | ONLINE | EMERGENCY

3) READ_ONLY | READ_WRITE

4) Termination options

ii. Cursor options

iii. Auto options

iv. SQL options

v. Database recovery options

vi. Other database options

g. Understanding database security

i. Database access

ii. Database security

iii. Databases vs. schemas

iv. Principals and schemas

v. Default schemas

h. Moving or copying a database

i. Detaching and reattaching a database

ii. Backing up and restoring a database

i. Understanding compatibility levels

j. Conclusion

4. Special databases

a. System databases

i. Understanding the master database

ii. Understanding the model database

iii. Introducing the tempdb database

iv. Understanding the resource database

v. Understanding the msdb database

vi. Moving system databases

vii. Moving the master database

b. The tempdb database

i. Objects in tempdb

1) User objects

2) Internal objects

3) Version store

ii. Optimizations in tempdb

1) Logging optimizations

2) Allocation and caching optimizations

iii. Best practices

iv. tempdb contention

1) DML contention

2) DDL contention

v. tempdb space monitoring

c. Database snapshots

i. Creating a database snapshot

ii. Understanding space used by database snapshots

iii. Managing your snapshots

d. Partially contained databases

i. Configuring a contained database

ii. Creating contained users

iii. Understanding database collation changes

iv. Detecting uncontained features

e. Conclusion

5. Logging and recovery

a. Transaction log internals

i. Phases of recovery

ii. Page LSNs and recovery

iii. Log reading

iv. The log cache

b. Changes in log size

i. Understanding virtual log files

1) Observing virtual log files

2) Using multiple log files

3) Understanding automatic truncation of virtual log files

ii. Maintaining a recoverable log

iii. Automatically shrinking the log

iv. Viewing the log file size

c. Database backup and restore

i. Understanding the types of backups

ii. Understanding recovery models

1) Minimally logged operations

2) FULL recovery model

3) BULK_LOGGED recovery model

4) SIMPLE recovery model

5) Switching recovery models

iii. Choosing a backup type

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

d. Conclusion

6. Table storage

a. Table creation

i. Naming tables and columns

ii. Avoiding reserved keywords

iii. Using delimited identifiers

iv. Understanding naming conventions

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

vi. The NULL problem

vii. User-defined data types

b. IDENTITY property

c. Sequence object

d. Internal storage

i. The sys.indexes catalog view

ii. Data storage metadata

iii. Catalog view queries

iv. Data pages

1) Page header

2) Data rows for in-row data

3) Row offset array

4) Examining data pages

v. The structure of data rows

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

vii. Storage of fixed-length rows

viii. Storage of variable-length rows

ix. NULLS and variable-length columns

x. Storage of date and time data

xi. Storage of sql_variant data

e. Constraints

i. Constraint names and catalog view information

ii. Constraint failures in transactions and multiple-row data modifications

f. Altering a table

i. Changing a data type

ii. Adding a new column

iii. Adding, dropping, disabling, or enabling a constraint

iv. Dropping a column

v. Internals of altering tables

g. Heap modification internals

i. Allocation structures

ii. Inserting rows

iii. Deleting rows

1) Deleting rows from a heap

2) Reclaiming pages

iv. Updating rows

1) Moving rows

2) Managing forward pointers

3) Updating in place

4) Updating not in place

h. Conclusion

7. Indexes: internals and management

a. Overview

b. SQL Server B-tree indexes

i. Example 1: An index with a large key column

ii. Example 2: An index with a very narrow key column

c. Tools for analyzing indexes

i. Using the dm_db_index_physical_stats DMV

ii. Using sys.dm_db_database_page_allocations

d. Understanding B-tree index structures

i. Clustering key dependency

ii. Nonclustered B-tree indexes

iii. Constraints and indexes

e. Index creation options

i. IGNORE_DUP_KEY

ii. STATISTICS_NORECOMPUTE

iii. MAXDOP

iv. Index placement

f. Physical index structures for B-trees

i. Index row formats

ii. Clustered index structures

iii. Non-leaf level(s) of a clustered index

iv. Analyzing a clustered index structure

v. 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)

g. Indexes on computed columns and indexed views

i. SET options

ii. Permissible functions

iii. Schema binding

iv. Indexes on computed columns

v. Implementation of a computed column

vi. Persisted columns

vii. Indexed views

viii. Additional requirements

ix. Creating an indexed view

x. Using an indexed view

h. Data modification internals

i. Inserting rows

ii. Splitting pages

1) Splitting the root page of an index

2) Splitting an intermediate index page

3) Splitting a leaf-level page

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

iv. Updating rows

1) Moving rows

2) Managing forwarding pointers

3) Updating in place

4) Updating not in place

v. Table-level vs. index-level data modification

vi. Logging

vii. Locking

viii. Fragmentation

i. Managing B-tree index structures

i. Dropping indexes

ii. Using the ALTER INDEX command

1) Rebuilding an index

2) Disabling an index

3) Changing index options

4) Reorganizing an index

iii. Detecting fragmentation

iv. Removing fragmentation

v. Rebuilding an index

vi. Online index building

j. Columnstore indexes

i. Creation of columnstore indexes

ii. Storage of columnstore indexes

iii. Columnstore index metadata

k. Conclusion

8. Special storage

a. Large object storage

i. Restricted-length large object data (row-overflow data)

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

b. FILESTREAM and FileTable data

i. Enabling FILESTREAM data for SQL Server

ii. Creating a FILESTREAM-enabled database

iii. Creating a table to hold FILESTREAM data

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

v. Exploring metadata with FILESTREAM data

vi. Creating a FileTable

vii. Considering performance for FILESTREAM data

viii. Summarizing FILESTREAM and FileTable

c. Sparse columns

i. Management of sparse columns

1) Creating a table

2) Altering a table

ii. Column sets and sparse column manipulation

iii. Physical storage

iv. Metadata

v. Storage savings with sparse columns

d. Data compression

i. Vardecimal

ii. Row compression

1) Enabling row compression

2) New row format

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

e. Table and index partitioning

i. Partition functions and partition schemes

ii. Metadata for partitioning

iii. The sliding window benefits of partitioning

iv. Partitioning a columnstore index

f. Conclusion

9. Special indexes

a. Special indexes vs. ordinary indexes

b. XML indexes

i. Creating and maintaining XML indexes

1) Primary XML index

2) Secondary XML indexes

ii. Using XQuery in SQL Server: internals

iii. Understanding how a query plan uses an XML index

iv. Using secondary XML indexes

v. Working with XML indexes and schema-validated columns

vi. Using XML-specific information in query plans

c. Spatial indexes

i. Purpose of spatial indexes

ii. Composition of the spatial index

iii. How a spatial query uses a spatial index

iv. How to ensure that your spatial index is being used

v. Spatial query plans and spatial indexes

vi. Nearest neighbor optimization in SQL Server 2012

vii. Spatial index diagnostic stored procedures

viii. Diagnostics with the SQL Server 2012 spatial functions

d. Full-text indexes

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

ii. Full-text index metadata views

iii. Full-text index creation

iv. Maintenance of a full-text index

v. Full-text status metadata, configuration, and diagnostic information

vi. How a full-text index is used in a query

vii. A full-text query plan

viii. Extended event information for full-text queries

e. Semantic indexes

f. Conclusion

10. Query execution

a. Introducing query processing and execution

i. Iterators

ii. Properties of iterators

1) Memory consumption

2) Nonblocking vs. blocking iterators

3) Dynamic cursor support

b. Reading query plans

i. Graphical plans

ii. Text plans

iii. XML plans

iv. Estimated vs. actual query plans

v. Query plan display options

c. Analyzing plans

i. Scans and seeks

ii. Seekable predicates and covered columns

1) Single-column indexes

2) Composite indexes

3) Identifying index keys

iii. Bookmark lookup

iv. Joins

1) Nested loops join

2) Merge join

3) Hash join

4) Summary of join properties

v. Aggregations

1) Scalar aggregation

2) Stream aggregation

3) Hash aggregation

vi. Unions

vii. Advanced index operations

1) Dynamic index seeks

2) Index unions

3) Index intersections

viii. Subqueries

1) Noncorrelated scalar subqueries

2) Correlated scalar subqueries

3) Removing correlations

4) Subqueries in CASE expressions

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

x. Inserts, updates, and deletes

d. Understanding data warehouses

e. Using columnstore indexes and batch processing

i. Adding new data

ii. Hints

f. Conclusion

11. The Query Optimizer

a. Overview

i. Understanding the tree format

ii. Understanding optimization

b. Search space and heuristics

i. Rules

ii. Properties

iii. Storage of alternatives: the Memo

iv. Operators

1) Compute Scalar: Project

2) Compute Sequence: Sequence Project

3) semi-join

4) Apply

5) Spools

6) Exchange

c. Optimizer architecture

i. Before optimization

ii. Simplification

iii. Trivial plan/auto-parameterization

iv. Limitations

v. The Memo: exploring multiple plans efficiently

d. Statistics, cardinality estimation, and costing

i. Statistics design

ii. Density/frequency information

iii. Filtered statistics

iv. String statistics

v. Cardinality estimation details

vi. Limitations

vii. Costing

e. Index selection

i. Filtered indexes

ii. Indexed views

f. Partitioned tables

i. Partition-aligned index views

g. Windowing functions

h. Data warehousing

i. Columnstore indexes

ii. Batch mode processing

1) Grouping rows for repeated operations

2) Column orientation within batches

3) Data encoding

4) Logical database design best practices

iii. Plan shape

iv. Columnstore limitations and workarounds

i. Updates

i. Halloween Protection

ii. Split/Sort/Collapse

iii. Merge

iv. Wide update plans

v. Non-updating updates

vi. Sparse column updates

vii. Partitioned updates

viii. Locking

ix. Partition-level lock escalation

j. Distributed query

k. Extended indexes

l. Plan hinting

i. Debugging plan issues

ii. {HASH | ORDER} GROUP

iii. {MERGE | HASH | CONCAT} UNION

iv. FORCE ORDER, {LOOP | MERGE | HASH} JOIN

v. INDEX=<indexname> | <indexid>

vi. FORCESEEK

vii. FAST <number_rows>

viii. MAXDOP <N>

ix. OPTIMIZE FOR

x. PARAMETERIZATION {SIMPLE | FORCED}

xi. NOEXPAND

xii. USE PLAN

m. Hotfixes

n. Conclusion

12. Plan caching and recompilation

a. The plan cache

i. Plan cache metadata

ii. Clearing plan cache

b. Caching mechanisms

i. Ad hoc query caching

ii. Optimizing for ad hoc workloads

1) Controlling the optimize for ad hoc workloads setting

2) The compiled plan stub

iii. Simple parameterization

1) Forced parameterization

2) Drawbacks of simple parameterization

iv. Prepared queries

1) The sp_executesql procedure

2) The prepare-and-execute method

3) Caching prepared queries

v. Compiled objects

1) Stored procedures

2) Functions

vi. Causes of recompilation

1) Correctness-based recompiles

2) Optimality-based recompiles

3) Skipping the recompilation step

4) Multiple recompilations

5) Removing plans from cache

c. Plan cache internals

i. Cache stores

ii. Compiled plans

iii. Execution contexts

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

v. Cache size management

1) Local memory pressure

2) Global memory pressure

vi. Costing of cache entries

d. Objects in plan cache: the big picture

i. Multiple plans in cache

ii. When to use stored procedures and other caching mechanisms

iii. Troubleshooting plan cache issues

1) Wait statistics indicating plan cache problems

2) Other caching issues

3) Handling problems with compilation and recompilation

e. Optimization hints and plan guides

i. Optimization hints

1) RECOMPILE

2) OPTIMIZE FOR

3) KEEP PLAN

4) KEEPFIXED PLAN

5) PARAMETERIZATION

6) USE PLAN

ii. Purpose of plan guides

iii. Types of plan guides

1) Object plan guides

2) SQL plan guides

3) Template plan guides

iv. Managing plan guides

v. Plan guide considerations

1) Plan guide validation

2) Freezing a plan from plan cache

f. Conclusion

13. Transactions and concurrency

a. Concurrency models

i. Pessimistic concurrency

ii. Optimistic concurrency

b. Transaction processing

i. ACID properties

1) Atomicity

2) Consistency

3) Isolation

4) Durability

ii. Transaction dependencies

1) Lost updates

2) Dirty reads

3) Nonrepeatable reads

4) Phantoms

iii. Isolation levels

1) Read Uncommitted

2) Read Committed

3) Repeatable Read

4) Snapshot

5) Serializable

c. Locking

i. Locking basics

ii. Spinlocks

iii. Lock types for user data

1) Lock modes

2) Lock granularity

3) Lock duration

4) Lock ownership

iv. Viewing locks

1) resource_ columns

2) request_ columns

v. Locking examples

vi. Lock compatibility

vii. Internal locking architecture

1) Lock partitioning

2) Lock blocks

3) Lock owner blocks

4) syslockinfo table

viii. Row-level locking vs. page-level locking

ix. Lock escalation

x. Deadlocks

1) Cycle deadlocks

2) Conversion deadlocks

3) Deadlock detection, intervention, and avoidance

d. Row versioning

i. Row versioning details

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

iii. Choosing a concurrency model

e. Controlling locking

i. Lock hints

1) Setting a lock timeout

f. Conclusion

14. DBCC internals

a. Shrinking files and databases

i. Data file shrinking

ii. Log file shrinking

iii. DBCC SHRINKFILE

iv. AUTO_SHRINK

b. Consistency checking

i. Getting a consistent view of the database

1) Disk space issues

2) Alternatives to using a database snapshot

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

iii. Performing primitive system catalog consistency checks

iv. Performing allocation consistency checks

1) Collecting allocation facts

2) Checking allocation facts

v. Performing per-table logical consistency checks

1) Metadata Consistency Checks

2) Page audit

3) Data and index page processing

vi. Processing columns

1) Computed columns

2) Text page processing

3) Cross-page consistency checks

vii. Performing cross-table consistency checks

1) Service broker consistency checks

2) Cross-catalog consistency checks

3) Indexed-view consistency checks

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

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

x. Performing database repairs

1) Repair mechanisms

2) Emergency mode repair

3) What data was deleted by repair?

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

c. Conclusion