Introduction

Master SQL database optimisation on this 3-day course.

This is a hands-on course. It includes lots of time to practice what you are being taught and try things out for yourself.

By the time you finish, you’ll be comfortable using a wide range of SQL’s optimisation tools.

✔ A complete performance tuning course for SQL databases.
✔ Plenty of one to one attention and time to ask questions.
✔ Friendly expert trainers, small groups and a comfortable place to learn.
✔ All the materials and extras that you’ll ever need.
✔ Ongoing support and help with issues you have after the course.

What Will I Learn?

After completing this course, you will have a good understanding of:

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

Am I Ready For This Course?

This course is intended for database professionals who looking to expand their understanding of the techniques used to optimise the performance of databases.

To get the most from this course you will need a working knowledge of:

  • Database administration and maintenance.
  • Core Windows Server skills.
  • Writing queries using Microsoft SQL Server Transact-SQL.

The Training Day

Our courses run from 9.30am to roughly 4.15pm with refreshments throughout the day and a break for a tasty, freshly prepared lunch.

We offer a relaxed, supportive learning environment, fully air-conditioned training facilities and some of the nicest instructors on the planet.

Also, you’ll receive:

✔ A full-colour manual covering everything in the course so that you can recap.
✔ A USB stick for the course exercises to let you practice more in your own time.
✔ A Certificate of Attendance.

  • 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