IEPTO1: Immersion Event on Performance Tuning and Optimization – Part 1

(Formerly IE1)

Overview

This 3/400-level, 5-day, SQL Server training class is part one of our two-part, comprehensive training program on SQL Server performance tuning and optimization (with IEPTO2 being the second half). Each week of this course stands alone, but we recommend taking Part 1 before Part 2 as more than 90% of people who attend IEPTO2 first then go back and take IEPTO1.

This class will give you a wealth of practical optimization techniques that you can put into production as soon as you return to work and we ensure you understand *why* and *how* a technique works rather than just what the technique is. This usually means we will explain how SQL Server works internally while discussing a specific topic. Our aim is that you can apply what we teach you to any situation and justify your solution to your peers, management, or clients (yes, we welcome SQL Server consultants in our classes).

As well as optimization techniques, this course will also help with design and architecture so you can prevent performance and scalability problems from happening. The cores of this class are comprehensive coverage of indexing and statistics strategies: a SQL Server workload will not perform well unless these are designed, implemented, and tuned correctly. You will also learn why and how to optimize transaction log operations, tempdb and data file configuration, transactions and isolation levels, and locking and blocking.

All these topics will be addressed on multiple fronts: how SQL Server works, implementation considerations and techniques, common problems, troubleshooting, and resolutions.

If you have previously attended IE1, you do not need to attend this course and you should attend IEPTO2 next.

This course will cover SQL Server 2005 through SQL Server 2014, and is essential for all SQL Server developers, DBAs, and architects. We will answer all your questions, to whatever depth you want to go.

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 for all SQL Server performance tuning and optimization is how SQL Server stores data on disk and this module covers the structures in a database. Topics covered include:

  • Records, record structure, and optimizations
  • Pages and page structure
  • Allocation bitmaps
  • IAM chains and allocation units

Module 2: Data File Internals and Maintenance

There are many things to consider around data file configuration and management, especially for tempdb. Topics covered include:

  • Physical layout considerations
  • Allocation algorithms and optimizations
  • Instant initialization
  • Growth, shrink, and their problems
  • Data compression
  • Tempdb configuration and performance

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

Module 5: Optimizing Logging and Recovery

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, including:

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

Module 6: Index Internals and Data Access

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. We’ll also start reviewing data access methods and the limited uses to nonclustered indexes. Topics covered include:

  • Index concepts
  • Table structure
  • Index internals
    • Heaps vs. clustered indexes
    • Table usage
  • Clustering key choice and performance implications
    • Data access patterns
    • Understanding selectivity
    • Understanding the ‘tipping point’

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
  • Fragmentation and its effect on performance
  • How does fragmentation happen?
  • Optimizing indexes to remove and prevent fragmentation

Module 8: Internals and Covering

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. We’ll also dive into the concept of covering and show the benefits of wider indexes. Topics covered include:

  • What methods exist for covering?
    • Nonclustered indexes
    • Using indexed views
    • Using INCLUDE
    • Using filtered indexes
    • Using filtered statistics
  • Practical index consolidation

Module 9: Statistics: Internals and Updates

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:

  • How the Optimizer uses statistics
  • Statistics from A-to-Z
    • What they look like
    • What they are telling us
    • How to see them
    • When/how they get created
    • When/how they get updated

Module 10: Cardinality Estimation Issues

SQL Server needs to “know” how much data to process in order to process it efficiently. There are many places where this is difficult to do. Knowing the limitations of SQL Server as well as many straightforward and even relatively easy workarounds, can make a huge difference in your queries performance. Topics covered include:

  • Problems/solutions with statistics
    • Steps in histogram
    • Filtered statistics
    • Uneven distribution
    • SQL Server 2014 cardinality estimation changes

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 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)

Questions?

If you have any questions, please contact us.