Performance Tuning and Optimizing SQL Databases

Session Detail:    Classroom 4 Day

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.

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.

Who Can Benefit

The primary audience for this course is individuals who administer and maintain SQL Server databases and are responsible for the optimal performance of SQL Server instances that they manage. These individuals also write queries against data and need to ensure optimal execution performance of the workloads.
The secondary audiences for this course are individuals who develop applications that deliver content from SQL Server databases.

Session Outline

Class Outline

Module 1: SQL Server Architecture, Scheduling, and Waits

  • Lesson 1: SQL Server Components and SQL OS
  • Lesson 2: Windows Scheduling vs SQL Scheduling
  • Lesson 3: Waits and Queues

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

  • Lesson 1: Core Concepts
  • Lesson 2: Storage Solutions
  • Lesson 3: I/O Setup and Testing

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

  • Lesson 1: Database Structure Internals
  • Lesson 2: Data File Internals
  • Lesson 3: TempDB Internals

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

  • Lesson 1: Windows Memory
  • Lesson 2: SQL Server Memory
  • Lesson 3: In-Memory OLTP

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: SQL Server Concurrency

  • Lesson 1: Concurrency and Transactions
  • Lesson 2: Locking Internals

After completing this module, you will be able to:

  • Explain concurrency and transactions
  • Describe locking

Module 6: Statistics and Index Internals

  • Lesson 1: Statistics Internals and Cardinality Estimation
  • Lesson 2: Index Internals
  • Lesson 3: Columnstore Indexes

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

  • Lesson 1: Query execution and optimizer internals
  • Lesson 2: Query execution plans
  • Lesson 3: Analyzing query execution plans
  • Lesson 4: Adaptive query processing

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

  • Lesson 1: Plan cache internals
  • Lesson 2: Troubleshooting plan cache issues
  • Lesson 3: Automatic tuning
  • Lesson 4: Query store

After completing this module, you will be able to:

  • Describe plan cache
  • Troubleshoot plan cache issues
  • Describe the query store and why you would use it

Module 9: Extended Events

  • Lesson 1: Extended events core concepts
  • Lesson 2: Working with 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

  • Lesson 1: Monitoring and tracing
  • Lesson 2: Baselining and benchmarking

After completing this module, you will be able to:

  • Describe various options for monitoring and tracing
  • Describe various options for benchmarking and baselining

Need to train your team?

All of our sessions can be customized to meet your team’s specific need. Build the perfect program by picking and choosing topics from any of the courses in our catalog. A personalized private session gives you the ultimate flexibility and helps maximize your team’s valuable time!

Requesting Team Training
DateTimeTypePriceAdd To Cart

Performance Tuning and Optimizing SQL Databases

4 Day
Classroom

$2,595.00

Chat with a Coach

Chat with a Coach

Have a more immediate need? Why spend the next hour searching online for answers when you can spend just 15 minutes with one of our experts and get accurate and personalized answers to all of your questions. 

$9.99 | 15 Minutes

Book Now