Pro SQL Server Internals - Part 1
Database Files and Filegroups
Data Pages and Data Rows
Large Objects Storage
Row-Overfl ow Storage
LOB Storage
SELECT * and I/O
Extents and Allocation Map Pages
Data Modifi cations
Much Ado about Data Row Size
Table Alteration
Chapter 2: Tables and Indexes: Internal Structure and Access Methods
Heap Tables
Clustered Indexes
Composite Indexes
Nonclustered Indexes
Chapter 3: Statistics
Introduction to SQL Server Update Statistics
Column-Level Statistics
Statistics and Execution Plans
Statistics and Query Memory Grants
Statistics Maintenance
New Cardinality Estimator (SQL Server 2014–2016)
Comparing Cardinality Estimators: Up-to-Date Statistics
Comparing Cardinality Estimators: Outdated Statistics
Comparing Cardinality Estimators: Indexes with Ever-Increasing Key Values
Comparing Cardinality Estimators: Joins
Comparing Cardinality Estimators: Multiple Predicates
Choosing the Model
Query Optimizer Hotfi xes and Trace Flag T4199
Chapter 4: Special Indexing and Storage Features
Indexes with Included Columns
Filtered Indexes
Filtered Statistics
Calculated Columns
Data Compression
Row Compression
Page Compression
Performance Considerations
Sparse Columns
Chapter 5: SQL Server 2016 Features
Temporal Tables
Stretch Databases Configuring Stretch Database
Querying Stretch Databases
Stretch Database Pricing
Row-Level Security
Performance Impact
Blocking Modifications
Always Encrypted
Always Encrypted Overview
Programmability
Security Considerations and Key Management
Dynamic Data Masking
Performance and Security Considerations
Combining Security Features
Chapter 6: Index Fragmentation
Types of Fragmentation
FILLFACTOR and PAD_INDEX
Index Maintenance
Designing an Index Maintenance Strategy
Patterns That Increase Fragmentation
Chapter 7: Designing and Tuning the Indexes
Clustered Index Design Considerations
Identities, Sequences, and Uniqueidentifiers
Nonclustered Index Design Considerations
Optimizing and Tuning Indexes
Detecting Unused and Inefficient Indexes
Index Consolidation
Detecting Suboptimal Queries
Every database consists of one or more transaction log files and one or more data files. A transaction log stores information about database transactions and all of the data modifications made in each session. Every time the data is modified, SQL Server stores enough information in the transaction log to undo (roll back) or redo (replay) this action, which allows SQL Server to recover the database to a transactionally consistent state in the event of an unexpected failure or crash.
Improve your ability to develop, manage, and troubleshoot SQL Server solutions by learning how different components work “under the hood,” and how they communicate with each other. The detailed knowledge helps in implementing and maintaining high-throughput databases critical to your business and its customers. You’ll learn how to identify the root cause of each problem and understand how different design and implementation decisions affect performance of your systems.
New in this second edition is coverage of SQL Server 2016 Internals, including In-Memory OLTP, columnstore enhancements, Operational Analytics support, Query Store, JSON, temporal tables, stretch databases, security features, and other improvements in the new SQL Server version. The knowledge also can be applied to Microsoft Azure SQL Databases that share the same code with SQL Server 2016.
Pro SQL Server Internals is a book for developers and database administrators, and it covers multiple SQL Server versions starting with SQL Server 2005 and going all the way up to the recently released SQL Server 2016. The book provides a solid road map for understanding the depth and power of the SQL Server database server and teaches how to get the most from the platform and keep your databases running at the level needed to support your business. The book:
• Provides detailed knowledge of new SQL Server 2016 features and enhancements
• Includes revamped coverage of columnstore indexes and In-Memory OLTP
• Covers indexing and transaction strategies
• Shows how various database objects and technologies are implemented internally, and when they should or should not be used
• Demonstrates how SQL Server executes queries and works with data and transaction log
No comments: