• Home
  • SQL Server 2012 & 2014 Internals and Query Tuning (SQL-245)
Database Administration SQL Server 2012 & 2014 Internals and Query Tuning (SQL-245)

SQL Server 2012 & 2014 Internals and Query Tuning (SQL-245)


Date of Class:


Last Day To Enroll:

3995.00 3,995.00

  • Course Delivery: Virtual Classroom
  • Language: English
  • Audience: SQL Server

Chat Live | Contact Us | Toll Free: (888) 360-8764



SQL Server 2014 Internals and Query Tuning 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 much of the information is relevant to SQL Server 2012 and SQL Server 2008 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.


Experienced SQL Server professionals.
At Course Completion
At the end of this course, the student will be able to:

  •    Examine details of SQL Server's internal structures to determine their impact on query performance.
  •    Use the SQL Server 2014 Dynamic Management Objects to determine SQL Server's internal behavior.
  •    Read query plans to determine where a poorly performing query needs tuning.
  •    Determine if concurrency issues are causing problems with performance and choose an appropriate solution.


The author of the course is Kalen Delaney, author of SQL Server 2012 Internals and SQL Server 2014 In-Memory OLTP Internals. Kalen has been a columnist at SQL Server Magazine since the first issue, as well as writing for MSDN and TechNet Magazines. Kalen's courses and seminars on SQL Server Internals have successfully delivered at internal Microsoft training events and as on-site courses for premier Microsoft partners around the world.

Topics Covered

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 Data 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
  •       Union
  •       Data Modification

Module 6: Optimization and Recompilation
  •       Query Processing Overview
  •       Index and Column Statistics
  •       SQL Server's Query Optimizer
  •       Plan Management and Reuse
  •       Causes of Recompilation
  •       Forcing Recompilation
  •       XML Plans
  •       Plan Cache Metadata

Day 4
Module 7: Concurrency Control
  •       Optimistic and Pessimistic Concurrency
  •       Consistency Guarantees and Isolation Levels
  •       Pessimistic Concurrency Control with Locking
  •       Aspects of Locking
  •       Locking Resources
  •       Locking Metadata
  •       Optimistic Concurrency Control with the Version Store
  •       Snapshot Isolation
  •       Snapshot 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


Before attending this course, students should have a good understanding and practical experience of SQL Server programming and administration. This is an advanced seminar designed for experienced SQL Server professionals.


Subject Matter Expert

360training, powered by QuickStart brings a quarter-century of keeping pace with the ever-evolving IT industry. You will find that experience reflected in every course, and every training modality we offer. We train more than 11,000 IT professionals and developers annually, and 97% of them say they are very glad they chose us.

Got questions? Contact us below or call 877-881-2235

Why Choose 360training.com?

  • Fast and easy courses completion
  • Get an education faster than at traditional colleges!
  • 100% online - No classroom attendance required.
  • Unlimited 24x7 online customer support
  • Over 500,000+ certified nationwide.