Performance Tuning SQL 2019

 

Performance Tuning SQL 2019

Duration

5 Days

Overview

Course provides students who manage and maintain SQL Server databases with the knowledge and skills to performance tune and optimize their databases.

What You’r Learn:

  • 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.

Course Outline

Module 1: Core Concepts

  • Query Lifecycle
  • Connections -> Sessions -> Requests -> Tasks -> Workers -> Threads
  • Common Wait Types in SQL Server, identification & diagnosis

Module 2: The Foursome – CPU, Memory, IO & Tempdb

  • Identifying, troubleshooting & tuning workload patterns that cause excessive CPU
  • Identifying, troubleshooting & tuning workload patterns that cause excessive Memory Usage
  • Identifying, troubleshooting & tuning workload patterns that cause excessive IO
  • Identifying, troubleshooting & tuning workload patterns that cause excessive Tempdb usage

Module 3: Query Execution & Query Plan Analysis

  • Statistics & Cardinality Estimation
  • Reading & Analysing Execution Plans
  • Important Iterators

Module 4: Index Tuning

  • Demystifying Common Myths with Indexes
  • Indexing Strategies
  • Index Fragmentation
  • Real-World Index Tuning Examples

Module 5: Query Tuning

  • Rewriting Transact-SQL
  • Dealing with Implicit Conversions
  • Sargability
  • Solving Parameter Sniffing Issues
  • Parametrization Best Practices

Module 6: Dealing with Deadlocks

  • Common Deadlock Types
  • Deadlock Best Practices
  • Practical Deadlock Troubleshooting & Diagnosis