01. 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 Components of the SQL Server engine Protocols Query processor The storage engine SQL Server 2012 configuration Using SQL Server Configuration Manager Managing services SQL Server system configuration Operating system configuration Trace flags SQL Server configuration settings Conclusion 02. The SQLOS NUMA architecture The scheduler Understanding SQL Server schedulers Binding schedulers to CPUs Observing scheduler internals 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 SQL Server Resource Governor Resource Governor overview Resource Governor controls Resource Governor metadata Extended Events Extended Events architecture Event execution life cycle Core concepts Extended Events DDL and querying Extended Events UI Conclusion 03. 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 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 04. 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 Optimizations in tempdb Best practices tempdb 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 05. 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 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 Choosing a backup type Restoring a database Conclusion 06. Table storage Table creation Naming tables and columns Avoiding reserved keywords Using delimited identifiers Understanding naming conventions Choosing a data type The NULL problem User-defined data types IDENTITY property Sequence object Internal storage The sysindexes catalog view Data storage metadata Catalog view queries Data pages The structure of data rows How to find a physical page 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 Updating rows Conclusion 07. 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 sysdm_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 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 Deleting rows Updating rows Table-level vs index-level data modification Logging Locking Fragmentation Managing B-tree index structures Dropping indexes Using the ALTER INDEX command Detecting fragmentation Removing fragmentation Rebuilding an index Online index building Columnstore indexes Creation of columnstore indexes Storage of columnstore indexes Columnstore index metadata Conclusion 08. Special storage Large object storage Restricted-length large object data (row-overflow data) Unrestricted-length large object data 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 Exploring metadata with FILESTREAM data Creating a FileTable Considering performance for FILESTREAM data Summarizing FILESTREAM and FileTable Sparse columns Management of sparse columns Column sets and sparse column manipulation Physical storage Metadata Storage savings with sparse columns Data compression Vardecimal Row compression Page compression Table and index partitioning Partition functions and partition schemes Metadata for partitioning The sliding window benefits of partitioning Partitioning a columnstore index Conclusion 09. Special indexes Special indexes vs ordinary indexes XML indexes Creating and maintaining 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 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 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 Bookmark lookup Joins Aggregations Unions Advanced index operations Subqueries Parallelism 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 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 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=| FORCESEEK FAST MAXDOP 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 Simple parameterization Prepared queries Compiled objects Causes of recompilation Plan cache internals Cache stores Compiled plans Execution contexts Plan cache metadata Cache size management 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 Optimization hints and plan guides Optimization hints Purpose of plan guides Types of plan guides Managing plan guides Plan guide considerations Conclusion 13. Transactions and concurrency Concurrency models Pessimistic concurrency Optimistic concurrency Transaction processing ACID properties Transaction dependencies Isolation levels Locking Locking basics Spinlocks Lock types for user data Viewing locks Locking examples Lock compatibility Internal locking architecture Row-level locking vs page-level locking Lock escalation Deadlocks Row versioning Row versioning details Snapshot-based isolation levels Choosing a concurrency model Controlling locking Lock hints 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 Processing the database efficiently Performing primitive system catalog consistency checks Performing allocation consistency checks Performing per-table logical consistency checks Processing columns Performing cross-table consistency checks Understanding DBCC CHECKDB output Reviewing DBCC CHECKDB options Performing database repairs Using consistency-checking commands other than DBCC CHECKDB Conclusion
2014년 5월 16일 금요일
Microsoft SQL Server 2012 Internals
피드 구독하기:
댓글 (Atom)
댓글 없음:
댓글 쓰기