Performance Tuning and Optimizing SQL Databases

This course is intended for end users, Database Administrators, analytics, Database Developers, and BI professionals. 

Module 1: SQL Server Architecture

• Lesson 1: SQL Server Components and SQLOS
• Lesson 2: Windows Scheduling vs. SQL Server Scheduling
• Lesson 3: Waits and Queues

Module 2: SQL Server I/O

• Lesson 1: Core Concepts of I/O
• Lesson 2: Storage Solutions
• Lesson 3: I/O Setup and Testing

Module 3: Database Structures

• Lesson 1: Database Structure Internals
• Lesson 2: SQL queries
• Lesson 3: Data File Internals
• Lesson 4: TempDB Internals

Module 4: SQL Server Memory

• Lesson 1: Windows Memory
• Lesson 2: SQL Server Memory
• Lesson 3: In-Memory OLTP

Module 5: SQL Server Concurrency

• Lesson 1: Concurrency and Transactions
• Lesson 2: Locking Internals

Module 6: Statistics and Index Internals

• Lesson 1: Statistics Internals and Cardinality Estimation
• Lesson 2: Index Internals
• Lesson 3: Columnstore Indexes

Module 7: Query Execution and Query Plan Analysis

• Lesson 1: Query Execution and Query Optimizer Internals
• Lesson 2: Query Execution Plans
• Lesson 3: Analyzing Query Execution Plans
• Lesson 4: Adaptive Query Processing, Intelligent Query Processing

Module 8: Plan Caching and Recompilation

• Lesson 1: Plan Cache Internals
• Lesson 2: Troubleshooting with the Plan Cache
• Lesson 3: Query Store

Module 9: Extended Events

• Lesson 1: Extended Events Core Concepts
• Lesson 2: SQL Trace, SQL Server Profiler
• Lesson 3: Working with Extended Events

Module 10: Monitoring, Tracing, and Baselines

• Lesson 1: Monitoring and Tracing
• Lesson 2: Baselining and Benchmarking

• Describe the high level architectural overview of SQL Server and its various components
• Describe the SQL Server execution model, waits and queues
• Describe core I/O concepts, Storage Area Networks and performance testing
• Describe architectural concepts and best practices related to data files for user databases and TempDB, rewrite SQL queries
• Describe architectural concepts and best practices related to Concurrency, Transactions, Isolation Levels and Locking
• Describe architectural concepts of the Optimizer and how to identify and fix query plan issues
• Describe architectural concepts, troubleshooting scenarios and best practices related to Plan Cache
• Describe architectural concepts, troubleshooting strategy and usage scenarios for Extended Events
• Explain data collection strategy and techniques to analyze collected data
• Understand techniques to identify and diagnose bottlenecks to improve overall performance
• Adaptive Query Processing, Intelligent Query Processing
• Create baseline, monitor and trace database server, databases and aplications

• Basic knowledge of T-SQL
• Basic knowledge of SQL Server databases
• Basic knowledge of the Microsoft Windows operating system

Classrooms are equipped with high-performance computers with Internet access and the possibility of wireless connection.

All participants will get internal OKsystem student materials.