Performance Tuning and Optimizing SQL Databases (3 days)

Course Overview

This instructor-led course is intended for students who manage and maintain Microsoft® SQL Server® databases with the knowledge and skills needed to performance tune and optimise their databases.

Target Audience

This course is intended for database professionals who needs to fulfil a database role with the view of optimising performance of the databases or whose role has expanded to include database technologies.

Course Prerequisites

This course requires that you meet the following prerequisites:

  • Working knowledge of Transact-SQL (ability to write Transact-SQL queries)
  • Working knowledge of database administration and maintenance.
  • Core Windows Server skills.
  • Writing Queries Using Microsoft® SQL Server® 2012/2014 Transact-SQL.

Course Summary

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
  • Describe architectural concepts and best practices related to Concurrency, Transactions, Isolation Levels and
  • Describe architectural concepts of the Optimizer and how to identify and fix query plan
  • Describe architectural concepts, troubleshooting scenarios and best practices related to Plan
  • Describe architectural concepts, troubleshooting strategy and usage scenarios for Extended
  • Explain data collection strategy and techniques to analyze collected
  • Understand techniques to identify and diagnose bottlenecks to improve overall performance

 

 

 

  • Module Overview
  • Lesson 1: SQL Server Components and SQLOS
  • Lesson 2: Windows Scheduling vs. SQL Server Scheduling
  • Lesson 3: Waits and Queues
  • Lab: SQL Server Architecture, Scheduling, and Waits
  • Module Overview
  • Lesson 1: Core Concepts of I/O
  • Lesson 2: Storage Solutions
  • Lesson 3: I/O Setup and Testing
  • Lab: Testing Storage Performance
  • Module Overview
  • Lesson 1: Database Structure Internals
  • Lesson 2: Data File Internals
  • Lesson 3: tempdb Internals
  • Lab: Database Structures
  • Module Overview
  • Lesson 1: Windows Memory
  • Lesson 2: SQL Server Memory
  • Lesson 3: In-Memory OLTP
  • Lab: SQL Server Memory
  • Module Overview
  • Lesson 1: Concurrency and Transactions
  • Lesson 2: Locking Internals
  • Lab: Concurrency and Transactions
  • Module Overview
  • Lesson 1: Statistics Internals and Cardinality Estimation
  • Lesson 2: Index Internals
  • Lesson 3: Columnstore Indexes
  • Lab: Statistics and Index Internals

 

  • Module Overview
  • Lesson 1: Query Execution and Query Optimizer Internals
  • Lesson 2: Query Execution Plans
  • Lesson 3: Analyzing Query Execution Plans
  • Lab: Query Execution and Query Plan Analysis
  • Module Overview
  • Lesson 1: Plan Cache Internals
  • Lesson 2: Troubleshooting with the Plan Cache
  • Lesson 3: Query Store
  • Lab: Plan Caching and Recompilation
  • Module Overview
  • Lesson 1: Extended Events Core Concepts
  • Lesson 2: Working with Extended Events
  • Lab: Extended Events
  • Module Overview
  • Lesson 1: Monitoring and Tracing
  • Lesson 2: Baselining and Benchmarking
  • Lab: Monitoring, Tracing, and Baselining