IECS:
Immersion Event on Columnstore Indexes

Upon completion: students will receive a verifiable badge through Credly/YourAcclaim.com

Overview

This 2/300 level, training course will provide an understanding of the differences between rowstore and columnstore indexes and how to plan for, implement, and maintain columnstore indexes in SQL Server 2012 and higher using best practices and our extensive real-world experience.

The class will start off by providing a foundation of how data is stored in SQL Server using row storage and B-tree indexes compared with columnstore indexes, as a means of understanding why columnstore indexes are better suited for certain workloads. Then you’ll learn how to implement columnstore indexes and strategies for using them, including combining rowstore and columnstore in the same table. We’ll cover how to find information about columnstore indexes once they have been implemented, data loading patterns for different workloads and their impact on columnstore and potential problems to watch out for, and finally how to maintain columnstore indexes and design the schema leveraging partitioning for optimization of the indexes and performance of queries and data loading.

By the time you’ve been through this class, you’ll have the knowledge to choose when to use columnstore indexes and how to use them most effectively in your environment. Dedicating only 3 half-days of your time for 9 hours of lecture and up to 4.5 hours of open Q&A, this is an amazing ROI while you can still get work done during your day!

Instructors: Jonathan Kehayias

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.


Select Quotes From Past Attendees of Jonathan’s Online Classes

  • “Extremely pleased with the course. FAR exceeded my expectations.”
  • “Well worth the time and expense to attend. Would highly recommend this to others.”
  • “Great course – very informative – very great instructors – I am sure to be back!”
  • “Great course. Good new info for me, plus refresher on other info. Thanks!”
  • “Both Erin and Jon have a vast knowledge of not only SQL Server & tools, but also effective presentation.”
  • “Thanks for taking the time to better my knowledge of SQL and allow me to better my career.”
  • “Great course. I could hear clearly, the content was relevant to current day problems, and provided clear instruction.”
  • “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.”

Curriculum

Module 1: Columnstore Index Basics

This module will compare the traditional row-based storage strategy of data in SQL Server, which is optimized for write workloads, to the columnar strategy implemented by columnstore, which is optimized for reading data and compression. This will be the foundation for understanding why columnstore is better for some workloads and may not benefit other workloads, which will be discussed throughout the remainder of this course. Topics covered include:

  • Row-based vs. columnar storage basics
  • Rowgroups
  • Column segments
  • Columnstore index types
  • Features supported by different SQL Server versions

Module 2: Columnstore Usage Strategies

This module will look at determining when to use a clustered columnstore index vs. using traditional rowstore with a non-clustered columnstore index based on workload requirements. Just because you can create a single clustered columnstore index on a table doesn’t mean that is the best option for every scenario. Topics covered include:

  • Clustered columnstore index scenarios
  • Using rowstore indexes for constraints
  • Rowstore with nonclustered columnstore scenarios
  • Determining which strategy is best

Module 3: Dynamic Management Views

This module will explain how to discover the metadata about columnstore indexes, including the current memory used by objects in each database. We will also look at how efficiently the columnstore indexes are stored and how to identify potential issues in a columnstore index. Topics covered include:

  • Investigating columnstore indexes in DMVs
  • Finding memory usage by columnstore objects
  • Finding physical storage information about columnstore indexes
  • Viewing columnstore index usage information

Module 4: Data Loading Patterns

This module will demonstrate the different strategies for loading data into columnstore indexes and demonstrate the different impacts of trickle-insertion of data and bulk loading data using the columnstore metadata DMVs previously discussed in the course. Topics covered include:

  • Bulk loading strategies
  • Understanding batch size impacts
  • Performance considerations
  • Trickle-insert strategies and considerations

Module 5: Columnstore Index Maintenance

Just as with rowstore indexes, columnstore indexes cannot simply be created and then left to manage themselves without any maintenance whatsoever. This module will discuss the internals of reorganizing columnstore indexes and the job of the tuple mover for closing and compressing row groups in the delta store, as well as how to determine when it is time to rebuild a columnstore index to clean up deleted rows within the rowgroups. Topics covered include:

  • How reorganizing works
  • COMPRESS_ALL_ROWGROUPS
  • Determining when to rebuild

Module 6: Partitioned Tables

Even though columnstore indexes have been writeable since SQL Server 2014 and support trickle-insertion of data, the best practices for data loading strategies using table partitioning remain applicable for both performance and manageability. This module will discuss and review the reasons to partition columnstore tables, partition design strategies, parallel data loading, and optimization of the columnstore compression of rowgroups as a part of schema design and data loading strategies. Topics covered include:

  • Reasons to partition columnstore tables
  • Partition design strategies

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.