SQL Training

SQL Server 2005, 2008, 2012 Internals and Query Tuning

Understanding SQL Server means knowing the product inside and out. In this world-renowned five-day course with longtime internals expert and Microsoft MVP Kalen Delaney, students will learn how to take a long, hard look at the SQL Server relational engine. After better understanding what’s happening internally, students will get the opportunity to investigate how internals can affect how you set up your databases for maximum performance and reliability. Query tuning within SQL Server 2005 and 2008, as well as parts of SQL Server 2012, will be discussed in depth.

This course is geared toward both SQL Server DBAs and developers with some experience with application development and architecture.

Register

Coming Soon!

Instructor: Kalen Delaney

Dates: TBD

Location: Your City?

Course Detail PDF Download of: SQL Server 2005, 2008, 2012 Internals and Query Tuning

Download the course details for yourself or your employer!

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 2005 Configuration Tools

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
  • Rebuilding Indexes
  • Partitioning Overview
  • Metadata for Partitioning

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
  • Optimization Overview
  • SQL Server’s Query Optimizer
  • Plan Management and Reuse
  • Causes of Recompilation
  • Forcing Recompilation
  • Optimizer 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 Isolation Metadata
  • Database Snapshots

Module 8: Index Tuning
  • Covering Indexes
  • Included Columns
  • Filtered Indexes
  • General Indexing Strategies

Day 5

Module 9: Query Tuning
  • Query Improvements
  • Search Arguments
  • Constants and Variables
  • Query Hints and Plan Guides

Module 10: Tracing and Troubleshooting
  • Verifying Database Consistency with DBCC
  • Maintenance Suggestions
  • Tracing
  • Other Tools for Tracking Down Problems

Intended Audience

This course is geared toward SQL Server DBAs and developers interested in gaining a better understanding of the relational engine and how it can affect query tuning and troubleshooting.

Prerequisites

SQL Server DBAs and developers with several years of experience are encouraged to attend, although all who are interested in learning more about the workings of the SQL Server engine will benefit from the course.

What You Will Learn

  • SQL Server internals
  • SQL Server architecture
  • Index structures and partitions
  • Query processing
  • Query plans
  • Optimization
  • Concurrency
  • Query Tuning
  • Index Tuning
  • Snapshot Isolation
  • Transaction log

Instructor Bio:

Kalen Delaney

Kalen Delaney remains one of the biggest names in the SQL Server world, as her books and courses continue to sell at record numbers. A Microsoft SQL Server MVP since 1993, Kalen is a hugely active member of the SQL community. In 2012, one of her primary areas of focus is on the changes within SQL Server 2012, especially as they relate to internals, query tuning, and performance.
 
Delaney has been writing about SQL Server for many years, in addition to her courseware development. She is the primary author of Microsoft SQL Server 2008 Internals, Inside SQL Server 2005: Query Tuning and Optimization, and Inside SQL Server 7. She also authored Inside SQL Server 2005: The Storage Engine and Inside SQL Server 2000.

Location: Your City?

Registration: Coming Soon! Click here to request this course

Visa, MasterCard, Discover, American Express, PayPal, eCheck, Check, Purchase Order

Data Educaiton accepts Visa, MasterCard, Discover, American Express, PayPal, eCheck, personal & business checks and purchase orders.

Course Dates: TBD (5 Days)
Course Time: 9:00 AM - 5:00 PM
Course Detail PDF Download of: SQL Server 2005, 2008, 2012 Internals and Query Tuning Download the full course details in a convenient PDF

Using a Purchase Order or Check?

If a purchase order is required for invoice, please send us the P.O. number using our contact form. Be sure to indicate the course title and attendee name(s) in your message.

We accept personal and business checks for course registrations up to 15 business days before the start date of the desired course. If paying by check, remember that it can take up to 5 business days to arrive at our office through postal mail depending on your location. Please include a note telling us the course title and attendee name(s), and make your check payable to:

Data Education
15 Lincoln Street
Suite 226
Wakefield, MA 01880

Refund & Cancellation Policy

If Data Education cancels the course, you will receive a 100% refund. Registration is fully transferable to a course of the same or lesser value within one year’s time of the initial course. If you cancel your registration with more than 30 days notice, Data Education will refund the fee minus $250 for processing. If you cancel your registration with between 16 and 30 days notice, Data Education will refund 75% of the fee. If you cancel your registration with between 7 and 15 days notice, Data Education will refund 50% of the fee. Cancellations unfortunately will not be allowed as of 6 days before the beginning of the course.