SQL Server Internals for Query Tuning - 3 Day Training

UPDATE: As of 2019, this class is also available as

SQL Server Internals for Query Tuning on AZURE

SQL Server Internals for Query Tuning provides the internal details you need to know to make sure you can get the best performance from your SQL Server queries.

This is an advanced course designed for advanced SQL Server professionals. The course includes extensive demonstrations that illustrate the details of SQL Server internals as well as tuning techniques. This course will be presented on SQL Server 2017 and cover features specific to that version, but most of the information is relevant to SQL Server 2016 and SQL Server 2014, and some is relevant to earlier versions. A few new features in SQL Server 2019 will be discussed. Topics to be covered include the following:

  • SQL Server Storage Metadata

  • Tools for exploring storage structures

  • Physical Storage Structures

  • Query Processing and Query Plans

  • Optimization and Recompilation

  • Index Design and Tuning

  • Statistics Internals and Management

  • Query Tuning Tips and Techniques

Course Outline


Part 1: SQL Server Metadata

  • Architecture Overview

  • Metadata Overview

  • Dynamic Management Views

Part 2:  Index Structures

  • Metadata for Storage

  • Space Allocation

  • Tools for Examining Physical Structures

  • Heaps and B-Trees

  • Clustered Indexes

  • Nonclustered Indexes

  • Fragmentation

  • Filtered Indexes

  • Rebuilding Indexes

  • Partitioning Overview

  • Creating and Maintaining Partitions

  • Metadata for Partitioning

  • Columnstore Index Storage


Part 3: Query Processing and Query Plans

  • SHOWPLAN Options

  • Query Plan Elements

  • Types of Joins

  • Aggregation

  • Sorting

  • Columnstore Index Processing

  • Data Modification

  • Query Tuning Basics

Part 4: Optimization and Recompilation

  • Optimization Overview

  • SQL Server’s Query Optimizer

  • Adaptive Query Processing

  • Plan Management and Reuse

  • Causes of Recompilation

  • Forcing Recompilation

  • Statistics Management

  • Plan Cache Metadata



Part 5: Index Tuning

  • Special Index Features

  • Statistics Internals

  • Covering Indexes

  • Filtered Indexes

  • Included Columns

  • Indexing Guidelines

Part 6: Query Tuning

  • Monitoring Tools

  • The Query Store

  • Query Improvements

  • Search Arguments

  • Constants and Variables

  • User Defined Functions

  • Plan Guides

  • Query Hints

  • Query Store

  • Automatic Plan Correction