Notes on Microsoft SQL Server 2012 Internals

                                         i.     Compatibility views
                                       ii.     Catalog views
                                     iii.     Dynamic Management Objects
                                      iv.     Other metadata
                                         i.     Protocols
                                       ii.     Query processor
                                     iii.     The storage engine
1)    Access methods
                                         i.     Using SQL Server Configuration Manager
                                       ii.     Managing services
                                         i.     Operating system configuration
1)    Task management
4)    Connectivity
                                       ii.     Trace flags
                                     iii.     SQL Server configuration settings
1)    Memory options
g.     Conclusion
2.     The SQLOS
a.     NUMA architecture
b.     The scheduler
                                         i.     Understanding SQL Server schedulers
                                       ii.     Binding schedulers to CPUs
                                     iii.     Observing scheduler internals
4)    sys.dm_os_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
2)    NUMA and memory
3)    Read-ahead
                                         i.     Resource Governor overview
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
                                        v.     Extended Events UI
f.      Conclusion
                                         i.     AdventureWorks
                                       ii.     pubs
                                     iii.     Northwind
                                         i.     Creating a database
                                       ii.     Using CREATE DATABASE: an example
                                         i.     Automatic file expansion
                                       ii.     Manual file expansion
                                     iii.     Fast file initialization
                                      iv.     Automatic shrinkage
                                        v.     Manual shrinkage
1)    DBCC SHRINKFILE
                                         i.     The default filegroup
                                       ii.     A FILEGROUP CREATION example
                                     iii.     Filestream filegroups
                                         i.     ALTER DATABASE examples
                                       ii.     Databases under the hood
                                     iii.     Space allocation
                                         i.     State options
                                       ii.     Cursor options
                                     iii.     Auto options
                                      iv.     SQL options
                                        v.     Database recovery options
                                      vi.     Other database options
                                         i.     Database access
                                       ii.     Database security
                                     iii.     Databases vs. schemas
                                      iv.     Principals and schemas
                                        v.     Default schemas
                                         i.     Detaching and reattaching a database
                                       ii.     Backing up and restoring a database
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
                                         i.     Objects in tempdb
1)    User objects
3)    Version store
                                       ii.     Optimizations in tempdb
                                     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
                                         i.     Configuring a contained database
                                       ii.     Creating contained users
                                     iii.     Understanding database collation changes
                                      iv.     Detecting uncontained features
e.     Conclusion
                                         i.     Phases of recovery
                                       ii.     Page LSNs and recovery
                                     iii.     Log reading
                                      iv.     The log cache
                                         i.     Understanding virtual log files
                                       ii.     Maintaining a recoverable log
                                     iii.     Automatically shrinking the log
                                      iv.     Viewing the log file size
                                         i.     Understanding the types of backups
                                       ii.     Understanding recovery models
                                     iii.     Choosing a backup type
                                      iv.     Restoring a database
3)    Restoring pages
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
                                      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
                                        v.     The structure of data rows
                                      vi.     How to find a physical page
                                    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
                                         i.     Allocation structures
                                       ii.     Inserting rows
                                     iii.     Deleting rows
                                      iv.     Updating rows
1)    Moving rows
h.     Conclusion
a.     Overview
                                         i.     Example 1: An index with a large key column
                                       ii.     Example 2: An index with a very narrow key column
                                         i.     Using the dm_db_index_physical_stats DMV
                                       ii.     Using sys.dm_db_database_page_allocations
                                         i.     Clustering key dependency
                                       ii.     Nonclustered B-tree indexes
                                     iii.     Constraints and indexes
                                         i.     IGNORE_DUP_KEY
                                       ii.     STATISTICS_NORECOMPUTE
                                     iii.     MAXDOP
                                      iv.     Index placement
                                         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
                                         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
                                         i.     Inserting rows
                                       ii.     Splitting pages
                                     iii.     Deleting rows
                                      iv.     Updating rows
1)    Moving rows
                                        v.     Table-level vs. index-level data modification
                                      vi.     Logging
                                    vii.     Locking
                                  viii.     Fragmentation
                                         i.     Dropping indexes
                                       ii.     Using the ALTER INDEX command
                                     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
                                         i.     Restricted-length large object data (row-overflow data)
                                       ii.     Unrestricted-length large object 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
                                        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
                                       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
2)    New row format
                                     iii.     Page compression
                                         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
b.     XML indexes
                                         i.     Creating and maintaining 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
                                       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
                                         i.     Iterators
                                       ii.     Properties of iterators
                                         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
                                     iii.     Bookmark lookup
                                      iv.     Joins
2)    Merge join
3)    Hash join
                                        v.     Aggregations
                                      vi.     Unions
                                    vii.     Advanced index operations
2)    Index unions
                                  viii.     Subqueries
                                      ix.     Parallelism
3)    Parallel scan
4)    Load balancing
                                        x.     Inserts, updates, and deletes
                                         i.     Adding new data
                                       ii.     Hints
f.      Conclusion
a.     Overview
                                         i.     Understanding the tree format
                                       ii.     Understanding optimization
                                         i.     Rules
                                       ii.     Properties
                                     iii.     Storage of alternatives: the Memo
                                      iv.     Operators
3)    semi-join
4)    Apply
5)    Spools
6)    Exchange
                                         i.     Before optimization
                                       ii.     Simplification
                                     iii.     Trivial plan/auto-parameterization
                                      iv.     Limitations
                                        v.     The Memo: exploring multiple plans efficiently
                                         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
h.     Data warehousing
                                         i.     Columnstore indexes
                                       ii.     Batch mode processing
3)    Data encoding
                                     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
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
                                     iii.     Simple parameterization
                                      iv.     Prepared queries
                                        v.     Compiled objects
2)    Functions
                                      vi.     Causes of recompilation
                                         i.     Cache stores
                                       ii.     Compiled plans
                                     iii.     Execution contexts
                                      iv.     Plan cache metadata
1)    Handles
                                        v.     Cache size management
                                      vi.     Costing of cache entries
                                         i.     Multiple plans in cache
                                       ii.     When to use stored procedures and other caching mechanisms
                                     iii.     Troubleshooting plan cache issues
                                         i.     Optimization hints
1)    RECOMPILE
2)    OPTIMIZE FOR
3)    KEEP PLAN
4)    KEEPFIXED PLAN
6)    USE PLAN
                                       ii.     Purpose of plan guides
                                     iii.     Types of plan guides
2)    SQL plan guides
                                      iv.     Managing plan guides
                                        v.     Plan guide considerations
f.      Conclusion
a.     Concurrency models
                                         i.     Pessimistic concurrency
                                       ii.     Optimistic concurrency
                                         i.     ACID properties
1)    Atomicity
2)    Consistency
3)    Isolation
4)    Durability
                                       ii.     Transaction dependencies
1)    Lost updates
2)    Dirty reads
4)    Phantoms
                                     iii.     Isolation levels
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
3)    Lock duration
4)    Lock ownership
                                      iv.     Viewing locks
                                        v.     Locking examples
                                      vi.     Lock compatibility
                                    vii.     Internal locking architecture
2)    Lock blocks
                                  viii.     Row-level locking vs. page-level locking
                                      ix.     Lock escalation
                                        x.     Deadlocks
1)    Cycle deadlocks
d.     Row versioning
                                         i.     Row versioning details
                                       ii.     Snapshot-based isolation levels
                                     iii.     Choosing a concurrency model
                                         i.     Lock hints
f.      Conclusion
                                         i.     Data file shrinking
                                       ii.     Log file shrinking
                                     iii.     DBCC SHRINKFILE
                                      iv.     AUTO_SHRINK
                                         i.     Getting a consistent view of the database
                                       ii.     Processing the database efficiently
                                     iii.     Performing primitive system catalog consistency checks
                                      iv.     Performing allocation consistency checks
                                        v.     Performing per-table logical consistency checks
2)    Page audit
                                      vi.     Processing columns
                                    vii.     Performing cross-table consistency checks
                                  viii.     Understanding DBCC CHECKDB output
1)    Regular output
                                      ix.     Reviewing DBCC CHECKDB options
1)    NOINDEX
2)    Repair options
3)    ALL_ERRORMSGS
5)    NO_INFOMSGS
6)    TABLOCK
7)    ESTIMATEONLY
8)    PHYSICAL_ONLY
9)    DATA_PURITY
                                        x.     Performing database repairs
                                      xi.     Using consistency-checking commands other than DBCC CHECKDB
1)    DBCC CHECKALLOC
2)    DBCC CHECKTABLE
5)    DBCC CHECKIDENT
c.     Conclusion