About Contact Us

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

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
  • Allocation units and IAM pages
  • Using DBCC PAGE
  • FILESTREAM
  • Data Compression

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:

  • Data file and filegroup layout
  • Allocation algorithms
  • Data file growth and shrinking
  • Instant file initialization
  • Tempdb
  • Monitoring and troubleshooting

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:

  • Anatomy of a transaction
  • Transaction madness
  • Locking
  • Lock escalation
  • Blocking
  • Deadlocks
  • Monitoring and troubleshooting

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:

  • Mixed workload challenges
  • Understanding isolation levels
  • Controlling isolation levels
  • Statement-level read consistency
  • Transaction-level read consistency

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:

  • Logging and log records
  • Analyzing transaction log contents
  • Checkpoints and crash recovery
  • Transaction log logical and physical architecture
  • Recovery models and their effects
  • Log file maintenance
  • Monitoring and troubleshooting

Module 6: Table Design Strategies

After having spent time on internals, we apply them directly to best practices in table design. Topics covered include:

  • Understanding row width (vertical partitioning)
  • Character data, overflow and LOB
  • LOB considerations
  • Application inconsistencies in types

Module 7: 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
  • Heaps
  • Clustered indexes
  • Nonclustered indexes
  • ColumnStore overview
  • Index internals case study

Module 8: 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:

  • How the Storage Engine uses an index structure
  • Forms of index fragmentations
  • Causes and effects of index fragmentation
  • Detecting index fragmentation
  • Removing index fragmentation – REORGANIZE vs REBUILD
  • Case study: GUIDs

Module 9: 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
  • Using INCLUDE (SQL Server 2005+)
  • Using filters (SQL Server 2008+)
    • Interval subsumption

Module 10: 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 11: 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 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 12: 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
  • Partition-aligned indexed views
  • Optimizing sets
    • Other features and partitioning
    • Partitioning vs. filtering

Questions?

If you have any questions, please contact us.