• Advanced T-SQL Performance Tuning

  • 3 Day Course  |  £1195.00 (+VAT)

Target Audience

This course is aimed at SQL Developers who wish to improve their coding abilities by going deeper into SQL Server internals to ensure that their code works with and not against the database engine.

Required Knowledge

  • Basic SQL Server Optimizer Knowledge
  • Intermediate Execution Plan Analysis
  • Intermediate understanding of T-SQL

Purpose of the Course

The purpose of the course is to provide SQL Developers with a deep knowledge of the SQL Server Optimizer and Execution engines to enable them to write highly performant code and get the very best performance from their servers.

Topics Covered

  • Data Storage and Access
  • Buffer Pool
  • Basic Execution Plan Analysis
  • Indexing
  • Internal Join Types
  • Memory Grants
  • Cost Based Queues
  • Statistics
  • Parameter Sniffing
  • Parallelism
  • Intermediate Execution Plan Analysis
  • Wait Statistics
  • Advanced Execution Plan Analysis

Course Modules

Module 1: Data Storage and Access

  • SQL Server Architecture
  • SQL OS
  • The Optimizer

Module 2: The Buffer Pool

  • Memory Allocation
  • Ghost Cleanup
  • Other Memory Consumers

Module 3: Basic Execution Plan Analysis

  • At a Glance
  • Reads / Writes
  • The 8K Page
  • Execution Plan Warnings

Module 4: Indexing

  • Clustered Indexes
  • Non Clustered Indexes
  • Key Columns vs Included Columns
  • Filtered Indexes and gotchas

Module 5: Optimizer Join Types

  • Loop Join
  • Merge Join
  • Hash Join
  • vNext

Module 6: Memory Grants

  • Spills
  • Concurrency

Module 7: Statistics

  • How to Read a Statistic
  • Performance Impact
  • Statistics Maintenance Tips
  • Filtered Statistics
  • Incremental Statistics

Module 8: Parallelism

  • How it works
  • Performance Boost
  • Performance Pitfalls
  • Optimizer Flaws

Module 9: Cost Based Queues

  • Query Cost
  • Costing Pyramid
  • Concurrency Issues

Module 10: Parameter Sniffing

  • What is Parameter Sniffing?
  • Plan Cache Limitations
  • Alternative Strategies

Module 11: Intermediate Execution Plan Analysis

  • Aggregation Operators
  • Blocking Operators
  • Estimation Quirks

Module 12: Wait Statistics

  • What Are They?
  • How to Collect Them
  • Interpretation

Module 13: Advanced Execution Plan Analysis

  • Deep Walkthrough Analysis of Complex Plans
  • Would you like to discuss your training requirements with me?

    I'm always happy to help so, whatever your question, simply give me a call or send me a message and I'll get back to you as quickly as possible.