دانلود کتاب SQL Server Query Tuning and Optimization: Optimize Microsoft SQL Server 2022 queries and applications

فهرست مطالب :

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