SQL Server 2012: Nonclustered Columnstore Indexes

Course Introduction
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server 2012 Non-clustered Columnstore Indexes course and you're watching the Introduction where I'll briefly describe what to expect over the modules that make up this course. Microsoft introduced the non-clustered columnstore index feature in SQL Server 2012 as a way to provide significant performance gains for queries against relational data warehouses. In this course, I'll cover the advantages of non-clustered columnstore indexes over traditional role-based indexes and I'll articulate some of the known limitations and planning considerations. Once you finish this course, you should have enough information to help you successfully apply this feature to your relational data warehouse.

Nonclustered Columnstore Index Fundamentals
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server 2012 Non-clustered Columnstore Indexes course, and you're watching module 2 where we'll go over the fundamentals of this new feature. Non-clustered columnstore indexes were introduced in SQL Server 2012 and they can be used to significantly improve the performance of workloads against very large relational data warehouses, for example, star or snowflake schemas with hundreds of, or even billions of rows in the associated fact table. The actual performance gains, which you'll see a demonstration of in this module, are achieved through a combination of different aspects including a new column-based storage type, compression of the column storage, a new execution mode, and the ability to bypass a significant amount of IO based on the predicates in your query. So in this module, we'll cover the core concepts behind this new feature allowing you to decide if this will meet your overall data warehouse objectives.

Leveraging Batch-Execution Mode
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server 2012 Non-clustered Columnstore Indexes course, and you're watching module 3 where we'll discuss how to leverage batch execution mode. Batch execution mode is one of the key components that you should look for in queries that access tables covered by columnstore indexes. And usually your workloads should automatically leverage batch execution mode where it can, but as of SQL Server 2012, there are some known batch mode inhibitors. In this module, we'll focus on batch execution mode and teach you how to check for it in an execution plan and also discuss some of the inhibitors and the known workarounds.

Benefiting from Segment Elimination
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server 2012 Non-clustered Columnstore Indexes course, and you're watching module 4 where we'll discuss how to benefit from segment elimination. A segment or row group represents a consecutive group of rows for a columnstore index and it's the unit of transfer for queries that reference non-clustered columnstore indexes. Metadata is stored for each segment and that can assist the query optimizer by matching predicates to specific segments and avoiding access entirely to those segments where the min and max data ranges wouldn't qualify. In this module, we'll cover how segment elimination can be leveraged to further reduce I/O and overall memory usage.

Managing Data Modifications
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server 2012 Non-clustered Columnstore Indexes course and you're watching module 5 where we'll discuss how to manage data modifications. In SQL Server 2012, once you've added a non-clustered columnstore index to a table, specific data modifications are no longer allowed. This can be a nontrivial limitation, but there're some techniques that can help you still keep data up-to-date while still benefiting from the performance gains that a columnstore index can provide. In this module, I'll cover what data modification options are available to you.

Deployment Planning
Hi, this is Joe Sack from SQLskills. com and I'm recording this course for Pluralsight. This is the SQL Server 2012 Non-clustered Columnstore Indexes course and you're watching module 6 where we'll discuss how to successfully plan the deployment of columnstore indexes in your relational data warehouse. Creating non-clustered columnstore indexes is relatively straightforward, but there are several areas that you still need to plan for in order to make sure that your deployment meets the intended objectives. In this module, I'll walk through the various deployment planning considerations and towards the end of this module I'll also cover a few of the improvements that are planned for the next version of SQL Server.