Toll Free - 877-442-3915

Account | Self-Paced Login

Account | Self-Paced Login | 877-442-3915

Improve your skills while working at home
Online Training options available for all of our courses
Live-Instructor - Online Group Training - On-Demand


Performance Tuning and Optimizing SQL Databases  Course

Performance Tuning and Optimizing SQL Databases Training Course


Course Description:  The Performance Tuning and Optimizing SQL Databases - 10987 course is a 4-day course that is designed to teach students that manage and maintain SQL Server databases the skills needed to performance tune and optimize their databases.

Topics covered in the course include:

  • High level architectural overview of SQL Server and its various components.
  • SQL Server execution model, waits and queues.
  • Core I/O concepts, Storage Area Networks and performance testing.
  • Architectural concepts and best practices related to data files for user databases and TempDB.
  • Architectural concepts and best practices related to Concurrency, Transactions, Isolation Levels and Locking.
  • Architectural concepts of the Optimizer and how to identify and fix query plan issues.
  • Architectural concepts, troubleshooting scenarios and best practices related to Plan Cache.
  • Architectural concepts, troubleshooting strategy and usage scenarios for Extended Events.
  • Data collection strategy and techniques to analyze collected data.
  • Understanding techniques used to identify and diagnose bottlenecks to improve overall performance.

Target Student:This course is designed for students that administer and maintain SQL Server databases, and who are responsible for the optimal performance of SQL Server instances which they manage.

Students should possess the following experience before attending the course:

  • Working knowledge of database administration and maintenance
  • Working knowledge of Transact-SQL
  • Basic experience with the Microsoft Windows operating system and its core features

Training Dates
(Click on the course name below to view course details and full list of class dates)

Performance Tuning and Optimizing SQL Databases
Locations: Available Nationwide and Online
Class Date(s): 6/8/2020 - 6/11/2020
Course Fee : $2395


Performance Tuning and Optimizing SQL Databases
Locations: Available Nationwide and Online
Class Date(s): 7/13/2020 - 7/16/2020
Course Fee : $2395


Performance Tuning and Optimizing SQL Databases
Locations: Available Nationwide and Online
Class Date(s): 8/17/2020 - 8/20/2020
Course Fee : $2395


Course Syllabus

Section 1: SQL Server Architecture, Scheduling, and Waits
This section provides a high level architectural overview of SQL Server and its various components. It goes into detail regarding the SQL Server execution model, waits and queues.

Topics
SQL Server Components and SQL OS
Windows Scheduling vs SQL Scheduling
Waits and Queues

Lab: SQL Server Architecture, Scheduling, and Waits

Section 2: SQL Server I/O
This section covers core I/O concepts, Storage Area Networks and performance testing. The section focuses on SQL Server I/O operations and how to test storage performance.

Topics
Core Concepts
Storage Solutions
I/O Setup and Testing

Lab: Testing Storage Performance

Section 3: Database Structures
This section dicusses Database Structures, Data File and TempDB Internals. The section focuses on architectural concepts and best practices related to data files for user databases and TempDB.

Topics
Database Structure Internals
Data File Internals
TempDB Internals

Lab: Database Structures

Section 4: SQL Server Memory
This section covers Windows and SQL Server Memory internals. The section focuses on architectural concepts and best practices related to SQL Server Memory Configuration.

Topics
Windows Memory
SQL Server Memory
In-Memory OLTP

Lab: SQL Server Memory

Section 5: Concurrency and Transactions
This section covers Transactions and Locking Internals. The section focuses on architectural concepts and best practices related to Concurrency, Transactions, Isolation Levels and Locking.

Topics
Concurrency and Transactions
Locking Internals

Lab: Concurrency and Transactions

Section 6: Statistics and Index Internals
This section covers Statistics and Index Internals. The section focuses on architectural concepts and best practices related to Statistics and Indexes.

Topics
Statistics Internals and Cardinality Estimation
Index Internals
Columnstore Indexes

Lab: Statistics and index Internals

Section 7: Query Execution and Query Plan Analysis
This section covers Query Execution and Query Plan Analysis. The section focuses on architectural concepts of the Optimizer and how to identify and fix query plan issues.

Topics
Query execution and optimizer internals
Analyzing query plans

Lab: Query execution and query plan analysis

Section 8: Plan Caching and Recompilation
This section covers Plan Caching and Recompilation. The section focuses on architectural concepts, troubleshooting scenarios and best practices related to Plan Cache.

Topics
Plan cache internals
Troubleshooting plan cache issues
Query store

Lab: Plan caching and recompilation

Section 9: Extended Events
This section covers Extended Events. The section focuses on architectural concepts, troubleshooting strategy and usage scenarios for Extended Events.

Topics
Extended events core concepts
Implementing extended events

Lab: Extended events

Section 10: Monitoring, Tracing, and Baselining
This section examines tools and techniques used to monitor, trace and baseline SQL Server performance data. The section focuses on data collection strategy and techniques to analyze collected data.

Topics
Monitoring and tracing
Baselining and benchmarking

Lab: Monitoring, Tracing and Baselining

Section 11: Troubleshooting Common Performance Issues
This section discusses common performance bottlenecks related to CPU, Memory, IO, TempDB and Concurrency. The section focuses on techniques to identify and diagnose bottlenecks to improve overall performance.

Topics
Troubleshoot CPU performance
Troubleshoot memory performance
Troubleshoot I/O performance
Troubleshoot Concurrency performance
Troubleshoot TempDB performance

Lab: Troubleshooting common performance issues

SQL Server On-Site Group Training
Arrange a private SQL Server training class for your team with one of our Certified Instructors. Private instruction for your team members at a lower per-student price than attending the public courses. Learn More or complete the quote request form below.



SQL Server On-Demand Training

"The Instructor made class truly interesting and put everything into real world terms."
- Meredith T(Jersey City, NJ)


Business Computer Skills Microsoft Partner

Business Computer Skills BBB Profile