IE1: Immersion Event on Internals and Performance

Overview

This 5-day SQL Server training class is focused on the core fundamentals and foundation for SQL Server experts. While many folks are just looking for ‘the answer’, attendees of this class will learn how to derive the answer for themselves. The more you know about how SQL Server works the easier you will be able to respond to performance, design, and corruption problems – as well as solve them!

In addition to internals, this class covers some of the most important elements of good design, indexing and isolation – items core to database scalability and performance. This class provides an excellent knowledge base for all other Immersion Events and the information within it should be considered a pre-requisite for the other courses (although attending this class is not a pre-requisite for attending the others).

Instructors: Paul S. Randal, Kimberly L. Tripp

Need Help Justifying Training? Here’s a letter to your boss explaining why SQLskills training is worthwhile and a list of community blog posts about our classes.

Please see our Immersion Events Schedule for class dates and our comprehensive Immersion Events FAQ for class costs and other frequently asked questions.


Curriculum

Module 1: Database Structures

The fundamental building block of knowledge is how SQL Server stores data on disk and this module covers all structures in a database. Topics covered include:

  • Records
  • Pages
  • Extents
  • Allocation bitmaps
  • IAM chains and allocation units
  • 2008 new structures (and feature overview)

Module 2: Data File Internals and Maintenance

Although this may seem like a simple topic, there are many things to consider around data file configuration and management, especially for tempdb. Topics covered include:

  • Physical layout considerations
  • Allocation algorithms
  • Instant initialization
  • Auto-grow
  • To shrink or not to shrink?
  • Tempdb

Module 3: Transactions and Locking

All operations performed by SQL Server are done in the context of transactions and all transactions involve locking to some degree. This module will explain how transactions and locking work, plus explore blocking and deadlocks and the performance and application problems that can result. Topics covered include:

  • The anatomy of a data modification
  • Locking and blocking
    • Granularity
    • Escalation
    • Duration
  • Troubleshooting locking behavior
    • Blocking situations
    • Deadlock situations

Module 4: Snapshot Isolation

In many systems today, real-time analysis is required – often at the expense of OLTP activity. And, when significant OLTP activity is present, real-time analysis is prone to inconsistencies. After discussing locking and blocking, we move to discussing statement-level and transaction-level inconsistencies, when they can occur, how to minimize/eliminate them using locking (at the expense of concurrency) and then how to use versioning to get the best of both worlds (concurrency and accuracy). But, at what expense? The primary focus of this module is to discuss when using these isolation levels is appropriate, what the possible trade-offs are, and what are the best practices for using versioning. Topics covered include:

  • Understanding isolation levels
  • Isolation in SQL Server
  • Controlling isolation levels
  • Statement-level read consistency
  • Transaction-level read consistency
  • Overhead/Monitoring
  • Isolation summary

Module 5: Logging, Recovery, and Log File Internals and Maintenance

Logging and recovery underpins SQL Server’s ability to recover after a crash and all high-availability and disaster recovery technologies. It can also have a profound effect on the performance of a workload. This module will explain these topics in great depth. Topics covered include:

  • Transaction log architecture
  • Log records
  • Checkpoints and recovery
  • Transaction log operations
  • Recovery models
  • Log file provisioning and maintenance

Module 6: Index Internals

Taking your design further, we next consider our base table strategies in indexes. This is one of the most misunderstood and improperly handled areas of SQL Server. This module explains the internals of both clustered and nonclustered indexes and is the foundation for the remainder of the class. Topics covered include:

  • Index concepts
  • Table structure
  • Index internals
    • Heaps
    • Why cluster
    • Table usage
  • Clustering key columns in nonclustered indexes

Module 7: Index Fragmentation

Even with the most carefully-designed indexing strategy, fragmentation is unavoidable. This module explains the causes and effects of fragmentation, as well as the trade-offs between the various methods of removing it. Topics covered include:

  • Data access methods
  • What is fragmentation?
  • How does fragmentation happen?
  • Detecting, mitigating, removing fragmentation

Module 8: Internals and Data Access 

Understanding the options that SQL Server has for accessing data from within indexes is critical to creating the RIGHT indexes. In this module we continue our internals discussion with primary focus on how some of the special features (INCLUDE and filters) enhance your indexing options. Topics covered include:

  • Data access patterns
  • Covering
    • Understanding selectivity
    • Understanding the “tipping point”
  • What methods exist for covering?
    • Nonclustered indexes (all releases)
    • Using indexed views (SQL Server 2000+)
    • Using INCLUDE (SQL Server 2005+)
    • Using filtered indexes (SQL Server 2008+)
    • Using filtered statistics (SQL Server 2008+)
  • Too many cooks in the kitchen…
  • Index consolidation

Module 9: Statistics

Now that we know how SQL Server uses indexes and the different options available, how does SQL Server know which index to choose? Topics covered include:

  • Cost-based optimization
  • Data access patterns
  • Statistics
    • What they look like
    • What they are telling us
    • How to see them
    • When/how they get created
    • When/how they get updated
  • Problems/solutions with statistics
    • Steps in histogram
    • Filtered statistics
    • Uneven distribution

Module 10: Indexing Strategies

What kinds of indexes are best to create? In what order should the columns be and should they be in the key or only in the leaf-level? This module focuses on a variety of real queries and then debates all of the options that should be considered and above all emphasizes consolidation. Topics covered include:

  • Indexing for performance
  • Indexing for AND (highly-selective queries, index intersection, covering)
  • Indexing for OR (tuning, re-writing)
  • Indexing for joins (join types, which strategies use what types of indexes, 3 phases of tuning)
  • Indexing for aggregates (hash aggregates, stream aggregates)
  • Indexed views (overview/key points)

Module 11: Partitioning

Primarily we discuss the use and benefits of vertical partitioning as well as horizontal partitioning. Topics covered include:

  • Horizontal partitioning strategies
    • Partitioned views
    • Partitioned tables
  • Implementing the sliding window scenario
  • Partitioning design techniques combined
  • The case for columnstore indexes

Questions?

If you have any questions, please contact us.