Performance Tuning and Optimizing SQL Databases (10987) Classroom Live Eagan, MN October 23, 2018

Course Overview

• Module 1: SQL Server Architecture, Scheduling, and Waits
• This module covers high level architectural overview of SQL Server and its various components. It dives deep into SQL Server execution model, waits and queues.
  • Lessons
    • SQL Server Components and SQL OS
    • Windows Scheduling vs SQL Scheduling
    • Waits and Queues
  • Lab : SQL Server Architecture, Scheduling, and Waits
  • After completing this module, you will be able to:
    • Describe the SQL Server components and SQL OS
    • Describe the differences between Windows Scheduling and SQL scheduling
    • Describe waits and queues
• Module 2: SQL Server I/O
• This module covers core I/O concepts, Storage Area Networks and performance testing. It focuses on SQL Server I/O operations and how to test storage performance.
  • Lessons
    • Core Concepts
    • Storage Solutions
    • I/O Setup and Testing
  • Lab : Testing Storage Performance
  • After completing this module, you will be able to:
    • Describe the core concepts of SQL I/O
    • Describe storage solutions
    • Setup and test I/O
• Module 3: Database Structures
• This module covers Database Structures, Data File and TempDB Internals. It focuses on architectural concepts and best practices related to data files for user databases and TempDB.
  • Lessons
    • Database Structure Internals
    • Data File Internals
    • TempDB Internals
  • Lab : Database Structures
  • After completing this module, you will be able to:
    • Describe the internal setup of database structures
    • Describe the internal setup of data files.
    • Describe the internal setup of TempDB
• Module 4: SQL Server Memory
• This module covers Windows and SQL Server Memory internals. It focuses on architectural concepts and best practices related to SQL Server Memory Configuration.
  • Lessons
    • Windows Memory
    • SQL Server Memory
    • In-Memory OLTP
  • Lab : SQL Server Memory
  • After completing this module, you will be able to:
    • Describe the components of Windows memory
    • Describe the components of SQL Server memory
    • Describe In-Memory OLTP
• Module 5: Concurrency and Transactions
• This module covers Transactions and Locking Internals. It focuses on architectural concepts and best practices related to Concurrency, Transactions, Isolation Levels and Locking.
  • Lessons
    • Concurrency and Transactions
    • Locking Internals
  • Lab : Concurrency and Transactions
  • After completing this module, you will be able to:
    • Explain concurrency and transactions
    • Describe locking
• Module 6: Statistics and Index Internals
• This module covers Statistics and Index Internals. It focuses on architectural concepts and best practices related to Statistics and Indexes.
  • Lessons
    • Statistics Internals and Cardinality Estimation
    • Index Internals
    • Columnstore Indexes
  • Lab : Statistics and index Internals
  • After completing this module, you will be able to:
    • Describe statistics internals
    • Explain cardinality estimation
    • Describe why you would use Columnstore indexes and be able to implement one
• Module 7: Query Execution and Query Plan Analysis
• This module covers Query Execution and Query Plan Analysis. It focuses on architectural concepts of the Optimizer and how to identify and fix query plan issues.
  • Lessons
    • Query execution and optimizer internals
    • Analyzing query plans
  • Lab : Query execution and query plan analysis
  • After completing this module, you will be able to:
    • Describe query execution and optimizer
    • Analyze query plans and resolve common issues
• Module 8: Plan Caching and Recompilation
• This module covers Plan Caching and Recompilation. It focuses on architectural concepts, troubleshooting scenarios and best practices related to Plan Cache.
  • Lessons
    • Plan cache internals
    • Troubleshooting plan cache issues
    • Query store
  • Lab : Plan caching and recompilation
  • After completing this module, you will be able to:
    • Describe plan cache
    • Troubleshoot plan cache issues
    • Describe query store and why you would use it
• Module 9: Extended Events
• This module covers Extended Events. It focuses on architectural concepts, troubleshooting strategy and usage scenarios for Extended Events.
  • Lessons
    • Extended events core concepts
    • Implementing extended events
  • Lab : Extended events
  • After completing this module, you will be able to:
    • Describe the core concepts of extended events
    • Implement extended events
• Module 10: Monitoring, Tracing, and Baselining
• This module covers tools and techniques to monitor, trace and baseline SQL Server performance data. It focuses on data collection strategy and techniques to analyze collected data.
  • Lessons
    • Monitoring and tracing
    • Baselining and benchmarking
  • Lab : Monitoring, Tracing and Baselining
  • After completing this module, you will be able to:
    • Describe various options for monitoring and tracing
    • Describe various options for benchmarking and baselining

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.