SQL Server Query Tuning and Optimization

by Benjamin Nevarez

Programming

Book Details

Book Title

SQL Server Query Tuning and Optimization

Author

Benjamin Nevarez

Publisher

Packt Publishing

Publication Date

2022

ISBN

9781803242620

Number of Pages

446

Language

English

Format

PDF

File Size

6.5MB

Subject

SQL Server Query

Table of Contents

  • Cover
  • Title Page
  • Copyright
  • Dedication
  • Contributors
  • Table of Contents
  • Preface
  • Chapter 1: An Introduction to Query Tuning and Optimization
  • Query Processor Architecture
  • Analyzing execution plans
  • Getting plans from a trace or the plan cache
  • SET STATISTICS TIME and IO statements
  • Summary
  • Chapter 2: Troubleshooting Queries
  • DMVs and DMFs
  • SQL Trace
  • Extended events
  • The Data Collector
  • Summary
  • Chapter 3: The Query Optimizer
  • Query optimization research
  • Introduction to query processing
  • The sys.dm_exec_query_optimizer_info DMV
  • Parsing and binding
  • Simplification
  • Trivial plan optimization
  • Joins
  • Transformation rules
  • The Memo
  • Statistics
  • Full optimization
  • Summary
  • Chapter 4: The Execution Engine
  • Data access operators
  • Aggregations
  • Joins
  • Parallelism
  • Updates
  • Summary
  • Chapter 5: Working with Indexes
  • Introduction to indexes
  • Creating indexes
  • Understanding index operations
  • The Database Engine Tuning Advisor
  • Missing indexes
  • Index fragmentation
  • Unused indexes
  • Summary
  • Chapter 6: Understanding Statistics
  • Exploring statistics
  • Histograms
  • A new cardinality estimator
  • Cardinality estimation errors
  • Incremental statistics
  • Statistics on computed columns
  • Filtered statistics
  • Statistics on ascending keys
  • UPDATE STATISTICS with ROWCOUNT and PAGECOUNT
  • Statistics maintenance
  • Cost estimation
  • Summary
  • Chapter 7: In-Memory OLTP
  • In-memory OLTP architecture
  • Tables and indexes
  • Natively compiled stored procedures
  • Limitations and later enhancements
  • Summary
  • Chapter 8: Understanding Plan Caching
  • Batch compilation and recompilation
  • Exploring the plan cache
  • Understanding parameterization
  • Parameter sniffing
  • Parameter-sensitive plan optimization
  • Summary
  • Chapter 9: The Query Store
  • Using the Query Store
  • Querying the Query Store
  • Summary
  • Chapter 10: Intelligent Query Processing
  • Overview of intelligent query processing
  • Memory grant feedback
  • Cardinality estimation feedback
  • Degree of parallelism feedback
  • Interleaved execution
  • Table variable deferred compilation
  • Adaptive joins
  • Summary
  • Chapter 11: An Introduction to Data Warehouses
  • Data warehouses
  • Star join query optimization
  • Columnstore indexes
  • Summary
  • Chapter 12: Understanding Query Hints
  • Breaking down complex queries
  • Hints
  • Joins
  • Aggregations
  • FORCE ORDER
  • The INDEX, FORCESCAN, and FORCESEEK hints
  • FAST N
  • The NOEXPAND and EXPAND VIEWS hints
  • Plan guides
  • USE PLAN
  • Summary
  • Index
  • About Packt
  • Other Books You May Enjoy