SQL Server 2012: Nonclustered Columnstore Indexes

Learn how nonclustered columnstore indexes in SQL Server 2012 can introduce incredible performance benefits when querying very large data sets in relational data warehouses, applicable to developers and DBAs responsible for application data tiers, from SQL Server 2012 onwards
Course info
Rating
(206)
Level
Intermediate
Updated
May 8, 2013
Duration
1h 16m
Table of contents
Course Introduction
Nonclustered Columnstore Index Fundamentals
Leveraging Batch-Execution Mode
Benefiting from Segment Elimination
Managing Data Modifications
Deployment Planning
Description
Course info
Rating
(206)
Level
Intermediate
Updated
May 8, 2013
Duration
1h 16m
Description

If you need to develop a solution to meet your business intelligence needs but don't have the in-house SQL Server Analysis Services skills to make it happen, don't rule out a SQL Server database engine solution just yet. SQL Server 2012 nonclustered columnstore indexes provide a compelling new option for extremely fast querying of very large data sets found in relational data warehouses. This course will cover the benefits of this new functionality and will also clearly articulate the current limitations and planning considerations along the way. By the end of this course, you'll be equipped with enough information to help decide if nonclustered columnstore indexes will be a viable choice for your application data tier. This course is perfect for developers, DBAs, and anyone responsible for SQL Server performance. The information in the course applies to SQL Server 2012 onwards.

About the author
About the author

Joe Sack is a Principal Program Manager in the SQL Server and Azure SQL Database product team at Microsoft, with a focus on query processing. With over 19 years of experience in the industry, Joe is an author and speaker, specializing in performance tuning and optimization.

More from the author
More courses by Joe Sack
Section Introduction Transcripts
Section Introduction Transcripts

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.