Home Page Icon
Home Page
Table of Contents for
Title
Close
Title
by Dmitri Korotkevitch
Pro SQL Server Internals
Cover
Title
Copyright
Dedication
Contents at a Glance
Contents
About the Author
About the Technical Reviewers
Acknowledgments
Introduction
Part 1: Tables and Indexes
Chapter 1: Data Storage Internals
Database Files and Filegroups
Data Pages and Data Rows
Large Objects Storage
Row-Overflow Storage
LOB Storage
SELECT * and I/O
Extents and Allocation Map Pages
Data Modifications
Much Ado About Data Row Size
Table Alteration
Summary
Chapter 2: Tables and Indexes: Internal Structure and Access Methods
Heap Tables
Clustered Indexes
Composite Indexes
Nonclustered Indexes
Summary
Chapter 3: Statistics
Introduction to SQL Server Statistics
Column-Level Statistics
Statistics and Execution Plans
Statistics and Query Memory Grants
Statistics Maintenance
SQL Server 2014 Cardinality Estimator
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
Summary
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
Summary
Chapter 5: Index Fragmentation
Types of Fragmentation
FILLFACTOR and PAD_INDEX
Index Maintenance
Designing an Index Maintenance Strategy
Patterns that Increase Fragmentation
Summary
Chapter 6: Designing and Tuning the Indexes
Clustered Index Design Considerations
Design Guidelines
Identities, Sequences, and Uniqueidentifiers
Nonclustered Indexes Design Considerations
Optimizing and Tuning Indexes
Detecting Unused and Inefficient Indexes
Index Consolidation
Detecting Suboptimal Queries
Summary
Part 2: Other things that matter
Chapter 7: Constraints
Primary Key Constraints
Unique Constraints
Foreign Key Constraints
Check Constraints
Wrapping Up
Chapter 8: Triggers
DML Triggers
DDL Triggers
Logon Triggers
UPDATE( ) and COLUMNS_UPDATED( )functions
Nested and Recursive Triggers
First and Last Triggers
CONTEXT_INFO
Summary
Chapter 9: Views
Regular Views
Indexed Views
Partitioned Views
Updatable Views
Summary
Chapter 10: User-Defined Functions
Multi-Statement Functions
Inline Table-Valued Functions
Summary
Chapter 11: XML
To Use or Not to Use XML? That Is the Question!
XML Data Type
Working with XML Data
value() Method
exists() Method
query() Method
nodes() Method
modify() Method
OPENXML
FOR XML
Summary
Chapter 12: Temporary Tables
Temporary Tables
Table Variables
User-Defined Table Types and Table-Valued Parameters
Regular Tables in tempdb
Optimizing tempdb Performance
Summary
Chapter 13: CLR
CLR Integration Overview
Security Considerations
Performance Considerations
Summary
Chapter 14: CLR Types
User-Defined CLR Types
Spatial Data Types
HierarchyId
Summary
Chapter 15: Data Partitioning
Reasons to Partition Data
When to Partition?
Data Partitioning Techniques
Partitioned Tables
Partitioned Views
Comparing Partitioned Tables and Partitioned Views
Using Partitioned Tables and Views Together
Tiered Storage
Moving Non-Partitioned Tables Between Filegroups
Moving Partitions Between Filegroups
Moving Data Files Between Disk Arrays
Tiered Storage in Action
Tiered Storage and High Availability Technologies
Implementing Sliding Window Scenario and Data Purge
Potential Issues
Summary
Chapter 16: System Design Considerations
General System Architecture
Data Access Layer Design
Connection Pooling
Working with Database Tables Directly
Database Views
Stored Procedures
Code Generators and ORM Frameworks
Analyzing Microsoft Entity Framework 6
Connections and Transactions
Executing Stored Procedures and Queries
Data Loading
Parameterization
IN Lists
Deletions
Optimistic Concurrency
Conclusions
Summary
Part 3: Locking, Blocking and Concurrency
Chapter 17: Lock Types
Chapter 18: Troubleshooting Blocking Issues
General Troubleshooting Approach
Troubleshooting Blocking Issues in Real Time
Collecting Blocking Information for Further Analysis
Summary
Chapter 19: Deadlocks
Classic Deadlock
Deadlock Due to Non-Optimized Queries
Deadlock Due to Simultaneous Read/Update Statements
Deadlock Due to Multiple Updates of the Same Row
Deadlock Troubleshooting
Reducing the Chance of Deadlocks
Summary
Chapter 20: Lock Escalation
Lock Escalation Overview
Lock Escalation Troubleshooting
Summary
Chapter 21: Optimistic Isolation Levels
Row Versioning Overview
Optimistic Transaction Isolation Levels
READ COMMITTED SNAPSHOT Isolation Level
SNAPSHOT Isolation Level
Version Store Behavior
Summary
Chapter 22: Application Locks
Chapter 23: Schema Locks
Schema Modification Locks
Multiple Sessions and Lock Compatibility
Lock Partitioning
Low-Priority Locks (SQL Server 2014)
Summary
Chapter 24: Designing Transaction Strategies
Part 4: Query Life Cycle
Chapter 25: Query Optimization and Execution
Query Life Cycle
Query Optimization
Query Execution
Operators
Joins
Aggregates
Spools
Parallelism
Query and Table Hints
INDEX Query Hint
FORCE ORDER Hint
LOOP, MERGE, and HASH JOIN Hints
FORCESEEK/FORCESCAN Hints
NOEXPAND/EXPAND VIEWS Hints
FAST N Hints
Summary
Chapter 26: Plan Caching
Plan Caching Overview
Parameter Sniffing
Plan Reuse
Plan Caching for Ad-Hoc Queries
Auto-Parameterization
Plan Guides
Plan Cache Internals
Examining Plan Cache
Summary
Part 5: Practical Troubleshooting
Chapter 27: System Troubleshooting
Looking at the Big Picture
Hardware and Network
Operating System Configuration
SQL Server Configuration
Database Options
SQL Server Execution Model
Wait Statistics Analysis and Troubleshooting
I/O Subsystem and Non-Optimized Queries
Memory-Related Wait Types
High CPU Load
Parallelism
Locking and Blocking
Worker Thread Starvation
ASYNC_NETWORK_IO Waits
Allocation Map Contention and Tempdb load
Wrapping Up
What to Do When the Server Is Not Responding
Working with Baseline
Summary
Chapter 28: Extended Events
Extended Events Overview
Extended Events Objects
Packages
Events
Predicates
Actions
Types and Maps
Targets
Creating Events Sessions
Working with Event Data
Working with the ring_buffer Target
Working with event_file and asynchronous_file_target Targets
Working with event_counter and synchronous_event_counter Targets
Working with histogram, synchronous_bucketizer, and asynchronous_bucketizer Targets
Working with the pair_matching Target
System_health and AlwaysOn_Health Sessions
Detecting Expensive Queries
Summary
Part 6: Inside the transaction log
Chapter 29: Transaction Log Internals
Data Modifications, Logging, and Recovery
Delayed Durability (SQL Server 2014)
Virtual Log Files
Database Recovery Models
TempDB Logging
Excessive Transaction Log Growth
Transaction Log Management
Summary
Chapter 30: Designing a Backup Strategy
Database Backup Types
Backing Up the Database
Restoring the Database
Restore to a Point in Time
Restore with StandBy
Designing a Backup Strategy
Partial Database Availability and Piecemeal Restore
Partial Database Backup
Backup to Windows Azure
Managed Backup to Windows Azure
Summary
Chapter 31: Designing a High Availability Strategy
SQL Server Failover Cluster
Database Mirroring
AlwaysOn Availability Groups
Log Shipping
Replication
Designing a High Availability Strategy
Summary
Part 7: In-Memory OLTP Engine
Chapter 32: In-Memory OLTP Internals
Why Hekaton?
In-Memory OLTP Engine Architecture and Data Structures
Memory-Optimized Tables
High-Availability Technology Support
Data Row Structure
Hash Indexes
Range Indexes
Statistics on Memory-Optimized Tables
Garbage Collection
Transactions and Concurrency
Transaction Isolation Levels and Data Consistency
In-Memory OLTP Transaction Isolation Levels
Cross-Container Transactions
Data Access, Modifications, and Transaction Lifetime
Transaction Logging
Data Durability and Recovery
Memory Usage Considerations
Summary
Part 8: Columnstore Indexes
Chapter 33: In-Memory OLTP Programmability
Native Compilation
Natively-Compiled Stored Procedures
Optimization of Natively-Compiled Stored Procedures
Creating Natively-Compiled Stored Procedures
Supported T-SQL Features
Execution Statistics
Interpreted T-SQL and Memory-Optimized Tables
Memory-Optimized Table Types and Variables
In-Memory OLTP: Implementation Considerations
Summary
Chapter 34: Introduction to Columnstore Indexes
Data Warehouse Systems Overview
Columnstore Indexes and Batch-Mode Processing Overview
Column-Based Storage and Batch-Mode Execution
Columnstore Indexes and Batch-Mode Execution in Action
Columnstore Index Internals
Nonclustered Columnstore Indexes
Data Storage
Metadata
Design Considerations and Best Practices for Columnstore Indexes
Reducing Data Row Size
Giving SQL Server as Much Information as Possible
Maintaining Statistics
Avoiding String Columns in Fact Tables
Summary
Chapter 35: Clustered Columnstore Indexes
Internal Structure of Clustered Columnstore Indexes
Internal Structure
Data Load
Delta Store and Delete Bitmap
Archival Compression
Clustered Columnstore Index Maintenance
Excessive Number of Partially Populated Row Groups
Large Delta Stores
Large Delete Bitmap
Index Maintenance Options
SQL Server 2014 Batch-Mode Execution Enhancements
Design Considerations
Summary
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Cover
Next
Next Chapter
Copyright
Pro SQL Server Internals
Dmitri Korotkevitch
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset