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