Course Outline: SQL Server 2014 Internals

Page

SQL Server 2014
Internals and Query Tuning
Return to SQLServerInternals Home
Go to Kalen Delaney’s Schedule

Course Description

This is an advanced 5-day course designed for experienced SQL Server professionals. This course will be presented on SQL Server 2014 and cover features specific to that version, but most of the information is relevant to SQL Server 2012 as well.

This 5-day intensive seminar was developed by one of the world’s leading experts in SQL Server internals. Topics to be covered include the following:

•SQL Server Architecture and Metadata
•Physical Storage Structures
•Index Design and Tuning
•Query Processing and Query Plans
•Optimization and Recompilation
•Concurrency Control: Locking, Blocking and Row Level Versioning
•In-memory OLTP
•Query Tips and Techniques
•Troubleshooting Tools

The course includes extensive demonstrations that illustrate the details of SQL Server internals as well as performance tuning techniques. All of the demonstration scripts as well as dozens of whitepapers and useful tools will be made available to the students.

Course Outline

Day 1

Module 1: SQL Server Architecture and Metadata

•Architecture Overview
•Metadata Overview
•Dynamic Management Views
•Process Management
•Memory Management

Module 2: File and Table Structures

•Tools for Examining Physical Structures
•Database Files and Space Allocation
•Table and Page Structures
•Large Datatype Storage
•Sparse Columns
•Data Compression

Day 2

Module 3: Logging and Recovery

•Structure of The Transaction Log
•Management of the Transaction Log
•Recovery and Recovery Models

Module 4: Index Structures and Partitions

•Heaps and B-Trees
•Clustered Indexes
•Nonclustered Indexes
•Fragmentation
•Creating and Managing Partitions
•Metadata for Partitioning
•Columnstore Index Storage
•Clustered Columnstore Indexes

Day 3

Module 5: Query Processing and Query Plans

•SHOWPLAN
•Query Plan Elements
•Types of Joins
•Aggregation
•Sorting
•Data Modification

Module 6: Optimization and Recompilation

•Optimization Overview
•SQL Server’s Query Optimizer
•Plan Management and Reuse
•Causes of Recompilation
•Forcing Recompilation
•Plan Cache Metadata
•XML Plans
•Plan Cache Metadata

Day 4

Module 7: Concurrency Control

•Optimistic and Pessimistic Concurrency
•ANSI SQL Isolation Levels
•Pessimistic Concurrency Control with Locking
•Aspects of Locking
•Locking Resources
•Locking Metadata
•Optimistic Concurrency Control with Row Versioning
•Snapshot Isolation
•Snapshot Isolation Metadata

Module 8: In-Memory OLTP (Hekaton)

•In-Memory OLTP Overview
•Row and Index Structures
•Operations on Memory-optimized Tables
•Concurrency and Isolation Levels for Memory-optimized Tables
•In-Memory OLTP Memory Management
•Checkpoint and Recovery

Day 5

Module 9: Monitoring and Tuning

•Overview of Monitoring Tools
•Monitoring with Extended Events
•Index Tuning
•Indexed Views
•Covering Indexes
•Included Columns
•Filtered Indexes
•General Indexing Strategies
•Query Tuning
•Search Arguments
•Constants and Variables
•Query Hints
•Natively Compiled Procedures for In-Memory OLTP
•Overview of Tuning Methodologies

4 thoughts on “Course Outline: SQL Server 2014 Internals

  1. Sasi

    I like the way the course structure is set. What is the schedule for next batch? Can you please email the course fee and information on upcoming batches?

    • Hi Sasi

      My public schedule is always available here on my site. There is a link to my schedule on the left. The price is set by the training partner, and their contact information can be reached by clicking on the link to the training partner in the right-most column.

      Thanks for your interest!
      Kalen

    • Hi Paul

      You can see my schedule from the link on the left. It’s the first link, right under the picture of the book. The next offering is in November.
      Thanks for your interest!

      ~Kalen Delaney

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s