Worth Corner, Crawley, RH10 7SL

Deep Dive T-SQL Performance Tuning

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 Queries

  • 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 9: Cost Based Queries

  • Query Cost
  • Costing Pyramid
  • Concurrency Issues

Module 2: The Buffer Pool

  • Memory Allocation
  • Ghost Cleanup
  • Other Memory Consumers
  • Memory Based DMVs

Module 10: Parameter Sniffing

  • What is it?
  • Plan Cache Limitations
  • Alternative Strategies

Module 3: Basic Execution Plans

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

Module 11: Intermediate Execution Plan Analysis

  • Aggregation Operators
  • Blocking Operators
  • Estimation Quirks

Module 4: Indexing

  • Clustered Indexes
  • Non-clustered Indexes
  • Key Columns vs Included Columns
  • UNION
  • INTERSECT / EXCEPT

Module 12: Locking and Blocking

  • What are Locks and Blocks?
  • Lock Types
  • Lock Escalation
  • Deadlocks

Module 5: Optimizer Join Types

  • Loop Join
  • Merge Join
  • Hash Join
  • Adaptive Query Joins

Module 13: Isolation Levels

  • Types of Isolation
  • Use Cases
  • Snapshot and Locking

Module 6: Memory Grants

  • Spills
  • Costing Process
  • Concurrency

Module 14: In-Memory

  • What is it?
  • How it works
  • Isolation Levels
  • Performance & Pitfalls

Module 7: Statistics

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

Module 15: Wait Statistics

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

Module 8: Parallelism

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

Module 16: Advanced Execution Plan Analysis

  • Live Troubleshooting
  • Deep Walkthrough of Complex Plans
  • Optimizer Hints and Tricks

Book Course

Sign up for email alerts

Stay current with our latest insights

© Copyright 2020 SQLTraining Ltd.