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