Module 1: SQL Server Architecture, Scheduling & Waits
- SQL Server Components and SQL OS
Waits and Queues
Query Lifecycle
Connections -> Sessions -> Requests -> Tasks -> Workers -> Threads
Common Wait Types in SQL Server, identification & diagnosis
Module 2: CPU Performance Monitoring & Tuning
- Troubleshooting CPU Performance
Identifying, troubleshooting & tuning workload patterns that cause excessive CPU
Module 3: SQL Server Tempdb Internals & Tuning
- Tempdb Internals
Identifying, troubleshooting & tuning workload patterns that cause excessive Tempdb usage
Module 4: SQL Server I/O Internals & Tuning
- Core Concepts
IO Setup and Testing
SQL Server IO Patterns
Identifying, troubleshooting & tuning workload patterns that cause excessive IO
Module 5: SQL Server Memory Internals & Troubleshooting
- Windows Memory Internals
SQL Server Memory Internals & Troubleshooting
Identifying, troubleshooting & tuning workload patterns that cause excessive Memory Usage
Module 6: Concurrency and Transactions
- Concurrency and Transactions
Locking Internals
Troubleshooting Blocking Scenarios
Troubleshooting Deadlocks
Module 7: Query Execution and Query Plan Analysis
- Query Execution and Optimizer Internals
Analyzing Query Plans
Module 8: Statistics and Index Internals
- Statistics Internals
Cardinality Estimation Internals
Index Internals
- Understanding common patterns & antipatterns
Troubleshooting Slow Running Queries
Identify Plan Quality Issues
Optimizing T-SQL code/Rewriting
Optimizing Stored Procedures
Real-World Query Tuning Examples
- Indexing Techniques
Indexing Strategies
Real World Index Tuning Examples
Module 11: Plan Caching and Recompilation
- Plan Cache Internals
Troubleshooting Plan Cache Issues
- Extended Events core concepts
Implementing Extended Events
Module 13: Monitoring, Tracing and Baselining
- Tools & Techniques
Monitoring and Tracing
Baselining and Benchmarking
Module 14: SQL Server New Features
- New Tuning Features in Latest Versions of SQL Server