Optimizing OLTP and Data Warehousing with SQL Server 2014

This course focuses on the database developers and architects engaged in the increasingly difficult task of achieving the best performance of database applications. SQL Server 2014 addresses these needs, optimizing for both OLTP and Data Warehousing workloads using In-Memory OLTP and Clustered Columnstore Indexes, allowing you to realize the fullest potential of your database systems and underlying hardware.
Course info
Rating
(155)
Level
Intermediate
Updated
Jan 15, 2014
Duration
2h 19m
Table of contents
Description
Course info
Rating
(155)
Level
Intermediate
Updated
Jan 15, 2014
Duration
2h 19m
Description

This course focuses on SQL Server 2014 and two of the latest additions to the database engine. We first look at how the shift in technology trends established the need for a different breed of technologies. Columnar, or Column-Oriented, databases have become increasingly popular in data warehousing applications because they allow for better compression and multi-fold performance improvements, and we'll see how SQL Server 2014 implements this with clustered columnstore indexes. In addition, as technology makes a bigger shift in hardware to multi-core CPU architectures and large amounts of memory are even cheaper and more attainable, it's important for software to adjust and utilize these changes. We'll see how In-Memory OLTP addresses these trends and more, letting you create tables in memory, eliminating locks and implementing optimistic multi-version concurrency. This course discusses in depth In-Memory OLTP (memory optimized tables), previously known as Hekaton. We'll cover creating memory-optimized tables, loading data, durability and the affect that has on the transaction log. We'll also look at indexing in detail, explaining the new index types created specifically to support In-Memory OLTP and how and when to apply each type based on your use case and we'll discuss how In-Memory OLTP and Clustered Columnstore indexes can fit a variety of applications while comparing them with each other and traditional disk-based tables and indexes.

About the author
About the author

Ahmad is a Data Architect specializing in the implementation of high-performance data warehouses and BI systems and enjoys speaking at various user groups and conferences.

More from the author
SQL on Hadoop - Analyzing Big Data with Hive
Intermediate
4h 16m
8 Oct 2013
Section Introduction Transcripts
Section Introduction Transcripts

In-Memory OLTP
Hi and welcome to this course on optimizing OLTP and Data Warehousing databases with SQL Server 2014. My name is Ahmad Alkilani and I'll be guiding you throughout this course as we look at the latest in SQL Server 2014 that can help you as a database developer or architect optimize your current SQL Server environments to achieve optimal performance. We're going to be looking at two of the latest features of SQL Server 2014. In particular, In Memory OLTP and Clustered Column Store Indexes. This is the first module of the course. In this module, we're going to briefly look at the technology drivers that led to the inception of these new technologies and what kind of problems the features we're going to discuss try to solve for. And then we'll dive right and look at what In Memory OLTP is all about and discuss its overall architecture and how that targets the various technology drivers. Finally, we'll walk through setting up the demo environment to get you up and running for the remainder of the course. And we'll run our first hands-on demo, creating memory optimized tables.

Data & Indexes for Memory Optimized Tables
Hi. This is Ahmad Alkilani, and welcome to the second module of this course. We'll kick off this module by loading data into the tables that we've created in the first module, and then we'll take a deep dive into the different types of indexes that you can create on memory-optimized tables, and we'll look at the various design principles, and how the data structure for indexes on memory-optimized tables is different than that for disk-based tables, and then we'll spend a lot of time in demos discussing how and when to use each type of index, and look at the considerations and constraints for each one of them. So let's get started, and we're actually going to kick off this module with a demo.

Advanced In-Memory OLTP
Hi, this is Ahmad Alkilani and welcome to this module. In this module, we're going to discuss some advanced topics for in-memory OLTP. So let's get started and take a look at the outline. So the first topic we're going to discuss is natively compiled stored procedures. We're going to take a look at what natively compiled stored procedures really are and how to create and use them. And then we're going to talk about concurrency control, specifically about the differences in transactional isolation levels between disk-based tables and in-memory OLTP. So let's get started.

Clustered Columnstore Indexes
Hi! This is Ahmad Alkilani from Pluralsight. And welcome to this module on clustered columnstore indexes. In the previous three modules we discussed In-Memory OLTP and how it applies primarily to OLTP type workloads because of its strength in handling concurrent transactions by eliminating a lock manager in addition to all of the benefits of being natively built for In-Memory storage. In this module we're going to look at clustered columnstore indexes, which is a technology aimed at data warehousing type workloads, and we'll see why as we walk through the use cases and demos. We'll start off by introducing columnstore indexes and the use cases for them, and why something like In-Memory OLTP wouldn't be ideal. Then we'll discuss clustered columnstore indexes specifically and how clustered columnstore indexes are different than the nonclustered columnstore index that was introduced in SQL Server 2012. Then we'll take a deeper look at the architecture behind clustered columnstore indexes, and compare that to normal row store indexes so you have a better understanding of what happens behind the scenes. We'll finally end with a few demos showcasing the power of clustered columnstore indexes, and we'll even run a few comparisons between all three types: row store, in-memory, and clustered columnstore, or CCI for short.