SQL Server 2008 & 2012 Architecture, Internals and Tuning (SQL-244)

Course SQL-244

Price: $3,995.00
Software Assurance Value: Not Eligible
Duration: 5 Days

  • Your Home
  • Your Office
  • Your local QS Training Center
  • 100% Satisfied or your money back
  • Cancellation Free Schedule
  • Version Upgrade Discount
  • Corporate Resource Guarantee
  • 4x Microsoft Training partner of the year
  • Highest Student Satisfaction Rating
  • Won 23 prestigious Microsoft nation-wide awards

Your Location: (View location)

Start Date:

  • Sep
    22
  • Dec
    08
Please select a start date! Please select a Start Date.

Time:

  • 8AM - 4PM
Live Remote Instructor Led Training Live Instructor Led Training
  • Couse Description

  • Testimonials

Course Description PDF

**This class is presented in a seminar style which means that the hands-on labs are very limited. Please keep in mind this is an Advanced Class and is only for those students that meet the prerequisites.

Description

SQL Server 2012 Internals is an advanced 5-day course designed for experienced SQL Server professionals. This course will be presented on SQL Server 2012 and cover features specific to that version, but most of the information is relevant to 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
  • 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.

Return to Top


About the Author

The author of the course is Kalen Delaney, author of the best selling books SQL Server 2008 Internals and SQL Server 2012 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.

Return to Top


Audience

Experienced SQL Server professionals.

Return to Top


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 2012 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 troubleshoot those problems.
  • Use the SQL Server 2012 Tracing Tools to determine how SQL Server is performing, and to use those tools as a basis for improving performance.

Return to Top


Prerequisites

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.

Return to Top


Course Outline

Day 1

Module 1: SQL Server Architecture and Metadata

  • Architecture Overview
  • Metadata Overview
  • Dynamic Management Views
  • Memory Management
  • Process Management
  • The Resource Governor
  • SQL Server Configuration Tools
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
  • Rebuilding Indexes
  • Partitioning Overview
  • Creating and Maintaining Partitions
  • Metadata for Partitioning
  • Columnstore Index Storage
Day 3

Module 5: Query Processing and Query Plans
  • SHOWPLAN
  • Query Plan Elements
  • Types of Joins
  • Aggregation
  • Sorting
  • Unions
  • 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
  • Plan Cache Metadata
  • XML Plans
Day 4

Module 7: Concurrency Control
  • Optimistic and Pessimistic Concurrency
  • ANSI SQL Isolation Levels
  • Pessimistic Concurrency Control with Locking
  • Aspects of Locking
  • Locking Resources
  • Controlling Locking
  • Locking Metadata
  • Optimistic Concurrency Control with Row Versioning
  • Snapshot Isolation
  • Snapshot Metadata
  • Database Snapshots
Module 8: Index Tuning
  • Indexed Views
  • Covering Indexes
  • Included Columns
  • Filtered Indexes
  • General Indexing Strategies
Day 5

Module 9: Query Tuning
  • Query Improvements
  • Search Arguments
  • Constants and Variables
  • Query Hints
Module 10: Maintenance and Troubleshooting
  • Verifying Database Consistency with DBCC
  • Maintenance Suggestions
  • Tracing and Troubleshooting Tools

Return to Top

Most Recent Customer Reviews

I did remote training so quality of audio and visual is important we had some setup issues with both audio and visual initially but once things were settled, both were fine.
Kalen Delaney is by far the best training instructor I've ever had.
Kalen really knows her stuff + her teacher background really help communicating her knowledge to students.
Kalen is an ace! Awesome presentation. She makes the distance learning thing not even an issue. I will look for presentations from Kalen at SQL events in the future.