Learn T-SQL Querying: A guide to developing efficient and elegant T-SQL code

Cover
Title Page
Copyright and Credits
Foreword
Contributors
Table of Contents
Preface
Part 1: Query Processing Fundamentals
Chapter 1: Understanding Query Processing
	Technical requirements
	Logical statement processing flow
	Query compilation essentials
	Query optimization essentials
	Query execution essentials
	Plan caching and reuse
		Stored procedures
		Ad hoc plan caching
		Parameterization
		The sp_executesql procedure
		Prepared statements
	How query processing impacts plan reuse
	The importance of parameters
		Security
		Performance
		Parameter sniffing
		To cache or not to cache
	Summary
Chapter 2: Mechanics of the Query Optimizer
	Technical requirements
	Introducing the Cardinality Estimator
	Understanding the query optimization workflow
		The Trivial Plan stage
		The Exploration stage
		The Transaction Processing phase
		The Quick Plan phase
		The Full Optimization phase
	Knobs for query optimization
	Summary
Part 2: Dos and Don’ts of T-SQL
Chapter 3: Exploring Query Execution Plans
	Technical requirements
	What is a query plan?
	Accessing a query plan
	Navigating a query plan
	Query plan operators of interest
		Blocking versus non-blocking operators
		Data access operators
		Joins
		Spools
		Sort and aggregation operators
	Query plan properties of interest
		Plan-level properties
		Operator-level properties
	Summary
Chapter 4: Indexing for T-SQL Performance
	Technical requirements
	Understanding predicate SARGability
	Data access using indexes
		Structure of a rowstore index
		Data access using rowstore indexes
		Inserting and updating data in a rowstore index
	Indexing strategy using rowstore indexes
		Best practices for clustered indexes
		Best practices for non-clustered indexes
	Index maintenance
	Summary
Chapter 5: Writing Elegant T-SQL Queries
	Technical requirements
	Best practices for T-SQL querying
		Referencing objects
		Joining tables
		Using NOLOCK
		Using cursors
	The perils of SELECT *
	Functions in our predicate
	Deconstructing table-valued functions
	Complex expressions
	Optimizing OR logic
	NULL means unknown
	Fuzzy string matching
	Inequality logic
	EXECUTE versus sp_executesql
	Composable logic
	Summary
Chapter 6: Discovering T-SQL Anti- Patterns in Depth
	Technical requirements
	Implicit conversions
	Avoiding unnecessary sort operations
		UNION ALL versus UNION
		SELECT DISTINCT
	Avoiding UDF pitfalls
	Avoiding unnecessary overhead with stored procedures
	Pitfalls of complex views
	Pitfalls of correlated sub-queries
	Properly storing intermediate results
		Using table variables and temporary tables
		Using Common Table Expressions (CTEs)
	Summary
Part 3: Assembling Our Query Troubleshooting Toolbox
Chapter 7: Building Diagnostic Queries Using DMVs and DMFs
	Technical requirements
	Introducing DMVs
	Exploring query execution DMVs
		sys.dm_exec_sessions
		sys.dm_exec_requests
		sys.dm_exec_sql_text
		sys.dm_os_waiting_tasks
	Exploring query plan cache DMVs
		sys.dm_exec_query_stats
		sys.dm_exec_procedure_stats
		sys.dm_exec_query_plan
		sys.dm_exec_cached_plans
	Troubleshooting common scenarios with DMV queries
	Investigating blocking
	Cached query plan issues
		Single-use plans (query fingerprints)
		Finding resource-intensive queries
		Queries with excessive memory grants
	Mining XML query plans
		Plans with missing indexes
		Plans with warnings
		Plans with implicit conversions
		Plans with lookups
	Summary
Chapter 8: Building XEvent Profiler Traces
	Technical requirements
	Introducing XEvents
	Getting up and running with XEvent Profiler
	Remote collection with SQL LogScout
	Analyzing traces with RML Utilities
	Summary
Chapter 9: Comparative Analysis of Query Plans
	Technical requirements
	Query plan analyzer
	Summary
Chapter 10: Tracking Performance History with Query Store
	Technical requirements
	Introducing the Query Store
		Inner workings of the Query Store
		Configuring the Query Store
	Tracking expensive queries
	Fixing regressed queries
	Features that rely on the Query Store
		Query Store for readable secondary replicas
		Query Store hinting
		Parameter Sensitive Plan Optimization
		Automatic Plan Correction
		Degree of parallelism feedback
		Optimized plan forcing
	Summary
Chapter 11: Troubleshooting Live Queries
	Technical requirements
	Using Live Query Statistics
	Understanding the need for lightweight profiling
		Diagnostics available with Lightweight Profiling
	Activity Monitor gets new life
	Summary
Chapter 12: Managing Optimizer Changes
	Technical requirements
	Understanding where QTA and CE feedback are needed
	Understanding QTA fundamentals
	Exploring the QTA workflow
	Summary
Index
Other Books You May Enjoy