SQL Immersion (IEPTO1): Dublin 2016

Location: Microsoft Auditorium, EPDC2, Leopardstown
Dates: October 3rd – October 7th 2016

Cost:
Early Bird Price (Before June 30th 2016) €2,395
Full Week Price (After June 30th 2016) €2,795


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.

Register Now

This 3/400-level, 5-day, SQL Server training class is part two of our two-part, comprehensive training program on SQL Server performance tuning and optimization. Each week of this course stands alone, but we recommend taking Part 1 before.

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

You will learn how to use specific tools and techniques for analyzing SQL Server: creating and using performance baselines, benchmarking tools, wait and latch statistics, Extended Events, DMVs, and PerfMon. These techniques will be highly adaptable to whatever situation you encounter and you will understand not just how to capture performance data but also how to interpret it, so you can derive answers to your own performance problems rather than relying on someone giving you the answer.

Agenda:

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: Row Versioning and 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
  • 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)

Event Pricing:

  • Early Bird: (€2,395)

    Before June 30th 2016
  • Full Price: (€2,795)

    After June 30th 2016
 Register Now
  • Microsoft

Speakers

  • Paul S. Randal

    Paul S. Randal

    Paul is a Microsoft SQL Server MVP and a Microsoft Regional Director. He spent 9 years working on the SQL Server team, writing DBCC CHECKDB, and ultimately responsible for the entire Storage Engine. In 2007 Paul left Microsoft to co-own and run SQLskills.com, and is a world-renowned author, consultant, and top-rated speaker on SQL Server performance tuning, administration, internals, and HA/DR. When he's not tweeting, blogging, or helping someone recover from a disaster, he's likely to be underwater somewhere in the world with his wife, Kimberly L. Tripp.

  • Kimberly L. Tripp

    Kimberly L. Tripp

    Kimberly has been a Microsoft SQL Server MVP and a Microsoft Regional Director for over 10 years; she's an author, consultant, and top-rated speaker having spoken at PASS, TechEd, SQLintersection, and numerous other events around the world. She has over 20 years of experience designing and tuning SQL Server solutions with a focus on optimizing procedural code, indexing strategies, statistics, and partitioning. When she's not tuning SQL servers, she's diving and photographing all sorts of underwater critters such as frogfish with her husband, Paul S. Randal.

Other Information