SQL Server 2022 Query Performance Tuning: Troubleshoot and Optimize Query Performance

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Query Performance Tuning
	The Query Performance Tuning Process
		Performance Issues
		A Repetitive Process
	Understanding What Defines “Good Enough”
	Establishing Comparison Points
	Most Likely Performance Issues
	Common Performance Issues
		Insufficient or Poor Indexes
		Inaccurate or Missing Statistics
		Bad T-SQL
		Problematic Execution Plans
		Excessive Blocking
		Deadlocks
		Non-Set-Based Operations
		Incorrect Database Design
		Poor Execution Plan Reuse
		Frequent Recompilation of Queries
	Summary
Chapter 2: Execution Plan Generation and the Query Optimizer
	The Query Optimization Process
		Optimization Preparation
			Parsing
			Binding
	Optimization
		Simplification
		Trivial Plan Match
		Optimization Phases
	Generating Parallel Execution Plans
	Execution Plan Caching
		Aging of the Execution Plan
	Summary
Chapter 3: Methods for Capturing Query Performance Metrics
	Methods to Capture Query Performance Metrics
		Include Client Statistics
		Connection Properties
		SET STATISTICS TIME/IO
		QueryTimeStats in the Execution Plan
		Trace Events (Profiler)
	Dynamic Management Views
		Actively Executing Queries
		Previously Executed Queries
	Query Store
	Extended Events
		Creating an Extended Events Session
		Adding and Configuring Events
			Adding Global Fields to Events
			Using Predicates with Events
			Optional Event Fields
		Defining Targets
			Using the event_file Target
			Using the histogram Target
		Working with Sessions
		Adding Causality Tracking
		Scripting Extended Events
		Live Data Explorer Window
			Filtering Live Data
			Aggregating Live Data
		General Recommendations for Using Extended Events
			Set Max File Size Appropriately
			Avoid Debug Events
			Avoid the Use of No_Event_Loss
	Summary
Chapter 4: Analyzing Query Behavior Using Execution Plans
	Estimated vs. Actual Execution Plans
	Capturing Execution Plans
		SQL Server Management Studio
		Dynamic Management Views
		Query Store
		Extended Events
	What Is Inside an Execution Plan
		Reading an Execution Plan
	What Do You Look for in an Execution Plan?
		First Operator
		Warnings
		Most Costly Operations
		Fat Pipes
		Extra Operators
		Scans
		Estimate vs. Actual
		After the Guideposts
	Tools That Assist You with Execution Plans
		SQL Server Management Studio
			Find Node
			Compare Plans
			Live Query Statistics
		Third Party
			Solar Winds Plan Explorer
			Supratimas
			Paste The Plan
	Summary
Chapter 5: Statistics, Data Distribution, and Cardinality
	Statistics in the Query Optimization Process
		Statistics on Rowstore Indexed Columns
			Benefits of Updated Statistics
			Drawbacks of Outdated Statistics
		Statistics on Nonindexed Columns
			Benefits of Statistics on a Nonindexed Column
			Comparing Performance with Missing Statistics
	Analyzing Statistics
		Header
		Density
		Histogram
		Cardinality
		Statistics on a Multicolumn Index
		Statistics on a Filtered Index
		Controlling the Cardinality Estimator
	Statistics Maintenance
		Auto Create Statistics
		Auto Update Statistics
		Auto Update Statistics Asynchronously
		Manual Maintenance
		Manage Statistics Settings
		Create Statistics Manually
	Analyzing the Effectiveness of Statistics for a Query
		Resolving a Missing Statistics Issue
		Resolving an Outdated Statistics Issue
	Recommendations on Statistics
		Backward Compatibility of Statistics
		Auto Create Statistics
		Auto Update Statistics
		Automatic Update Statistics Asynchronously
		Amount of Sampling to Collect Statistics
	Summary
Chapter 6: Using Query Store for Query Performance and Execution Plans
	Query Store Function and Design
		Information Collected by Query Store
		Query Runtime Data
	Controlling Query Store
		Capture Mode
	Query Store Reporting
	Plan Forcing
		Forcing Query Hints
		Optimized Plan Forcing
	Query Store for Upgrades
	Summary
Chapter 7: Execution Plan Cache Behavior
	Querying the Plan Cache
	Execution Plan Caching and Plan Reuse
		Ad Hoc Workload
			Optimize for Ad Hoc Workload
			Simple Parameterization
			Forced Parameterization
		Prepared Workload
			Stored Procedure
			Stored Procedure Compiled on Initial Execution
			Performance Benefits of Stored Procedures
			Nonperformance Benefits of Stored Procedures
		sp_executesql
		Prepare/Execute Model
	Query Hash and Query Plan Hash
	Execution Plan Cache Recommendations
		Explicitly Parameterize Values in Your Query
		Use Stored Procedures Where You Can
		Use sp_executesql As an Alternative to Stored Procedures
		Take Advantage of the Prepare/Execute Model
		Avoid Ad Hoc Queries
		Enable Optimize For Ad Hoc
	Summary
Chapter 8: Query Recompilation
	Benefits and Drawbacks of Recompilation
	Identifying the Statement Being Recompiled
	Analyzing Causes of Recompilation
		Deferred Object Resolution
			Recompilation on a Table
			Recompilation on a Temporary Table
	Avoiding Recompiles
		Avoid Interleaving DDL and DML Statements
		Reduce Recompilation Caused by Statistics Changes
			Use the KEEPFIXED PLAN Hint
			Disable Automatic Statistics Maintenance on a Table
		Use Table Variables
		Use Temporary Tables Across Multiple Scopes
		Avoid Changing SET Options Within a Batch
	Controlling Recompile Results
		Plan Forcing
		Query Hints
		Plan Guides
		Hint Forcing
	Summary
Chapter 9: Index Architecture
	What Is a Rowstore Index?
		The Benefits of Indexes
		Index Overhead
	What Is a Columnstore Index?
		Columnstore Index Storage
	Index Design Recommendations
		Type of Query Processing Being Performed
		Determine Filtering Criteria
		Use Narrow Indexes
		Consider Selectivity of the Data
		Determine Data Type
		Consider Column Order
		Determine Data Storage
	Rowstore Index Behavior
		Clustered Indexes
			Heap Tables
			Relationships with Nonclustered Indexes
			Clustered Index Recommendations
				Create the Clustered Index First
				Keep Clustered Indexes Narrow
				Rebuild the Clustered Index in a Single Step
				Where Possible, Make the Clustered Index Unique
			When to Use a Clustered Index
				Accessing the Data Directly
				Retrieving Presorted Data
			Poor Design Practices for a Clustered Index
				Frequently Updated Columns
				Wide Keys
		Nonclustered Indexes
			Nonclustered Index Maintenance
			Defining the Lookup Operation
			Nonclustered Index Recommendations
				When to Use a Nonclustered Index
				When Not to Use a Nonclustered Index
	Columnstore Index Behavior
		Columnstore Recommendations
	Summary
Chapter 10: Index Behaviors
	Covering Indexes
		A Pseudoclustered Index
		Recommendations
	Index Intersection
	Index Joins
	Filtered Indexes
	Indexed Views
		Benefit
		Overhead
		Usage Scenarios
	Index Compression
	Index Characteristics
		Different Column Sort Order
		Index on Computed Columns
		CREATE INDEX Statement Processed As a Query
		Parallel Index Creation
		Online Index Creation
		Considering the Database Engine Tuning Advisor
		OPTIMIZE_FOR_SEQUENTIAL_KEY
		Resumable Indexes and Constraints
	Special Index Types
		Full-Text
		Spatial
		XML
	Summary
Chapter 11: Key Lookups and Solutions
	Purpose of Lookups
	Performance Issues Caused by Lookups
	Analysis of the Causes of Lookups
	Techniques to Resolve Lookups
		Create a Clustered Index
		Use a Covering Index
		Take Advantage of Index Joins
	Summary
Chapter 12: Dealing with Index Fragmentation
	Causes of Rowstore Fragmentation
		How Fragmentation Occurs in Rowstore Indexes
			Page Split from an UPDATE Statement
			Page Split by an INSERT Statement
		How Fragmentation Occurs in the Columnstore Indexes
	Fragmentation Overhead
		Rowstore Overhead
		Columnstore Overhead
	Analyzing the Amount of Fragmentation
	Analyzing the Fragmentation of a Small Table
	Fragmentation Resolutions
		Drop and Recreate the Index
		Recreating the Index with the DROP_EXISTING Clause
		Execute the ALTER INDEX REBUILD Command
		Execute the ALTER INDEX REORGANIZE Command
		Defragmentation and Partitions
	Significance of the Fill Factor
	Automatic Maintenance
	Summary
Chapter 13: Parameter-Sensitive Queries: Causes and Solutions
	How Does Parameter Sniffing Work?
	Identifying Queries That Are Sensitive to Parameter Values
	Mechanisms for Addressing Plan-Sensitive Queries
		Disable Parameter Sniffing
		Local Variables
		Recompile
		OPTIMIZE FOR Query Hint
		Force Plan
		Parameter Sensitive Plan Optimization
	Summary
Chapter 14: Query Design Analysis
	Query Design Recommendations
	Keep Your Result Sets Small
		Limit the Columns in Your SELECT List
		Filter Your Data Through a WHERE Clause
	Use Indexes Effectively
		Use Effective Search Conditions
			BETWEEN vs. IN/OR
			LIKE Condition
			!< Condition vs. >= Condition
		Avoid Operations on Columns
		Custom Scalar UDF
	Minimize Optimizer Hints
		JOIN Hint
		INDEX Hints
	Using Domain and Referential Integrity
		NOT NULL Constraint
		User-Defined Constraints
		Declarative Referential Integrity
	Summary
Chapter 15: Reduce Query Resource Use
	Avoiding Resource-Intensive Queries
		Use Appropriate Data Types
		Test EXISTS over COUNT(*) to Verify Data Existence
		Favor UNION ALL Over UNION
		Ensure Indexes Are Used for Aggregate and Sort Operations
		Be Cautious with Local Variables in a Batch Query
		Stored Procedure Names Actually Matter
	Reducing Network Overhead Where Possible
		Execute Multiple Queries in Sets
		Use SET NOCOUNT
	Techniques to Reduce Transaction Cost of a Query
		Reduce Logging Overhead
		Reduce Lock Overhead
			Mark the Database As READONLY
			Use Snapshot Isolation
			Prevent SELECT Statements from Requesting a Lock
	Summary
Chapter 16: Blocking and Blocked Processes
	Blocking Fundamentals
		A Short Discussion of Terminology
		Introducing Blocking
	Transactions and ACID Properties
		Atomicity
			SET XACT_ABORT ON
			Explicit Rollback
		Consistency
		Isolation
		Durability
	Lock Types
		Row Locks
		Key Locks
		Page Locks
		Extent Locks
		Heap or B-Tree Locks
		Rowgroup Locks
		Table Locks
		Database Locks
	Lock Operations and Modes
		Lock Escalation
		Lock Modes
			Shared (S) Mode
			Update (U) Mode
			Exclusive (X) Mode
			Intent Shared (IS), Intent Exclusive (IX), and Shared with Intent Exclusive (SIX) Modes
			Schema Modification (Sch-M) and Schema Stability (Sch-S) Modes
			Bulk Update (BU) Mode
			Key-Range Mode
		Lock Compatibility
	Isolation Levels
		Read Uncommitted
		Read Committed
		Repeatable Read
		Serializable
		Snapshot
	Effect of Indexes on Locking
		Effect of a Nonclustered Index
		Effects of a Clustered Index
	Capturing Blocking Information
		Capturing Blocking Information Using T-SQL
		Extended Events and the blocked_process_report Event
	Recommendations to Reduce Blocking
	Summary
Chapter 17: Causes and Solutions For Deadlocks
	Deadlock Fundamentals
		Choosing the Deadlock Victim
	Analyzing the Causes of Deadlocks
		Capturing Deadlock Information
			Trace Flag
			Extended Events
		Analyzing the Deadlock Graph
	Error Handling for Deadlocks
	Mechanisms to Prevent Deadlocks
		Access Resources in the Same Order
		Decrease the Amount of Resources Accessed
			Convert a Nonclustered Index to a Clustered Index
			Use a Covering Index
		Minimize Lock Contention
			Implement Row Versioning
			Decrease the Isolation Level
			Use Locking Hints
			Tune the Queries
	Summary
Chapter 18: Row-by-Row Processing from Cursors and Other Causes
	Cursor Fundamentals
		Cursor Location
			Client-Side Cursors
			Server-Side Cursors
		Cursor Concurrency
			Read-Only
			Optimistic
			Scroll Locks
		Cursor Types
			Forward-Only Cursors
			Static Cursors
			Keyset-Driven Cursors
			Dynamic Cursors
			WHILE Loop
	Cursor Cost Comparison
		Cost Comparison Based on Location
			Client-Side Cursors
			Server-Side Cursors
		Cost Comparison Based on Concurrency
			Read-Only
			Optimistic
			Scroll Locks
		Cost Comparison Based on Cursor Type
			Forward-Only Cursors
			Fast-Forward-Only Cursor
			Static Cursors
			Keyset-Driven Cursors
			Dynamic Cursor
	Default Result Set
		Benefits
		Multiple Active Result Sets
		Drawbacks
	Cursor Overhead
	Cursor Recommendations
	Summary
Chapter 19: Memory-Optimized OLTP Tables and Procedures
	In-Memory OLTP Fundamentals
		System Requirements
		Basic Setup
	Creating Tables
		In-Memory Indexes
			Hash Index
			Nonclustered Indexes
			Columnstore Index
		Statistics Maintenance
	Natively Compiled Stored Procedures
	Recommendations
		Baselines
		Correct Workload
		Memory Optimization Advisor
		Native Compilation Advisor
	Summary
Chapter 20: Graph Databases
	Introduction to Graph Databases
	Querying Graph Data
		Shortest Path
	Performance Considerations of Graph Data
	Summary
Chapter 21: Intelligent Query Processing
	Adaptive Query Processing
		Interleaved Execution
		Query Processing Feedback
			Memory Grants
			Cardinality Estimates
			Degree of Parallelism (DOP) Feedback
			Feedback Persistence
	Approximate Query Processing
		APPROX_COUNT_DISTINCT
		APPROX_PERCENTILE_CONT and APPROX_PERCENTILE_DISC
	Table Variable Deferred Compilation
	Scalar User-Defined Function Inlining
	Summary
Chapter 22: Automated Tuning in Azure and SQL Server
	Automatic Plan Correction
		Tuning Recommendations
		Enabling Automatic Tuning
			Azure Portal
			SQL Server
		Automatic Tuning in Action
	Azure SQL Database Automatic Index Management
	Summary
Chapter 23: A Query Tuning Methodology
	Database Design
		Use Entity-Integrity Constraints
		Maintain Domain and Referential Integrity Constraints
		Adopt Index-Design Best Practices
		Avoid the Use of the “sp_” Prefix for Stored Procedure Names
		Minimize the Use of Triggers
		Put Tables into In-Memory Storage
		Use Columnstore Indexes
		Take Advantage of Graph Storage
	Configuration Settings
		Memory Configuration Options
		Cost Threshold for Parallelism
		Max Degree of Parallelism
		Optimize for Ad Hoc Workloads
		Block Process Threshold
		Database Compression
	Database Administration
		Keep Statistics Up to Date
		Maintain a Minimum Amount of Index Fragmentation
		Avoid Database Functions Such As AUTO_CLOSE or AUTO_SHRINK
	Query Design
		Use the Command SET NOCOUNT ON
		Explicitly Define the Owner of an Object
		Avoid Non-sargable Search Conditions
		Avoid Arithmetic Expressions on Filter Clauses
		Avoid Optimizer Hints
		Stay Away from Nesting Views
		Ensure No Implicit Data Type Conversions
		Minimize Logging Overhead
		Adopt Best Practices for Reusing Execution Plans
			Caching Execution Plans Effectively
			Minimize Recompilation of Execution Plans
		Adopt Best Practices for Database Transactions
		Eliminate or Reduce the Overhead of Database Cursors
		Use Natively Compiled Stored Procedures
		Take Advantage of Columnstore for Analytical Queries
		Enable Query Store
	Summary
Index