Announcing a New Class: Fundamentals of Columnstore
Your report queries are too slow. Will columnstore indexes help?
You’ve tried throwing some hardware at it: your production SQL Server has 12 CPU cores or more, 128GB RAM, and SQL Server 2016 or newer. It’s still not enough to handle your growing data. It’s already up over 250GB, and they’re not letting you purge old data.
In one day, you’ll learn:
- How columnstore data is stored, and how that impacts your architecture choices
- How to do a hands-on self-assessment of your workloads, data, and server to figure out whether columnstore indexes make sense for you
- How to do a proof-of-concept implementation with initial creation and measuring how your queries are impacted
As this is a Fundamentals class, I need to be clear about what’s out of scope, too. We won’t be covering how to tune loads, tune query plans, design rowstore indexes atop columnstore ones, tune ongoing maintenance, feature cross-compatibility, compatibility levels, or behaviors of different SQL Server versions or editions.
Prerequisites – you need to be already thoroughly familiar with my free 90-minute class, How to Think Like the SQL Server Engine, and my 1-day Fundamentals of Index Tuning class. After you enroll, do the full prerequisites including chat setup.
Each student needs to bring their own SQL Server. You’re highly encouraged to follow along with the labs. Throughout the class, you’ll have homework where you need to complete a hands-on lab using SQL Server 2017 or 2019 and the Stack Overflow database. Read the instructions on how to set up your own lab SQL Server to follow along – a lab server is not included unless you buy my Live Class Season Pass PLUS Lab VM.
This is a live online class – but you have two buying choices:
- Instant Replay Recordings Only – you can start streaming the Instant Replay recordings as soon as the first live class finishes. You can watch for a year straight.
- Live Class + Instant Replay – not only do you get instant access to the most recent recordings, but you can also attend all of the live deliveries of this class for 12 months after you buy your ticket. Read the live class logistics page. Here are the upcoming dates scheduled so far:
- October 20, 2020 online 9:00-5:00 Eastern (click to see in your own time zone) – iCal
- November 17, 2020 online 9:00-5:00 Eastern (click to see in your own time zone) – iCal
- February 5, 2021 online 9:00-5:00 Eastern (click to see in your own time zone) – iCal
- May 10, 2021 online 9:00-5:00 Eastern (click to see in your own time zone) – iCal
Register now – and of course, Live Class Season Pass holders are welcome to drop in at any time, no registration required. Your pass gets you into any live online class I do during your subscription. See you in class!
4 Comments. Leave new
hi, I only see 1 buying option if I click on register. Don’t see the “Instant replay recording only”
There is saying “you can start streaming the Instant Replay recordings as soon as the first live class finishes.” It seems we should wait until October 20, 2020.
** Press Enter to quickly original post, please delete **
Hello Brent & Team,
Firstly, I am grateful for all your insightful blog posts and contributions to the community. They have been incredibly helpful. I have taken your “Fundamentals of Columnstore Indexes” class, which provided great insights into columnstore technology.
I am referencing a specific Microsoft document that discusses memory optimizations for columnstore compression in Azure Synapse, which can be found here: Memory Optimizations for Columnstore Compression. https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-memory-optimizations-for-columnstore-compression
I am working on optimizing rowgroup quality for columnstore indexes in an Azure Synapse dedicated SQL pool and comparing the behaviors with other platforms like SQL Server, SSAS, and PowerBI. My focus is on increasing the number of rows per rowgroup. It seems that the reason for low numbers might be hitting the maximum dictionary size of 16 MB. Based on the documentation, it appears that dictionaries are only utilized for string columns where the data type exceeds 32 bytes. I have a few questions regarding this:
1.0 – Could you confirm my understanding that strings of less than 32 bytes do not use dictionary compression, and I should only be focused on data types that represent strings >= 32 bytes?
1.1 – If dictionary compression is not happening, does that mean the fallback is just Run-Length Encoding (RLE)? Is this interpretation accurate?
2.0 – Given your knowledge of SQL Server’s columnstore indexes, is this behavior regarding the 32 bytes rule the applies to SQL Server’s columnstore indexes? I don’t recall this being in the videos and cant find anything online., would that suggest that dictionary compression happening on all char & nvarchar date types?
3.0 My general rule is always to find ways, whether it be a string (32 bytes or less) or any columns, to use a data type that adheres to the requirement that the “input and output data type must be one of the following and must fit within 64 bits.” This approach primarily aims to benefit from aggregated pushdown and batch mode, as discussed in your article “Key Lookups with Columnstore Indexes” https://www.brentozar.com/archive/2017/09/key-lookups-columnstore-indexes/.
Hopefully, I have not had to deal much with this product. One of the biggest problems I have encountered with Synapse Dedicated SQL Pools is that they do not have a query plan that looks like SQL Server or SQL Server with a column store index.
Thank you for considering my questions.
Kind regards,
Rif – https://g.dev/rif
Hi! These are great questions but they’re WAY outside of the scope of the fundamentals, and not something I cover in the existing training classes. I’m out on vacation with my family, but if you’d like to hire me for consulting help on my return, click Consulting at the top of the site. Thanks for understanding!