Cours M10987-C
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.