IECS:
Immersion Event on Columnstore Indexes

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

Course Formats

Online deliveries: 3 consecutive half days
Available ONDEMAND now: Check out our recorded course library

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!

This class is delivered live via online streaming and the recordings are available to purchase HERE.

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 register? Please see our Immersion Events Schedule for class dates and registration details.


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 register? Please see our Immersion Events Schedule for class dates and registration details.


Questions? Please contact us.

Course Formats

Online deliveries: 3 consecutive half days
Available ONDEMAND now: Check out our recorded course library

Overview

The Query Store feature was introduced in SQL Server 2016 and it’s a game-changer for data professionals.  Have you ever…

  • …had that emergency where you’re informed “The database is slow! It was fine yesterday! Nothing changed!”?
  • …wished for a query’s baseline metrics so that you could quickly compare pre- and post-upgrade performance?
  • …flailed about querying DMV after DMV to figure out the *real* problem with performance?

If so, then this is the course where you learn how to stop reacting and start making Query Store work for you.

Query Store allows you to proactively monitor query performance and find those queries that are currently causing problems, or have the potential to cause problems. In addition, you can stabilize query performance use plan forcing, whether that’s something you do manually or automatically with Automatic Plan Correction. Beyond the basics, you’ll see demos that teach you how to interrogate the data to find common patterns in query performance using T-SQL and visualization techniques, and how to take advantage of the wait statistics information captured. After this class you will have the skills to find and leverage important information in Query Store to make query tuning easier.

This class is delivered live via online streaming and the recordings are available to purchase HERE.

Instructor: Erin Stellato

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 Erin’s SQLskills Classes

  • “Extremely pleased with the course. FAR exceeded my expectations.”
  • “Well worth the time and expense to attend. Would highly recommend this to others.”
  • “I though this was extremely worthwhile. I have been a DBA for a few years and this was a great refresher for a lot of things I don’t do very often but should.”
  • “Found class extremely helpful and plan to take the next one.”
  • “Great course – very informative – very great instructors – I am sure to be back!”
  • “I was familiar with all of the topics in this course, but barely understood most. I feel I will be able to begin to do all of the things I need to take simpler tasks from our DBAs to allow them to focus on the more advanced tasks. That was exactly my goal for taking this class.”
  • “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.”

Curriculum

Module 1: Query Store Fundamentals

In the first module we will create the foundation on which the rest of the class will build, as we cover requirements, configuration, and data captured. We’ll discuss methods traditionally used to capture query performance data, and how we find that same information within Query Store. Topics covered include:

  • What’s in Query Store (and what isn’t)
  • Query Store settings
  • How it works

Module 2: Understanding the Query Store Data

If you want to make decisions about query performance, it’s essential to understand the data used to make such decisions. We will look at the data in detail to understand what the data represents and how it compares to the data you’ve used historically. Topics covered include:

  • Understanding runtime statistics
  • Understanding wait statistics
  • The relationship between statistics and plans

Module 3: Workload Characteristics and Query Store Performance

Once data professionals realize the value of Query Store, a typical follow-up question is “How will it affect performance?” In this module we’ll look at how different workload types affect the data stored in Query Store, and the workload characteristics to monitor to understand how they affect Query Store performance. Topics covered include:

  • Typical workloads
  • How to identify what type of workload you have
  • Performance with Query Store enabled

Module 4: Finding Performance Issues

Query Store is designed to help you quickly identify “what happened”, but you need to know where to find that information and how to leverage the built-in reports to quickly diagnose poor-performing and regressed queries. The reports may not always provide what you need, or want, but you have the ability to write your own queries against the data to return the information you need. Topics covered include:

  • Using reports
  • Internal schema
  • Writing your own Query Store queries

Module 5: Forcing Plans

DBAs and developers have a long history of coming up with creative ways to stabilize query performance without refactoring code. Query Store provides another option with plan forcing, a very easy (but still temporary) solution to ensure a query consistently uses the same plan. But there are many details to be aware of with forcing, which we’ll review in depth in this module. Topics covered include:

  • When and how to force a plan
  • What causes forcing to fail
  • Monitoring and addressing failures

Module 6: Automatic Tuning

Microsoft continues to move forward the concept of the intelligent database, with Automatic Tuning forming one small part of that automation. Comprised of Automatic Plan Correction and Automatic Index Management, we’ll review both features to see how they work and how they rely on Query Store data. Topics covered:

  • Automatic Plan Correction
  • Automatic Index Management

Module 7: Other Uses of Query Store

While Query Store is frequently marketed as a flight-recorder for your database that will help you identify performance issues, it can be used for so much more. In this module we’ll explore how you can leverage the data in Query Store to mitigate upgrade issues, find patterns in code, and potential problems based on plans. Topics covered include:

  • Upgrade testing
  • Cardinality Estimator testing
  • Mining Query Store data

Module 8: Visualizing Query Store Data

SQL Server’s Management Studio comes with multiple built-in reports for reviewing Query Store data, but sometimes those reports aren’t enough. While you can’t customize what’s in SSMS, you can create your own reports using other tools such as Power BI, R, and SQL Server Reporting Services (SSRS). In this module we’ll pull together our knowledge of the schema and data to create customized solutions that you (and your managers) can use to look at solution performance.


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


Questions?

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

Course Formats

Online deliveries: 3 consecutive half days
Available ONDEMAND now: Check out our recorded course library

Overview

The Query Store feature was introduced in SQL Server 2016 and it’s a game-changer for data professionals.  Have you ever…

  • …had that emergency where you’re informed “The database is slow! It was fine yesterday! Nothing changed!”?
  • …wished for a query’s baseline metrics so that you could quickly compare pre- and post-upgrade performance?
  • …flailed about querying DMV after DMV to figure out the *real* problem with performance?

If so, then this is the course where you learn how to stop reacting and start making Query Store work for you.

Query Store allows you to proactively monitor query performance and find those queries that are currently causing problems, or have the potential to cause problems. In addition, you can stabilize query performance use plan forcing, whether that’s something you do manually or automatically with Automatic Plan Correction. Beyond the basics, you’ll see demos that teach you how to interrogate the data to find common patterns in query performance using T-SQL and visualization techniques, and how to take advantage of the wait statistics information captured. After this class you will have the skills to find and leverage important information in Query Store to make query tuning easier.

This class is delivered live via online streaming and the recordings are available to purchase HERE.

Instructor: Erin Stellato

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 Erin’s SQLskills Classes

  • “Extremely pleased with the course. FAR exceeded my expectations.”
  • “Well worth the time and expense to attend. Would highly recommend this to others.”
  • “I though this was extremely worthwhile. I have been a DBA for a few years and this was a great refresher for a lot of things I don’t do very often but should.”
  • “Found class extremely helpful and plan to take the next one.”
  • “Great course – very informative – very great instructors – I am sure to be back!”
  • “I was familiar with all of the topics in this course, but barely understood most. I feel I will be able to begin to do all of the things I need to take simpler tasks from our DBAs to allow them to focus on the more advanced tasks. That was exactly my goal for taking this class.”
  • “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.”

Curriculum

Module 1: Query Store Fundamentals

In the first module we will create the foundation on which the rest of the class will build, as we cover requirements, configuration, and data captured. We’ll discuss methods traditionally used to capture query performance data, and how we find that same information within Query Store. Topics covered include:

  • What’s in Query Store (and what isn’t)
  • Query Store settings
  • How it works

Module 2: Understanding the Query Store Data

If you want to make decisions about query performance, it’s essential to understand the data used to make such decisions. We will look at the data in detail to understand what the data represents and how it compares to the data you’ve used historically. Topics covered include:

  • Understanding runtime statistics
  • Understanding wait statistics
  • The relationship between statistics and plans

Module 3: Workload Characteristics and Query Store Performance

Once data professionals realize the value of Query Store, a typical follow-up question is “How will it affect performance?” In this module we’ll look at how different workload types affect the data stored in Query Store, and the workload characteristics to monitor to understand how they affect Query Store performance. Topics covered include:

  • Typical workloads
  • How to identify what type of workload you have
  • Performance with Query Store enabled

Module 4: Finding Performance Issues

Query Store is designed to help you quickly identify “what happened”, but you need to know where to find that information and how to leverage the built-in reports to quickly diagnose poor-performing and regressed queries. The reports may not always provide what you need, or want, but you have the ability to write your own queries against the data to return the information you need. Topics covered include:

  • Using reports
  • Internal schema
  • Writing your own Query Store queries

Module 5: Forcing Plans

DBAs and developers have a long history of coming up with creative ways to stabilize query performance without refactoring code. Query Store provides another option with plan forcing, a very easy (but still temporary) solution to ensure a query consistently uses the same plan. But there are many details to be aware of with forcing, which we’ll review in depth in this module. Topics covered include:

  • When and how to force a plan
  • What causes forcing to fail
  • Monitoring and addressing failures

Module 6: Automatic Tuning

Microsoft continues to move forward the concept of the intelligent database, with Automatic Tuning forming one small part of that automation. Comprised of Automatic Plan Correction and Automatic Index Management, we’ll review both features to see how they work and how they rely on Query Store data. Topics covered:

  • Automatic Plan Correction
  • Automatic Index Management

Module 7: Other Uses of Query Store

While Query Store is frequently marketed as a flight-recorder for your database that will help you identify performance issues, it can be used for so much more. In this module we’ll explore how you can leverage the data in Query Store to mitigate upgrade issues, find patterns in code, and potential problems based on plans. Topics covered include:

  • Upgrade testing
  • Cardinality Estimator testing
  • Mining Query Store data

Module 8: Visualizing Query Store Data

SQL Server’s Management Studio comes with multiple built-in reports for reviewing Query Store data, but sometimes those reports aren’t enough. While you can’t customize what’s in SSMS, you can create your own reports using other tools such as Power BI, R, and SQL Server Reporting Services (SSRS). In this module we’ll pull together our knowledge of the schema and data to create customized solutions that you (and your managers) can use to look at solution performance.


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


Questions?

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

Course Formats

Online deliveries: 3 consecutive half days
Available ONDEMAND now: Check out our recorded course library

Overview

The Query Store feature was introduced in SQL Server 2016 and it’s a game-changer for data professionals.  Have you ever…

  • …had that emergency where you’re informed “The database is slow! It was fine yesterday! Nothing changed!”?
  • …wished for a query’s baseline metrics so that you could quickly compare pre- and post-upgrade performance?
  • …flailed about querying DMV after DMV to figure out the *real* problem with performance?

If so, then this is the course where you learn how to stop reacting and start making Query Store work for you.

Query Store allows you to proactively monitor query performance and find those queries that are currently causing problems, or have the potential to cause problems. In addition, you can stabilize query performance use plan forcing, whether that’s something you do manually or automatically with Automatic Plan Correction. Beyond the basics, you’ll see demos that teach you how to interrogate the data to find common patterns in query performance using T-SQL and visualization techniques, and how to take advantage of the wait statistics information captured. After this class you will have the skills to find and leverage important information in Query Store to make query tuning easier.

This class is delivered live via online streaming and the recordings are available to purchase HERE.

Instructor: Erin Stellato

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 Erin’s SQLskills Classes

  • “Extremely pleased with the course. FAR exceeded my expectations.”
  • “Well worth the time and expense to attend. Would highly recommend this to others.”
  • “I though this was extremely worthwhile. I have been a DBA for a few years and this was a great refresher for a lot of things I don’t do very often but should.”
  • “Found class extremely helpful and plan to take the next one.”
  • “Great course – very informative – very great instructors – I am sure to be back!”
  • “I was familiar with all of the topics in this course, but barely understood most. I feel I will be able to begin to do all of the things I need to take simpler tasks from our DBAs to allow them to focus on the more advanced tasks. That was exactly my goal for taking this class.”
  • “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.”

Curriculum

Module 1: Query Store Fundamentals

In the first module we will create the foundation on which the rest of the class will build, as we cover requirements, configuration, and data captured. We’ll discuss methods traditionally used to capture query performance data, and how we find that same information within Query Store. Topics covered include:

  • What’s in Query Store (and what isn’t)
  • Query Store settings
  • How it works

Module 2: Understanding the Query Store Data

If you want to make decisions about query performance, it’s essential to understand the data used to make such decisions. We will look at the data in detail to understand what the data represents and how it compares to the data you’ve used historically. Topics covered include:

  • Understanding runtime statistics
  • Understanding wait statistics
  • The relationship between statistics and plans

Module 3: Workload Characteristics and Query Store Performance

Once data professionals realize the value of Query Store, a typical follow-up question is “How will it affect performance?” In this module we’ll look at how different workload types affect the data stored in Query Store, and the workload characteristics to monitor to understand how they affect Query Store performance. Topics covered include:

  • Typical workloads
  • How to identify what type of workload you have
  • Performance with Query Store enabled

Module 4: Finding Performance Issues

Query Store is designed to help you quickly identify “what happened”, but you need to know where to find that information and how to leverage the built-in reports to quickly diagnose poor-performing and regressed queries. The reports may not always provide what you need, or want, but you have the ability to write your own queries against the data to return the information you need. Topics covered include:

  • Using reports
  • Internal schema
  • Writing your own Query Store queries

Module 5: Forcing Plans

DBAs and developers have a long history of coming up with creative ways to stabilize query performance without refactoring code. Query Store provides another option with plan forcing, a very easy (but still temporary) solution to ensure a query consistently uses the same plan. But there are many details to be aware of with forcing, which we’ll review in depth in this module. Topics covered include:

  • When and how to force a plan
  • What causes forcing to fail
  • Monitoring and addressing failures

Module 6: Automatic Tuning

Microsoft continues to move forward the concept of the intelligent database, with Automatic Tuning forming one small part of that automation. Comprised of Automatic Plan Correction and Automatic Index Management, we’ll review both features to see how they work and how they rely on Query Store data. Topics covered:

  • Automatic Plan Correction
  • Automatic Index Management

Module 7: Other Uses of Query Store

While Query Store is frequently marketed as a flight-recorder for your database that will help you identify performance issues, it can be used for so much more. In this module we’ll explore how you can leverage the data in Query Store to mitigate upgrade issues, find patterns in code, and potential problems based on plans. Topics covered include:

  • Upgrade testing
  • Cardinality Estimator testing
  • Mining Query Store data

Module 8: Visualizing Query Store Data

SQL Server’s Management Studio comes with multiple built-in reports for reviewing Query Store data, but sometimes those reports aren’t enough. While you can’t customize what’s in SSMS, you can create your own reports using other tools such as Power BI, R, and SQL Server Reporting Services (SSRS). In this module we’ll pull together our knowledge of the schema and data to create customized solutions that you (and your managers) can use to look at solution performance.


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


Questions?

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