SQL Server Advanced Troubleshooting and Performance Tuning

1. SQL Server Setup and Configuration
	Hardware and Operating System Considerations
		CPU
		Memory
		Disk Subsystem
		Network
		Operating Systems and Applications
		Virtualization and Clouds
	Configuring Your SQL Server
		SQL Server Version and Patching Level
		Instant File Initialization
		Tempdb Configuration
		Trace Flags
		Server Options
	Configuring Your Databases
		Database Settings
		Transaction Log Settings
		Data Files and Filegroups
	Analyzing SQL Server Error Log
	Consolidating Instances and Databases
	Observer Effect
	Summary
	Troubleshooting Checklist
2. SQL Server Execution Model and Wait Statistics
	SQL Server: High-Level Architecture
	SQLOS and the Execution Model
	Wait Statistics
	Execution Model–Related Data Management Views
		sys.dm_os_wait_stats
		sys.dm_exec_session_wait_stats
		sys.dm_os_waiting_tasks
		sys.dm_exec_requests
		sys.dm_os_schedulers
	Resource Governor Overview
	Summary
		Troubleshooting Checklist
3. Troubleshooting Disk Subsystem Issues
	Anatomy of the SQL Server I/O Subsystem
		Scheduling and I/O
		Data Reads
		Data Writes
	The Storage Subsystem: A Holistic View
		sys.dm_io_virtual_file_stats view
		Performance Counters and OS Metrics
		Virtualization, HBA, and Storage Layers
	Checkpoint Tuning
	I/O Waits
		ASYNC_IO_COMPLETION waits
		IO_COMPLETION waits
		WRITELOG waits
		WRITE_COMPLETION waits
		PAGEIOLATCH waits
	Summary
		Troubleshooting Checklist
4. Detecting Inefficient Queries
	The Impact of Inefficient Queries
	Plan-Cache-Based Execution Statistics
	SQL Traces and Extended Events
	Query Store
		Query Store SSMS Reports
		Working with Query Store DMVs
	Third-Party Tools
	Summary
		Troubleshooting Checklist
5. Intro to Query Tuning
	Data Storage and Access Patterns
		Row-Based Storage Tables
		B-Tree Indexes
		Composite Indexes
		Nonclustered Indexes
	Index Fragmentation
	Statistics and Cardinality Estimation
		Statistics Maintenance
		Cardinality Estimation Models
	Analyzing Your Execution Plan
		Row Mode and Batch Mode Execution
		Live Query Statistics and Execution Statistics Profiling
	Common Issues and Inefficiencies
		Inefficient Code
		Inefficient Index Seek
		Incorrect Join Type
		Excessive Key Lookups
	Indexing the Data
	Summary
		Troubleshooting Checklist
6. High CPU Load
	Non-Optimized Queries and T-SQL Code
		Inefficient T-SQL Code
		Scripts for Troubleshooting High CPU Load
		Non-Optimized Query Patterns to Watch For
	Query Compilation and Plan Caching
		Parameter-Sensitive Plans
		Parameter-Value Independence
	Compilation and Parameterization
		Auto-Parameterization
		Simple parameterization
		Forced Parameterization
	Parallelism
	Summary
		Troubleshooting Checklist
7. Troubleshooting Memory Issues
	SQL Server Memory Usage and Configuration
		Configuring SQL Server Memory
		How Much Memory Is Enough?
	Memory Allocations
		Memory Clerks
		DBCC MEMORYSTATUS command
	Query Execution and Memory Grants
		Optimizing Memory Intensive Queries
		Memory Grant Feedback
		Controlling Memory Grant Size
	In-Memory OLTP Memory Usage and Troubleshooting
	Summary
		Troubleshooting Checklist
8. Troubleshooting TempDB Usage and Performance
	Temporary Objects: Usage and Best Practices
		Temporary Tables and Table Variables
		Temporary Object Caching
		Table-Valued Parameters
		Regular Tables in TempDb and Transaction Logging
	Internal TempDB Consumers
		Version Store
		Spills
	Common TempDB Issues
		System Pages Contention
		Running out of Space
	TempDB Configuration
	Summary
		Troubleshooting Checklist
9. Latches
	Introduction to Latches
	Page Latches
		Addressing Hotspots: OPTIMIZE_FOR_SEQUENTIAL_KEY Index Option
		Addressing Hotspots: Hash Partitioning
		Addressing Hotspots: In-Memory OLTP
	Other Latch Types
	Summary
		Troubleshooting Checklist
10. Transaction Log
	Transaction Log Internals
		Data Modifications and Transaction Logging
		Explicit and Autocommitted Transactions and Log Overhead
		Delayed Durability
		In-Memory OLTP Transaction Logging
		VIRTUAL LOG FILES
	Transaction Log Configuration
	Log Truncation Issues
		LOG_BACKUP Log Reuse Wait
		ACTIVE_TRANSACTION Log Reuse Wait
		AVAILABILITY_REPLICA Log Reuse Wait
		DATABASE_MIRRORING Log Reuse Wait
		REPLICATION Log Reuse Wait
		ACTIVE_BACKUP_OR_RESTORE Log Reuse Wait
		Other Mitigation Strategies
	Transaction Log Throughout
	Summary
		Troubleshooting Checklist
11. AlwaysOn Availability Groups
	AlwaysOn Availability Groups Overview
	Availability Group Queues
	Synchronous Replication and the Danger of the HADR_SYNC_COMMIT Wait
		Availability Group Extended Events
	Asynchronous Replication and Readable Secondaries
		The Impact of the Readable Secondaries
	Parallel Redo
	Troubleshooting Failover Events
		Availability Groups and Windows Server Failover Cluster
		Troubleshooting Failovers
		When Failover Does Not Occur
	Summary
		Troubleshooting Checklist