SQL1901

Ladda ner som PDF

SQL Server Internals for Query Tuning - with Kalen Delaney

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

Målgrupp

This is an advanced course designed for advanced SQL Server professionals

Course Outline

DAY 1

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

 

DAY 2

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

 

DAY 3

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

    ... Läs mer

Course Outline

DAY 1

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

 

DAY 2

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

 

DAY 3

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

    Mikael Wedham

    Organizer of SQL Server Internals for Query Tuning - with Kalen Delaney

    Mikael Wedham is the first Microsoft Certified Master on SQL Server 2008 in Sweden. Working with SQL Server since 1994 he is now an appreciated teacher and speaker in the .NET and SQL areas.

    Mikael has been speaking at several SQL Saturdays throughout Europe and is also the founder of SQL Saturday Gothenburg and co-founder of SQL Saturday Stockholm.

    Karen Delaney

    Kalen Delaney has been working with SQL Server since 1987 when she joined the Sybase Corporation in Berkeley, California. Kalen has an independent international trainer and consultant since 1992. As a consultant, she has worked with both Microsoft Corporation and Sybase Corporation to develop courses and provide internal training for their technical support staff. Kalen has taught Microsoft Official Curriculum courses, as well as her own independently developed Advanced SQL Server Internals courses, to clients around the world. In addition, she has been writing regularly about SQL Server since 1995. Kalen is also a contributing editor and columnist for SQL Server Magazine and has been a SQL Server Most Valuable Professional since 1995.

Kursfakta

Kurs-ID: SQL1901
Längd: 3 dagar
Pris exkl moms: 29 995 kr
Kan betalas med:
TRAINING CARD

Lämna dina kontaktuppgifter om du önskar en företagsintern utbildning.