"A Training Solution Provider delivering Learning Skills to keep forever"

Call the JCS training team free now 0800 5425 150 Or mail Email | training@jcstraining.com

Microsoft SQL Server Query Performance Tuning & Optimization DURATION: 2 Days

Course Overview

INTRODUCTION This 2 day course is intended for students who are already competent at querying data in a Microsoft SQL Server database, and need to take their skills to the next level. This course would be suitable for anyone who wants to more clearly understand the inner workings of Microsoft SQL Server from a performance perspective. After taking this course, students will be able to analyse the performance of their queries, and the database engine, and be able to rectify various performance related problems. You will find that this is a comprehensive course for this level, with plenty of illustrated examples and augmented with practical hands-on exercises.






Students should be familiar with working in a Microsoft Windows Server operating system environment. Delegates should also understand the fundamental design of relational databases including, but not restricted to, the purpose of primary and foreign keys, entity relationships, and data normalization. A good working knowledge of the SQL language will also be assumed.

This is an advanced level course, and attendees should feel confident querying and analysing data in a Microsoft SQL Server environment.


Upon successful completion of this course, students will be able to:

  • Understand the issues related to querying data that affect performance
  • Use the right tools and utilities for analysing various facets of performance
  • Measure the utilization of system resources
  • Analyse the performance of queries
  • Understand and implement different indexing architectures
  • Understand how indexing affects query performance
  • Manage and maintain indexes
  • Understand and implement Full Text indexes
  • Understand and implement Columnstore indexes
  • Analyse and manage statistics
  • Understand how statistics affect query performance
  • Examine and interpret query execution plans
  • Fine tune queries for performance
  • Understand the effect that locking and blocking can have on performance
  • Resolve issues related to locking and blocking
  • Configure the server and database environment for optimal performance
  • Implement best practices when writing queries


  1. Introduction to Query Performance Tuning

Performance Tuning Overview

SQL Server Performance Killers

  1. Resource Performance Analysis

Tools of the Trade

Memory Performance

Disk Performance

CPU Performance

Network performance

SQL Server Performance

Baselining Performance

  1. Query Performance Analysis

Implementing Extended Events

Analysing Costly Queries

Analysing Execution Plans

  1. SQL Server Indexes

Introducing SQL Server Row Indexes

Index Design Considerations

Clustered Indexes

Nonclustered Indexes

Comparing Clustered and Nonclustered Indexes

Indexing Strategies and Techniques

Using the Database Engine Tuning Advisor

Analysing and Resolving Key Lookup Issues

  1. Index Management and Maintenance

What is Index Fragmentation?

Analysing Index Fragmentation

Resolving Fragmentation issues

Looking for Missing Indexes

  1. Special Index Types

Full Text Indexes

Columnstore Indexes

  1. Analysing and Managing Statistics

What are Statistics?

Analysing Statistics

Managing Statistics

How Statistics Affect Query Performance

  1. Query Execution Plans

What are Execution Plans?

How Execution Plans are Generated

Analysing the Execution Plan Cache

The Effect of Ad Hoc Workloads

Execution Plan Best Practices

Parameter Sniffing

Analysing Query Recompilation Issues

Ways to Avoid Query recompilation

  1. Query Design Best Practices

Query Against Small Sets of Data

Make Effective Use of Indexes

The Effect of Optimizer Hints

Other Important Considerations

  1. Blocking and Locking

Introducing Blocking and Locking Concepts

Lock Types, Operations, and Modes

Isolation Levels

Locking and Indexes

Analysing and Resolving Blocking Issues

Analysing and Resolving Deadlocks

  1. SQL Server Optimization Checklist

Database Design Considerations

Configuration Settings

Database Administration

Query Design Checklist