Practical SQL Server In-Memory OLTP Tables and Objects

A practical approach to understanding the benefits and requirements of implementing in-memory OLTP tables, indexes, natively compiled stored procedures, and UDFs. This course also treats support, monitoring, and troubleshooting IMOLTP.
Course info
Rating
(34)
Level
Intermediate
Updated
May 13, 2016
Duration
2h 50m
Table of contents
Description
Course info
Rating
(34)
Level
Intermediate
Updated
May 13, 2016
Duration
2h 50m
Description

In-memory OLTP objects have created some of the most drastic performance improvements of any SQL Server feature introduced to date. Further improvements from its initial "hekaton" release to the modern version of SQL Server makes this feature not only more robust, but puts this approach within reach of the every-day DBA and database developer. In this course, Practical SQL Server In-memory OLTP Tables and Objects, you will learn the foundational make-up of SQL Server in-memory objects and what you need to leverage these objects in your projects. You'll also learn to avoid pitfalls common to working with in-memory, as well as some of the myths surrounding in-memory objects, identifying where they do and do not offer a benefit. This practical approach focuses on real world usage but gives the user a moderately deep overview of internals as well. By the end of this course, you will understand what you need to implement in-memory objects and how they can benefit you.

About the author
About the author

Currently an IT leader in Denver Colorado's financial sector Russ has focused on database development, modelling, administration, and BI since 1997 across the Microsoft stack. Russ is a passionate trainer and SQL community volunteer presenting regularly at PASS SQL Saturday events and local user groups around the US.

More from the author
More courses by Russ Thomas
Section Introduction Transcripts
Section Introduction Transcripts

Reducing Latency with In-Memory Tables in New and Existing Work
Hey, welcome to module 3 of this In-Memory OLTP course. This module is entitled Reducing Latency with In-Memory Tables in New and Existing Work. I wanted a longer title, but Pluralsight said no. My name's Russ Thomas, and you can find me on Twitter @sqljudo. In this module we're going to look at a few test scenarios so that we can evaluate In-Memory OLTP against our use cases. We are looking for real world improvement. We'll start by implementing a memory-optimized file group. We'll do this for our test cases, but along the way we'll show you how to go about creating a file group, as well as a logical file for that group. With that done, we'll create some simple tables with memory optimization on, and that will allow us to implement our tests. Now, the concept is exactly the same whether you're building a test table or a real table. With those test tables built, we'll evaluate some simple test cases, and then armed with that knowledge we'll choose our first target use case to actually implement into production to see if we can reduce some of the problem areas that we're experiencing. In doing all these previous steps, we'll have already shown how to create in-memory tables from scratch, so we'll close out the module with showing how to migrate existing tables to memory-optimized tables, including some quick ways to go about setting up checklists if you have some work to do, and it's not just a simple conversion process. Alright, we've done plenty of talking, let's get to some demos.

Gaining Complete Memory Efficiency with Natively Compiled Objects
Welcome back to this Pluralsight course on In-Memory SQL Server Solutions. This is module 5, Gaining Complete Memory Efficiency With Natively Compiled Objects. My name is Russ Thomas, and I'm out on Twitter @sqljudo. The key to understanding the benefit of natively compiled tables, procedures, and now in 2016 even user-defined functions is to first establish a good understanding of what native compilation is, both from the standpoint of tables, as well as code executed against those tables. Next understanding how T-SQL queries are optimized and compiled against legacy tables gives you something to compare and contrast. This module will begin with a quick introduction to native compilation in general. We will then review query compilation and query optimization, finally bringing the two together, we will then use some demos and real life use cases to see the steps that are taken to use that same T-SQL syntax against memory-optimized tables versus legacy tables, and what native compilation can offer to benefit that process. We'll close with a review of some of the compatibility and migration tools you may have to navigate when moving legacy queries and stored procedures to natively compiled stored procedures. All of the demo portions of this module will be again against our Abacos use cases. To get started, let's look first at native compilation in general.

Analyzing and Monitoring Your In-Memory Solution
Hi, welcome to the last module in this In-Memory course. This is module 6, Analyzing and Monitoring Your In-Memory Solution. My name is Russ Thomas, out on Twitter @sqljudo. In this module we're going to first quantify everything we've done, and get an overall picture of the performance improvements that we've been able to gain out of migrating some of our tables, indexes, and code to In-Memory OLTP solutions. We'll discuss which ones faired really well, and the ones that maybe not so well, we'll discuss why. We'll consider both performance, as well as the actual memory that these approaches demand. Hopefully you've picked up some excitement for the possibilities of memory optimization, but the reality is it takes more than just know how and technical excitement, you've also got to have the physical memory resources available that it demands. Now your solutions will vary, but hopefully you can get a feel for what our use cases demanded to help establish some ideas for what you'll need in your own projects. Once we have a good idea of what we've been able to do with this technology and what it required from a resource perspective, we'll spend the rest of this module talking about what is required for longterm support, monitoring and troubleshoot, this type of solution, beyond just proving how much faster it is. We'll look at a couple extended events, a couple of PerfMon metrics that are useful, some of the common error messages that you might run into, and probably most useful, the major DMVs and system tables that you'll want to make critical components in your monitoring solution when you're using In-Memory OLTP. Alright, let's wrap this course up.