SQL Server Execution Plans. For SQL Server 2008 through to 2017 and Azure SQL Database

Chapter 1: Introducing the Execution Plan
	What Happens When a Query is Submitted?
		Query compilation phase
			Query parsing
			Query binding
			Query optimization
		Query execution phase
	Working with the Optimizer
		The importance of statistics
		The plan cache and plan reuse
			Plan aging
			Manually clearing the plan cache
			Avoiding cache churn: query parameterization
			Plan recompilation
	Getting Started with Execution Plans
		Permissions required to view execution plans
		Execution plan formats
			XML plans
			Text plans
			Graphical plans
		Retrieving cached plans
		Plans for ad hoc queries: estimated and actual plans
		Will the estimated and actual plans ever be different?
		Capturing graphical plans in SSMS
		Capturing our first plan
		The components of a graphical execution plan
			Operators
			Data flow arrows
			Estimated operator costs
			Estimated total query cost relative to batch
			Operator properties
			Tooltips
		Saving execution plans
Chapter 2: Getting Started Reading Plans
	The Language of Execution Plans
		Common operators
		Reading a plan: right to left, or left to right?
		Streaming versus blocking operators
	What to Look for in an Execution Plan
		First operator
		Warnings
		Estimated versus actual number of rows
		Operator cost
		Data flow
		Extra operators
		Read operators
	The Information Behind the First Operator
		Optimization level
		Parameter List
		QueryHash and QueryPlanHash
		SET options
	Other Useful Tools and Techniques when Reading Plans
		I/O and timing statistics using SET commands
		Include Client Statistics
		SQL Trace and Profiler
		Extended Events
Chapter 3: Data Reading Operators
	Reading an Index
		Index Scans
			Clustered Index Scan
			Index Scan
			Are scans "bad?"
		Index seeks
			Clustered Index Seek
			Index Seek (nonclustered)
			Key lookups
	Reading a Heap
		Table Scan
		RID Lookup
Chapter 4: Joining Data
	Logical Join Operations
	Fulfilling JOIN Commands
		Nested Loops operator
			Estimated and Actual Number of Rows properties
			Outer References property
			Rebind and Rewind properties
		Hash Match (join)
			How Hash Match joins work
	Hashing and Hash Tables
		Performance considerations for Hash Match joins
		Compute Scalar
		Merge Join
			How Merge Joins work
			Performance considerations for Merge Joins
		Adaptive Join
	Other Uses of Join Operators
	Concatenating Data
Chapter 5: Sorting and Aggregating Data
	Queries with ORDER BY
		Sort operations
			Sort operations and the Ordered property of Index Scans
			Dealing with expensive Sorts
			Top N Sort
			Distinct Sort
			Sort warnings
	Aggregating Data
		Stream Aggregate
		Hash Match (Aggregate)
		Filtering aggregations using HAVING
		Plans with aggregations and spools
			Table Spool
			Index Spool
	Working with Window Functions
Chapter 6: Execution Plans for Data Modifications
	Plans for INSERTs
		INSERT operator
		Constant Scan operator
		Clustered Index Insert operator
		Assert operator
	Plans for UPDATEs
		Table Spool (Eager Spool) operator
		Clustered Index Update operator
	Plans for DELETEs
		A simple DELETE plan
		A per-index DELETE plan
	Plans for MERGE queries
Chapter 7: Execution Plans for Common T-SQL Statements
	Stored Procedures
	Subqueries
	Derived Tables Using APPLY
	Common Table Expressions
	Views
		Standard views
		Indexed views
	Functions
		Scalar functions
		Table valued functions
Chapter 8: Examining Index Usage
	Standard Indexes
	How the optimizer selects which indexes to use
		Estimated costs and statistics
		Selectivity and cardinality estimations
		Indexes and selectivity
			Statistics header
			Density graph
			The histogram
		Using covering indexes
	What can go wrong?
		Problems with statistics
		Problems with parameter sniffing
			Stored procedures and parameter sniffing
			What to do if parameter sniffing causes performance problems
	Columnstore Indexes
		Using a columnstore index for an aggregation query
		Aggregate pushdown
		No seek operation on columnstore index
		Predicate pushdown in a columnstore index
		Batch mode versus row mode
	Memory-optimized Indexes
		Using memory-optimized tables and indexes
		No option to seek a hash index for a range of values
		Plans with natively-compiled stored procedures
Chapter 9: Exploring Plan Reuse
	Querying the Plan Cache
	Plan Reuse and Ad Hoc Queries
		The cost of excessive plan compilation
		Simple parameterization for "trivial" ad hoc queries
			Simple parameterization in action
			"Unsafe" simple parameterization
	Programming for Plan Reuse: Parameterizing Queries
		Prepared statements
		Stored procedures
		What can go wrong with plan reuse for parameterized queries?
	Fixing Problems with Plan Reuse if You Can't Rewrite the Query
		Optimize for ad hoc workloads
		Forced parameterization
		Plan guides
			Template plan guides
			SQL plan guides
			Object plan guides
			Viewing, validating, disabling, and removing plan guides
		Plan forcing
			Using plan guides to do plan forcing
			Using Query Store to do plan forcing
Chapter 10: Controlling Execution Plans with Hints
	The Dangers of Using Hints
	Query Hints
		HASH | ORDER GROUP
		MERGE | HASH | CONCAT UNION
		LOOP | MERGE | HASH JOIN
		FAST n
		FORCE ORDER
		MAXDOP
		OPTIMIZE FOR
		RECOMPILE
		EXPAND VIEWS
		IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX
	Join Hints
	Table Hints
		NOEXPAND
		INDEX()
		FORCESEEK/FORCESCAN
Chapter 11: Parallelism in Execution Plans
	Controlling Parallel Query Execution
		Max degree of parallelism
		Cost threshold for parallelism
		Blockers of parallel query execution
	Parallel Query Execution
		Examining a parallel execution plan
		Are parallel plans good or bad?
Chapter 12: Batch Mode Processing
	Batch Mode Processing Defined
	Plan for Queries that Execute in Batch Mode
		Batch mode prior to SQL Server 2016
		Mixing columnstore and rowstore indexes
		Batch mode adaptive memory grant
	Loss of Batch Mode Processing
Chapter 13: The XML of Execution Plans
	A Brief Tour of the XML Behind a Plan
		The XML for an estimated plan
		The XML for an actual plan
	Safely Saving and Sharing Execution Plans
	When You'll Really Need the XML Plan
		Use the XML plan for plan forcing
		First operator properties when capturing plans using Extended Events
		Pre-SQL Server 2012: full "missing index" details
	Querying the Plan Cache
		Why query the XML of plans?
		Query the plan XML for specific operators
		Querying the XML for missing index information
Chapter 14: Plans for Special Data Types and Cursors
	XML
		Plans for queries that convert relational data to XML (FOR XML)
			Plans for basic FOR XML queries
			Returning XML as XML data type
			Plans for Explicit mode FOR XML queries
		Plans for queries that convert XML to relational data (OPENXML)
		Plans for querying XML using XQuery
			Plans for queries that use the .exist method
			Plans for queries that use the .query method
		When to use XQuery
	JavaScript Object Notation
	Hierarchical Data
	Spatial Data
	Cursors
		Static cursor
		Keyset cursor
		Dynamic cursor
Chapter 15: Automating Plan Capture
	Why Automate Plan Capture?
	Tools for Automating Plan Capture
		Automating plan capture using Extended Events
			Create an event session using the SSMS GUI
			Create an event session in T-SQL
			Viewing the event data
			Ensuring "lightweight" event sessions when capturing the plan
		Automating plan capture using SQL Trace
			Trace events for execution plans
			Creating a Showplan XML trace using Profiler
			Creating a server-side trace
Chapter 16: The Query Store
	Behavior of the Query Store
	Query Store Options
	Retrieving Plans from the Query Store
		SSMS reports
			Overview of Query Store reports
			The Top Resource Consuming Queries report
		Retrieve Query Store plans using T-SQL
	Control Plans Using Plan Forcing
		How to force a plan
			Automated plan forcing
	Remove Plans from the Query Store
Chapter 17: SSMS Tools for Exploring Execution Plans
	The Query
	The SQL Server Management Studio 17 Tools
		Analyze Actual Execution Plan
		Compare Showplan
		Find Node
		Live execution plans
			Live per-operator statistics using sys.dm_exec_query_profiles
			Using the query_thread_profile extended event
			Live execution plans in SSMS
			Viewing the live execution plan in Activity Monitor
	Other Execution Plan Tools
		Plan Explorer
		Supratimas
		SSMS Tools Pack – Execution Plan Analyzer
		SQL Server performance monitoring tools
_GoBack