MS SQL Server Performance Tuning Training Course

Learn to diagnose, troubleshoot and fix slow Microsoft SQL Server databases.


Register

About this course

Learn more about the MS SQL Server database performance and tuning training course.



What is the course about

This course is designed to give the right amount of internal knowledge and wealth of practical tuning and optimization techniques that you can put into production. The 5 day class offers a comprehensive coverage of SQL Server architecture, indexing and statistics strategies, optimize transaction log operations, tempdb and data file configuration, transactions and isolation levels, and locking and blocking. The course also teaches how to create baselines and benchmark SQL Server performance, how to analyze workload and figure out where performance problems are, and how to fix them. The course has a special focus on SQL Server I/O, CPU usage, memory usage, query plans, statement execution, parameter sniffing and procedural code, deadlocking, plan cache, wait and latch statistics, Extended Events, DMVs and PerfMon

Duration

The course is run 5 days full time.

Technical Skill

The primary audience for this course is individuals who administer and maintain SQL Server databases and are responsible for 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.

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.
Private Training

This course is offered only privately. The course can be run onsite or on our site. The course can be run from our office in Cape Town or Johannesburg. The course price is R12 599 onsite and R17 500 on our premises. There is no set date to run the course, we schedule the course on dates that suits your team. A minimum of 4 delegates is required to run the course.

Course Schedule

Download the full course schedule below

Performance Tuning Overview

The Performance-Tuning Process
Performance vs. Price
Performance Baseline
Where to Focus Efforts
SQL Server Performance Killers

Performance Monitor Tool
Dynamic Management Views
Hardware Resource Bottlenecks
Memory Bottleneck Analysis
Memory Bottleneck Resolutions
Disk Bottleneck Analysis
Disk Bottleneck Resolutions
Processor Bottleneck Analysis
Processor Bottleneck Resolutions
Network Bottleneck Analysis
Network Bottleneck Resolutions
SQL Server Overall Performance
Creating a Baseline
System Behavior Analysis Against Baseline

Extended Events Wizard
Extended Events Automation
Extended Events Recommendations
Other Query Performance Metrics Methods
Costly Queries Execution Plans

Index Analysis

What Is an Index?
Index Design Recommendations
Clustered Indexes
Non-clustered Indexes Clustered vs. Non-clustered Indexes Advanced Indexing Techniques
ColumnStore Indexes
Special Index Types
Additional Characteristics of Indexes

Database Engine Tuning Advisor Mechanisms
Database Engine Tuning Advisor Examples
Database Engine Tuning Advisor Limitations

Purpose of Bookmark Lookups
Drawbacks of Bookmark Lookups
Analyzing the Cause of a Bookmark Lookup
Resolving Bookmark Lookups

Statistics Analysis

The Role of Statistics in Query Optimization
Statistics on an Indexed Column
Statistics on a Nonindexed Column
Analyzing Statistics
Statistics Maintenance
Analyzing the Effectiveness of Statistics

Causes of Fragmentation
Fragmentation Overhead
Analyzing the Amount of Fragmentation
Fragmentation Resolutions
Significance of the Fill Factor
Automatic Maintenance

Execution Plan Generation
Execution Plan Caching
Components of the Execution Plan
Aging of the Execution Plan
Analyzing the Execution Plan Cache
Execution Plan Reuse
Query Plan Hash and Query Hash
Execution Plan Cache Recommendations

Query Recompilation

Benefits and Drawbacks of Recompilation
Identifying the Statement Causing Recompilation
Analyzing Causes of Recompilation
Avoiding Recompilations

Query Design Recommendations
Operating on Small Result Sets
Using Indexes Effectively
Avoiding Optimizer Hints
Using Domain and Referential Integrity
Avoiding Resource-Intensive Queries
Reducing the Number of Network Round-Trips
Reducing the Transaction Cost

Blocking Fundamentals
Understanding Blocking
Locks
Isolation Levels Effect of Indexes on Locking
Capturing Blocking Information
Blocking Resolutions
Recommendations to Reduce Blocking
Automation to Detect and Collect Blocking Informatio

Deadlock Analysis

Deadlock Fundamentals
Using Error Handling to Catch a Deadlock
Deadlock Analysis
Avoiding Deadlocks

Cursor Fundamentals
Cursor Cost Comparison
Default Result Set
Analyzing SQL Server Overhead with Cursors
Cursor Recommendations

Database Stressing with JMeter
Replaying SQL Scripts with JMeter
Performance Testing Overview
Capturing Data with the Server Side Trace Distributed Replay for Database Testing
Summary and SQL Server Optimization Checklist

Download Course Outline
Course Pricing

Choose your training options. Attend a public or private training.

Public Training
R17500
  • Training at our center
  • Price for 1 delegate
  • Small class size
  • 5 days instructor led
  • Access to Slack channel
  • Access to materials website
  • 6 months after training support
  • Teamviewer, email and telephone support
  • Personalized feedback
  • Light lunches
Reserve Seat
Popular
Private Training - Onsite
RR12599
  • Training on your site
  • Minimum 4 delegates
  • Maximum 12 delegates per class
  • 5 days instructor led
  • Access to Slack channel
  • Access to materials website
  • 6 months after training support
  • Teamviewer, email and telephone support
  • Personalized feedback
  • You provide the lunches
Enquire Now
Private Training - Our Center
R17500
  • Training at our offices
  • Minimum 4 delegates
  • Maximum 12 delegates per class
  • 5 days instructor led
  • Access to Slack channel
  • Access to materials website
  • 6 months after training support
  • Teamviewer, email and telephone support
  • Personalized feedback
  • Light lunches
Enquire Now

Newsletter Sign Up

No Spam - Only latest news, price and activity updates
What people say about our courses

This is what our clients have to say

After attending the Xamarin training from Peruzal on our site, we were able to drastically accelerate our internal mobile app development efforts. Peruzal was a phone call away each time we needed support.
Jonathan Winnaar Scientist @ Institute for Maritime Technology
Came across your courses on Google search, after searching for Ruby training around South Africa. We were completely blown away with how much your trainers know stuff.
Divan Santan Infrastrucutre Engineer @ First National Bank
After our DBA left, we were left with noone with the skills to manage the research and scientific database Infrastrucutre. With Peruzal, we were able to get our team running in a matter of a week.
Encarni Colmenero Astronomer @ Southern African Large Telescope
Our team had different backgrounds, application programmers and astronomers with varied database interactions, we did not have formal training in the MySQL database. Peruzal delivered the training onsite and we were comfortable working with MySQL.
Lucian Botha Astronomy Information System Specialist @ South African Astronomical Observatory
“Coming from an academic background, with no prior exposure to programming, I had to develop an Android mobile app for tuition and research purposes. Peruzal helped me to build and implement the app in record time!”
Thuli Shandu Lecturer @ Univeristy of South Africa
Having years of experience in automation and embedded systems, we wanted our team to have experience in developing for Android. Peruzal did send a trainer onsite and we were blown away with the amount of information we had learnt in just 5 days
Cobus Smith Automation Manager @ ArcelorMittal
FAQS

Answers to some of the most frequently asked questions.



Public Training
What is public training

With public training you attend the course with other delegates from other companies at our premises. If you would like private training we can arrange that the training be done at your premises, however, we require a minimum of 4 delegates for a private course.

What is private training

The training runs at your premises or you can come to us. We require a minimum of 4 delegates for a private training.

Refund Policy

Should you not be happy with the training, this should be raised within the first hour of training. We will try to resolve the issue and if we can't we refund 100% your money.

Cancelling Training

You can not cancel the course once you have booked. Please make sure that you have resolved your dates before making a booking. It takes a long to organise the training, so we will not be able to refund once you book, however we can try to re-schedule the course at a later date for you.

Course Pricing
How much does the course cost?

The course price depends on whether the course will be on your site(private training) or at our offices(public). For a private course we require a minimum of 4 delegates in order to schedule the course.

How long does it take to complete the course

Most of our coruses are offered over 5 days full time, except for the Xamarin Cross Platform Mobile App Development course which is offered over 2 weeks. No part-time courses are offered at the moment.

Accomodation

We don't provide accomodation but we can help you find accomodation close to the center. We can also help you with arranging transport to pick you up from the airport.