Overview

This 3/400-level, 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 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.

Ready to buy? Please see our Immersion Event Offerings for options, bundles, pricing. and more.

Quotes From Past Attendees

Listed below are some verbatim quotes from recent attendees of this class:

• “Amazing learning experience! Having followed SQLskills from afar while developing my SQL Server knowledge, it was eye-opening to learn from Paul and Kimberly in person.”
  
• “In 20 years of database administration, best performance training I have ever had!”
• “Always the best – Paul and Kimberly, you both rock!”
• “I was a better DBA after the first day!”
• “I loved how all the topics built on top of one another. At the end, I was able to the entire picture. This was the most informative class I have EVER had. Thank you!”
• “I really admire your team approach and enjoyed the different presentation styles.”
• “By far the most useful and educational training I have attended. Aside from the material covered, which was well organized, the discussions and Q&A information was great.”
• “It’s not every day I get to meet experts in the industry. Truly, both of you are gifted with amazing teaching skills.”
• “Immediately applicable information gained, already discussing with those in the office while at training.”
• “No other course I have taken has even come close to matching the knowledge I have gained this week.”
• “Both of you approach these topics with genuine enthusiasm – thank you!”
• “Lots of good material – I would love to take another course with SQLskills (I’d even get a lot of value from repeating this same course!)”
• “Excellent course. I consider myself to be lucky to have attended course presented by the ‘best’ in the world. Not only you guys are extremely knowledgeable, but had the ability to break it down.”
• “This was the first course where I was made to feel extremely welcomed, just as a person or guest when they come for a visit. Thank you for your time and effort in making this a great valuable experience.”
• “This was the most unique and amazing course I’ve ever attended. It’s not often that you get the opportunity to learn from two individuals who are so knowledgeable, friendly, and truly *care* that you understand the material. Thank you both so much!”
• “Your overall training methodology is wonderful, and you both are very easy to talk to and ask questions. The training dynamic between you is awesome as it makes training productive but enjoyable.”
• “The difference of knowing how to cook in your own kitchen and then being taught how to be a chef in a 5-star restaurant.”
• “Fantastic knowledge provided by Paul and Kimberly. Answered all of my questions, whether it was high or low level. No arrogance. Paul and Kimberly are very enthusiastic and want to share their knowledge with a genuine and professional approach.”
• “Could we make IEPTO1 mandatory for our application vendors?”
• “Excellent. Mind blown. Can’t wait to take another SQLskills class.”
• “Great course! Impressive scope and depth of knowledge, very well delivered with real-world examples and practical takeaways.”

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: Locking and Blocking

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: Isolation Levels and Versioning

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: Logging, Recovery, and the Transaction Log

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

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 structures
• Heaps vs. clustered indexes
• Clustering key choice
• Clustering key columns in nonclustered indexes
• Nonclustered index structure

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 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. We’ll also dive into the concept of covering and show the benefits of wider indexes. 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: 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: 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
  • Design strategies
  • Overall strategies
• Using the tools for tuning
  • SET STATISTICS IO ON
  • Showplan
  • Missing indexes
• 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)
• Indexed views v. columnstore indexes
• Rowstore indexes v. columnstore indexes

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

• Selectivity and estimates
• Query complexity
• Estimates from statistics
  • Sampling
  • The histogram
  • Filtered statistics
  • Uneven distribution
• Overview: Changes to cardinality estimation in SQL Server 2014
• Migrations / upgrades / regressions
• SQL Server 2016+

Ready to buy? Please see our Immersion Event Offerings for options, bundles, pricing. and more.

Questions?

If you have any questions not answered by our Immersion Events F.A.Q., please contact us.