Special, in person, One Day Sessions...
 Pinal Dave
16thand 17th May 2019
Special, in person, One Day Sessions...
 Pinal Dave
16thand 17th May 2019
Special, in person, One Day Sessions...
 Pinal Dave
16thand 17th May 2019
Special, in person, One Day Sessions...
 Pinal Dave
16thand 17th May 2019

Deep Dive 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 in-memory Module 5: Optimizer Join Types
  • Loop Join
  • Merge Join
  • Hash Join
  • Adaptive Query Joins
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.
Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron