SQL Server Internals for Query Tuning on AZURE - 3 Day Training

Available as of February, 2019!

SQL Server Internals for Query Tuning on AZURE provides the internal details you need to know to make sure you can get the best performance from your SQL Server queries when running SQL Server on Microsoft’s Cloud platform.

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 Azure SQL DB , but most of the information is relevant to SQL Server 2017, SQL Server 2016 and SQL Server 2014. A few new features in SQL Server 2019 will be discussed. Topics to be covered include the following:

  • SQL Server options on Azure

  • 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 on Azure

  • Azure SQL DB

  • SQL Server in a Managed Instance

  • Other Azure Options

  • Comparison of Azure With Other Cloud Platforms

Part 2: SQL Server Metadata

  • Architecture Overview

  • Metadata Overview

  • Dynamic Management Views

  • AZURE-specific Metadata

Part 3:  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

  • Columnstore Index Storage


Part 4: Query Processing and Query Plans

  • SHOWPLAN Options

  • Query Plan Elements

  • Types of Joins

  • Aggregation

  • Sorting

  • Columnstore Index Processing

  • Data Modification

  • Query Tuning Basics

Part 5: Optimization and Recompilation

  • Optimization Overview

  • SQL Server’s Query Optimizer

  • Adaptive Query Processing

  • Plan Management and Reuse

  • Causes of Recompilation

  • Forcing Recompilation

  • Statistics Management



Part 6: Index Tuning

  • Special Index Features

  • Statistics Internals

  • Covering Indexes

  • Filtered Indexes

  • Included Columns

  • Indexing Guidelines

Part 6: Query Tuning

  • Monitoring Tools for Azure

  • The Query Store

  • Query Improvements

  • Search Arguments

  • Constants and Variables

  • User Defined Functions

  • Plan Guides

  • Query Hints

  • Query Store

  • Automatic Plan Correction