MS-10987 - Performance Tuning and Optimizing SQL Databases

This four-day instructor-led course provides students who manage and maintain SQL Server databases with the knowledge and skills to performance tune and optimize their databases.

Instructor did a great job, from experience this subject can be a bit dry to teach but he was able to keep it very engaging and made it much easier to focus. Student
Excellent presentation skills, subject matter knowledge, and command of the environment. Student
Instructor was outstanding. Knowledgeable, presented well, and class timing was perfect. Student

Click here to print this page »

Prerequisites


In addition to their professional experience, students who attend this training should already have the following technical knowledge:
· Basic knowledge of the Microsoft Windows operating system and its core functionality.
· Working knowledge of database administration and maintenance
· Working knowledge of Transact-SQL.

Detailed Class Syllabus


Module 1: SQL Server Architecture, Scheduling, and Waits


SQL Server Components and SQL OS
Windows Scheduling vs SQL Scheduling
Waits and Queues

Module 2: SQL Server I/O


Core Concepts
Storage Solutions
I/O Setup and Testing

Module 3: Database Structures


Database Structure Internals
Data File Internals
TempDB Internals

Module 4: SQL Server Memory


Windows Memory
SQL Server Memory
In-Memory OLTP

Module 5: SQL Server Concurrency


Concurrency and Transactions
Locking Internals

Module 6: Statistics and Index Internals


Statistics Internals and Cardinality Estimation
Index Internals
Columnstore Indexes

Module 7: Query Execution and Query Plan Analysis


Query execution and optimizer internals
Query execution plans
Analyzing query execution plans

Module 8: Plan Caching and Recompilation


Plan cache internals
Troubleshooting plan cache issues
Query store

Module 9: Extended Events


Extended events core concepts
Working with extended events

Module 10: Monitoring, Tracing, and Baselining


Monitoring and tracing
Baselining and benchmarking