IEVLT: Immersion Event on Very Large Tables: Optimizing Performance and Availability through Partitioning

Overview

Very large tables are large because they’re holding data that’s not only current but historical. Companies are keeping more data around longer and wanting to better evaluate that data for trends over time. These larger-and-larger data sets become difficult to deal with as there are many distinct and often conflicting access patterns, from the critical “hot” data needing to be inserted, to the somewhat recent “read-mostly” data that’s still fairly active, to the older data that’s rarely used but still needed. All of this is “sales” data and it seems appropriate to have it stored in our “sales” table – but how do we do this efficiently and effectively so that modifications do not affect queries and queries do not affect modifications. Worse yet, maintenance is needed for the volatile data but not the older / static data so how can we do this on our large tables when really only a small portion of the table needs maintenance.

But, it’s really a lot more than this. There have long been features (partitioned tables since SQL Server 2005, and partitioned views since SQL Server 7.0) that tackle various aspects of the “VLT” problem but each feature has problems in and of itself. What this class does is directly address all of the problems to show you a realistic and powerful architecture that gives you the best of all worlds: scalability, maintainability, and high-availability.

This class is delivered live via online streaming.

Instructor: 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 register? Please go to the class page for details on the class cost and logistics, and the registration link.


Select Quotes From Past Attendees of Kimberly’s SQLskills Classes

  • “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!”
  • “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.”
  • “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.”
  • “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.”
  • “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: Horizontal Partitioning Strategies

The first module has us looking at the problem space and the primary areas of interest – the workload and the varying access patterns that drive our partitioning strategies and architectures. Topics covered include:

  • Workloads / time-based data patterns
  • Horizontal partitioning: motivation?
  • Horizontal partitioning (partitioned views or partitioned tables)
  • Functional partitioning

Module 2: Partitioned Views

While partitioned views are the original and “old way” of partitioning – first available in SQL Server 7.0 – there are still many reasons to consider their use. This module will introduce them as well as discuss their requirements and limitations. Topics covered include:

  • Creating partitioned views
  • Check constraints (filtering)
  • Verify constraint is “trusted”
  • Partitioned view challenges
  • Stored procedure estimates and recompilation

Module 3: Partitioned Tables

Partitioned tables are the “newer” way of partitioning – available from SQL Server 2005 onward – and while they are the newer feature with many benefits, there are still limitations (even in SQL Server 2016 and SQL Server 2017) that make fully understanding their architecture critical for VLTs. Partitioned tables are a bit more complicated to learn as there are many features that are designed for them. This module will introduce the structures and the syntax for creating them as well as their requirements and limitations but later modules will cover some of their complementary features. Topics covered include:

  • Range-based partitioned tables
  • Partition function
    • Left or right?
    • Partitioning: date column
  • Partition scheme
    • Aligned schemes
    • Storage-aligned schemes

Module 4: Tables, Indexes, Keys, and Relationships

Verifying relationships, defining keys, and knowing exactly where your data resides becomes more and more difficult when you have more complicated architectures. Additionally, creating indexes becomes more important. In this module we’ll look at the concerns and considerations you’ll have to make with your existing structures to support your desired partitioning strategy. Topics covered include:

  • Partitioning keys
  • Primary and foreign keys
  • Relationships
  • Partitioned object indexing strategies
  • Verifying partition data / location

Module 5: Implementing the Sliding Window Scenario

As time moves forward, data changes. New data is added and old data needs to: be deleted, be archived, or just become less and less used. How do you deal with this? Topics covered include:

  • The Sliding Window Scenario
  • The Sliding Window Scenario with partitioned tables
  • The Sliding Window Scenario with partitioned views
  • Switching data IN
    • Staging area/loading in a heap
    • Why load into a staging area?
  • Switching data OUT
    • For archiving
    • For removal
      • New in 2016
  • The Sliding Window Scenario key points

Module 6: Key Partitioning Concerns and Considerations

Even with everything we’ve discussed there are still other related features that might give you grief, that’s where this module comes in. We’ll go over some of the special considerations and even some of the things that are often confused with partitioning (like filtering) and why they may or may not be used in some situations. Topics covered include:

  • Special considerations for partitioned tables
  • Online operations, indexes, and partitioning
  • Incremental statistics updates
  • Partition-aligned index views
  • Partitioned table with filtered indexes
  • Interval subsumption
    • Partitioning / fast-switching

Module 7: Partitioning Techniques Combined

Ultimately, the most effective partitioning solutions are those where a layered approach is used. This architecture combines the best of both features and subsequently resolves all of their negatives. Topics covered include:

  • Partitioning: partitioned views vs. partitioned tables
  • Damaged “partitions” on Enterprise Edition
  • Functionally partitioning data
  • Other features and partitioning
  • Partitioning for performance

Module 8: Review

We will have covered a lot of content in our course but what should you do from here before you implement a partitioned solution? I’ll give you an ordered set of scripts and written exercises/lab that will give you a practical approach to partitioning so that your adequately prepared for your next VLT discussion!

  • Review
  • Self-paced study
  • Lab exercises

Ready to register? Please go to the class page for the class cost, logistical details, and the registration link.


Questions?

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