Cours M10987-C
Performance Tuning and Optimizing SQL Databases
Performance Tuning and Optimizing SQL Databases
Doelstelling
- 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.
- 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.
Inhoud
SQL Server Architecture, Scheduling, and Waits
- SQL Server Components and SQL OS
- Windows Scheduling vs SQL Scheduling
- Waits and Queues
SQL Server I/O
- Core Concepts
- Storage Solutions
- I/O Setup and Testing
Database Structures
- Database Structure Internals
- Data File Internals
- TempDB Internals
SQL Server Memory
- Windows Memory
- SQL Server Memory
- In-Memory OLTP
SQL Server Concurrency
- Concurrency and Transactions
- Locking Internals
Statistics and Index Internals
- Statistics Internals and Cardinality Estimation
- Index Internals
- Columnstore Indexes
Query Execution and Query Plan Analysis
- Query execution and optimizer internals
- Query execution plans
- Analyzing query execution plans
- Adaptive query processing
Plan Caching and Recompilation
- Plan cache internals
- Troubleshooting plan cache issues
- Automatic tuning
- Query store
Extended Events
- Extended events core concepts
- Working with extended events
Monitoring, Tracing, and Baselining
- Monitoring and tracing
- Baselining and benchmarking
Details
Duur
4 dagen
Deelnemers
8
Bestemd voor
IT-specialist
Voorkennis
- Basic knowledge of the Microsoft Windows operating system and its core functionality.
- Working knowledge of database administration and maintenance
- Working knowledge of Transact-SQL.