Section 1: SQL Server Architecture, Scheduling, and Waits
This section provides a high level architectural overview of SQL Server and its various components. It goes into detail regarding the SQL Server execution model, waits and queues.
SQL Server Components and SQL OS
Windows Scheduling vs SQL Scheduling
Waits and Queues
Lab: SQL Server Architecture, Scheduling, and Waits
Section 2: SQL Server I/O
This section covers core I/O concepts, Storage Area Networks and performance testing. The section focuses on SQL Server I/O operations and how to test storage performance.
I/O Setup and Testing
Lab: Testing Storage Performance
Section 3: Database Structures
This section dicusses Database Structures, Data File and TempDB Internals. The section focuses on architectural concepts and best practices related to data files for user databases and TempDB.
Database Structure Internals
Data File Internals
Lab: Database Structures
Section 4: SQL Server Memory
This section covers Windows and SQL Server Memory internals. The section focuses on architectural concepts and best practices related to SQL Server Memory Configuration.
SQL Server Memory
Lab: SQL Server Memory
Section 5: Concurrency and Transactions
This section covers Transactions and Locking Internals. The section focuses on architectural concepts and best practices related to Concurrency, Transactions, Isolation Levels and Locking.
Concurrency and Transactions
Lab: Concurrency and Transactions
Section 6: Statistics and Index Internals
This section covers Statistics and Index Internals. The section focuses on architectural concepts and best practices related to Statistics and Indexes.
Statistics Internals and Cardinality Estimation
Lab: Statistics and index Internals
Section 7: Query Execution and Query Plan Analysis
This section covers Query Execution and Query Plan Analysis. The section focuses on architectural concepts of the Optimizer and how to identify and fix query plan issues.
Query execution and optimizer internals
Analyzing query plans
Lab: Query execution and query plan analysis
Section 8: Plan Caching and Recompilation
This section covers Plan Caching and Recompilation. The section focuses on architectural concepts, troubleshooting scenarios and best practices related to Plan Cache.
Plan cache internals
Troubleshooting plan cache issues
Lab: Plan caching and recompilation
Section 9: Extended Events
This section covers Extended Events. The section focuses on architectural concepts, troubleshooting strategy and usage scenarios for Extended Events.
Extended events core concepts
Implementing extended events
Lab: Extended events
Section 10: Monitoring, Tracing, and Baselining
This section examines tools and techniques used to monitor, trace and baseline SQL Server performance data. The section focuses on data collection strategy and techniques to analyze collected data.
Monitoring and tracing
Baselining and benchmarking
Lab: Monitoring, Tracing and Baselining
Section 11: Troubleshooting Common Performance Issues
This section discusses common performance bottlenecks related to CPU, Memory, IO, TempDB and Concurrency. The section focuses on techniques to identify and diagnose bottlenecks to improve overall performance.
Troubleshoot CPU performance
Troubleshoot memory performance
Troubleshoot I/O performance
Troubleshoot Concurrency performance
Troubleshoot TempDB performance
Lab: Troubleshooting common performance issues