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 see our Immersion Events Schedule for class dates.


Select Quotes From Past Attendees of Kimberly’s IEVLT Classes

  • “Kimberly is incredibly knowledgeable and was able to adapt the techniques to all the different scenarios presented to her.”
  • “The best educator I’ve ever seen. She makes complex concepts “magically” easy to grasp. Incred-amazing.”
  • “Great course. I could hear clearly, the content was relevant to current day problems, and provided clear instruction.”
  • “This was REALLY good. Getting to an IE is tough there are only a few a year and more importantly because there are only a few they cover a pretty broad range of information. Since I do mainly database design and query tuning, I can’t justify much beyond IE1 as I don’t do day to day DBA work. Seeing you were offering the online event focused on a specific topic – very large tables – was PERFECT. I know I really need to improve my knowledge of the options in that area. I recalled the PV/PT architecture from IE1 and knew a refresher, coupled with the new information would be perfect. The cost was BEYOND reasonable. The time frame, at only about 1/2 a day, was easy to justify and easy to manage keeping up with regular work while I did it. So this worked out to be a perfect event.” – Todd Everett, USA
  • “SLIGHTLY prefer the classroom option, but attendance to a location can be very hard to manage and therefore WebEx is a VERY, VERY good alternative. I didn’t think it would be, but I’m definitely sold.” – Kevin Urquhart, UK
  • “I’m really glad I decided to participate in this event. I learnt a lot and I already see lots of opportunities to implement some of the strategies at my workplace (after working through all the materials of course :P).” – Ernst Kruger, South Africa
  • “Loved the online aspect. It felt like I was there with the question ability and having the questions just answered right there. I felt I had a voice and could ask anything and the ability to watch it later made it totally worth the registration.”
  • “Always love to sit at the feet of Kimberly. Always learn a ton.”
  • “I struggled with WebEx at first… felt a little like watching a video… BUT the moment it became interactive (which was very early on), that whole perception changed. By the end I REALLY enjoyed it as a method.”
  • “I really enjoyed the ability to ask questions as the course went along so that I didn’t forget what I wanted to ask while you were teaching. This allowed for questions to come through and class to continue until a good stopping point to answer the questions. Plus having the questions written from other attendees was nice for future reference instead of trying to remember from an in-person class discussion.”
  • “Kimberly, I have to tell you that you are hands down the finest educator I have ever met. Your dynamic personality, combined with your depth of knowledge, and passion to fully understand the subject matter are unmatched, in my experience. I feel truly privileged to have had the opportunity to learn from you again and again. You really DO make learning fun. Thank you!” – Mike Petri, USA
  • “I learned a lot of new topics and found this class really useful. Usually when I attend a class or an in person event I learn new things but no where near this class. Thanks for a great class.”

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.