Home About Courses Schedule Services Webinars Contact Search

SQL Server Optimization, Performance & Tuning

SEE SCHEDULE

Duration: 3 Days

Method: Instructor led with hands on.

Price: $1800.00

Course Code: SS1030



Audience

This course is intended for SQL developers or anyone who is interested in tuning their queries to produce the best performance possible.

Description

Optimizing SQL queries requires attention to details. This course shows attendees how to maximize query performance through a variety of means, including index management, analyzing query execution plans, coding efficient queries, good data statistics and more. Want to elevate yourself to a higher level of SQL programming? Want to differentiate yourself from most SQL developers in the world today? Want to understand how to execute and understand a SQL Server execution plan output? Want to know what it takes to get optimal performance out of queries, programs, and applications? Then this is your class! SQL statements will be broken down in detail so the participants understand in depth what the SQL Server optimizer goes through in fulfilling an SQL request, and what can be done in order to get it to run faster. Time will especially be spent breaking down SQL Joins, Subqueries, and Table Expressions in order to understand the fundamental issues associated with performance. Performance-related issues in relationship to database design, indexing, efficient and effective SQL queries, and the gathering of accurate statistical information for optimization are addressed. Participants will come out of this class being ‘Empowered’ and having a much better understanding of why certain optimizations are chosen, and what can be done to change them for the better. Much of the class will be in workshops where the participants will code and review queries, execute SQL Server explain plans, and performing analysis on the information. Many of the workshops have the participants rewrite and/or apply tuning statements to existing queries in order to get them to perform more efficiently. Participants will know exactly what steps to take in getting poor performing SQL queries to execute more efficiently.

Objectives

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

  • Describe the process of performance tuning
  • Display a working knowledge of the SQL Server optimizer and access path selection
  • Analyze and understand SQL access paths
  • Manage indexes on tables for maximum query efficiency
  • Understand the importance of statistics in query performance
  • Analyze and optimize the execution plans of queries
  • Design and code efficient performing queries
  • Be move advanced in coding SQL queries

Prerequisites

Attendees should have knowledge of the Structured Query Language and some experience developing SQL queries.

Topics

  1. SQL Server Optimization
    • Optimization Terminology
    • Database Management System Overview
    • SQL Modification Process
    • Understanding the Physical File
    • Tuning SQL
    • What causes CPU and I/O
    • Reducing CPU and I/O
    • The SQL Server Optimizer
  2. SQL Server Access Path Review / Plan Executions
    • Table Scan
    • Table Scan Clustered Index
    • Indexing. Clustered vs Non-clustered
    • Index Seek Processing
    • Index Scan Processing
    • Index Screening
    • Index Only
    • SQL Sorts
    • Parallel Processing
    • Unions
    • Groupings
  3. SQL Server Tuning Strategies
    • Tuning Strategy - A Top Down Approach
    • Tuning Strategy - Where to Start
    • SQL Performance Basics
    • Writing Efficient Queries
    • Understanding SQL Predicates (Indexable vs Non-Indexable)
    • SQL Coding Standards and Guidelines
    • What to look for in Table Scans
    • Knowing your Data
    • Knowing your Queries
  4. Tuning Joins
    • Join Types
      • Inner Joins
      • Outer Joins
    • Join Methods
      • Nested Loop Join
      • Merge Scan Join
      • Hash Joins
    • Join plan execution analysis
  5. Aggregate Functions, SQL Subqueries & Performance
    • Aggregate Functions and Evaluations
    • Subqueries
      • Scalar Fullselects
      • Non Correlated Subqueries
      • Correlated Subqueries
      • Tuning Subqueries using Explain
      • Subquery plan execution analysis
    • Existence Checking
      • Methods of Existence Checking
      • Methods of Not Existence Checking
      • Choosing the best method
    • Analyze and Tune Subqueries
  6. Tables expressions and Performance
    • Nested Table Expressions (derived tables)
    • Common Table Expressions
    • Views and Nested Table Expression Processing
    • Materialization
    • Table expressions plan execution analysis
    • Using Table Expressions as a tuning approach
    • Using Explain to analyze Table Expression Performance
  7. Statistics Analysis
    • The Role of Statistics in Query Optimization
    • Statistics on an Indexed Column
    • Statistics on a Non-Indexed Column
    • Analyzing Statistics
    • Statistics Maintenance
    • Analyzing the Effectiveness of Statistics for a Query
  8. Working with the DBA
    • System Settings
    • Indexing
    • Plan Caching
    • Plan Stability