SQL Server Query Tuning and Optimization: Optimize Microsoft SQL Server 2022 queries and applications

✍ Scribed by Benjamin Nevarez

• Publisher: Packt Publishing
• Year: 2022
• Tongue: English
• Leaves: 446
• Category: Library

✦ Synopsis

Get well-versed with ready-to-use techniques for creating high-performance queries and applications

Key Features

• Speed up queries and dramatically improve application performance by both understanding query engine internals and practical query optimization
• Understand how the query optimizer works
• Learn about intelligent query processing and what is new in SQL Server 2022

Book Description

SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications.

This book starts by describing the inner workings of the query optimizer, and will enable you to use this knowledge to write better queries and provide the query engine with all the information it needs to produce efficient execution plans. As you progress, you'll get practical query optimization tips for troubleshooting underperforming queries. The book will also guide you through intelligent query processing and what is new in SQL Server 2022. Query performance topics such as the Query Store, In-Memory OLTP and columnstore indexes are covered as well.

By the end of this book, you'll be able to get the best possible performance for your queries and applications.

What you will learn

• Troubleshoot queries using methods including extended events, SQL Trace, and dynamic management views
• Understand how the execution engine and query operators work
• Speed up queries and improve app performance by creating the right indexes
• Detect and fix cardinality estimation errors by examining query optimizer statistics
• Monitor and promote both plan caching and plan reuse to improve app performance
• Troubleshoot and improve query performance by using the Query Store
• Improve the performance of data warehouse queries by using columnstore indexes
• Handle query processor limitations with hints and other methods

Who this book is for

This book is for SQL Server developers who are struggling with slow query execution, database administrators who are tasked with troubleshooting slow application performance, and database architects who design SQL Server databases in support of line-of-business and data warehousing applications.

Table of Contents

1. An Introduction to Query Tuning and Optimization
2. Troubleshooting Queries
3. The Query Optimizer
4. The Execution Engine
5. Working with Indexes
6. Understanding Statistics
7. In-Memory OLTP
8. Understanding Plan Caching
9. The Query Store
10. Intelligent Query Processing
11. An Introduction to Data Warehouses
12. Understanding Query Hints

✦ 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
Parsing and binding
Query optimization
Generating candidate execution plans
Plan cost evaluation
Query execution and plan caching
Analyzing execution plans
Graphical plans
XML plans
Text plans
Plan properties
Warnings on execution plans
Getting plans from a trace or the plan cache
sys.dm_exec_query_plan DMF
SQL Trace/Profiler
Extended events
Removing plans from the plan cache
SET STATISTICS TIME and IO statements
Summary
Chapter 2: Troubleshooting Queries
DMVs and DMFs
sys.dm_exec_requests and sys.dm_exec_sessions
Sys.dm_exec_query_stats
Understanding statement_start_offset and statement_end_offset
sql_handle and plan_handle
query_hash and plan_hash
Finding expensive queries
Blocking and waits
SQL Trace
Extended events
Mapping SQL Trace events to extended events
Working with extended events
The Data Collector
Configuration
Using the Data Collector
Querying the Data Collector tables
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
Contradiction detection
Foreign Key Join elimination
Trivial plan optimization
Joins
Transformation rules
The Memo
Statistics
Full optimization
Search 0
Search 1
Search 2
Summary
Chapter 4: The Execution Engine
Data access operators
Scans
Seeks
Bookmark lookup
Aggregations
Sorting and hashing
Stream Aggregate
Hash Aggregate
Distinct Sort
Joins
Nested Loops Join
Merge Join
Hash Join
Parallelism
The exchange operator
Limitations
Updates
Per-row and per-index plans
Halloween protection
Summary
Chapter 5: Working with Indexes
Introduction to indexes
Creating indexes
Clustered indexes versus heaps
Clustered index key
Covering indexes
Filtered indexes
Understanding index operations
The Database Engine Tuning Advisor
Tuning a workload using the plan cache
Offload of tuning overhead to test server
Missing indexes
Index fragmentation
Unused indexes
Summary
Chapter 6: Understanding Statistics
Exploring statistics
Creating and updating statistics
Inspecting statistics objects
The density vector
Histograms
A new cardinality estimator
Trace flag 4137
Cardinality estimation errors
Incremental statistics
Statistics on computed columns
Filtered statistics
Statistics on ascending keys
Trace flag 2389
UPDATE STATISTICS with ROWCOUNT and PAGECOUNT
Statistics maintenance
Cost estimation
Summary
Chapter 7: In-Memory OLTP
In-memory OLTP architecture
Tables and indexes
Creating in-memory OLTP tables
Hash indexes
Nonclustered or range indexes
Natively compiled stored procedures
Creating natively compiled stored procedures
Inspecting DLLs
Limitations and later enhancements
Summary
Chapter 8: Understanding Plan Caching
Batch compilation and recompilation
Exploring the plan cache
How to remove plans from memory
Understanding parameterization
Autoparameterization
The Optimize for Ad Hoc Workloads option
Forced parameterization
Stored procedures
Parameter sniffing
Optimizing for a typical parameter
Optimizing on every execution
Local variables and the OPTIMIZE FOR UNKNOWN hint
Disabling parameter sniffing
Parameter sniffing and SET options affecting plan reuse
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
Parameter-sensitive plan optimization
Memory grant feedback
Persistence and percentile
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
Performance benefits
Batch mode processing
Creating columnstore indexes
Hints
Summary
Chapter 12: Understanding Query Hints
Breaking down complex queries
OR logic in the WHERE clause
Joins on aggregated datasets
Hints
When to use hints
Types of 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