Duration:

4 Days

Audience:

Employees of federal, state and local governments; and businesses working with the government.

Course Description:

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.

Learning Objectives:

After completing this course, students will be able to:

  • 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

1 – SQL SERVER ARCHITECTURE, SCHEDULING, & WAITS
  • SQL Server Components and SQL OS
  • Windows Scheduling vs SQL Scheduling
  • Waits and Queues
  • Lab: SQL Server Architecture, Scheduling, and Waits
2 – SQL SERVER I/O
  • Core Concepts
  • Storage Solutions
  • I/O Setup and Testing
  • Lab: Testing Storage Performance
3 – DATABASE STRUCTURES
  • Database Structure Internals
  • Data File Internals
  • TempDB Internals
  • Lab: Database Structures
4 – SQL SERVER MEMORY
  • Windows Memory
  • SQL Server Memory
  • In-Memory OLTP
  • Lab: SQL Server Memory
5 – CONCURRENCY & TRANSACTIONS
  • Concurrency and Transactions
  • Locking Internals
  • Lab: Concurrency and Transactions
6 – STATISTICS & INDEX INTERNALS
  • Statistics Internals and Cardinality Estimation
  • Index Internals
  • Column Store Indexes
  • Lab: Statistics and index Internals
7 – QUERY EXECUTION & QUERY PLAN ANALYSIS
  • Query execution and optimizer internals
  • Analyzing query plans
  • Lab: Query execution and query plan analysis
8 – PLAN CACHING & RECOMPILATION
  • Plan cache internals
  • Troubleshooting plan cache issues
  • Query store
  • Lab: Plan caching and recompilation
9 – EXTENDED EVENTS
  • Extended events core concepts
  • Implementing extended events
  • Lab: Extended events
10 – MONITORING, TRACING, & BASELINING
  • Monitoring and tracing
  • Baselining and benchmarking
  • Lab: Monitoring, Tracing and Baselining
11 – TROUBLESHOOTING COMMON PERFORMANCE ISSUES
  • Troubleshoot CPU performance
  • Troubleshoot memory performance
  • Troubleshoot I/O performance
  • Troubleshoot Concurrency performance
  • Troubleshoot TempDB performance
  • Lab: Troubleshooting common performance issues