Share via

In-Memory OLTP Videos: What it is and When/How to use it

  • 10/03/2016

In-Memory OLTP is the premier technology for optimizing the performance of transaction processing in SQL Server. Last week at Microsoft Ignite 2016 we presented two session about the In-Memory OLTP technology in SQL Server and Azure SQL Database. For those of you who did not attend the conference or did not make it to the session, here is a brief recap with links to the videos for the sessions. At the bottom of this post you will find links to the demos used in the sessions, as well as further resources for In-Memory OLTP.

What is In-Memory OLTP?

Session title: Review In-Memory OLTP in SQL Server 2016 and Azure SQL Database

Link to the video: https://youtu.be/PuZ--v4c6HI

Recap of the session:

We explain why Microsoft decided to build the In-Memory OLTP feature. We go on to discuss the value proposition of In-Memory OLTP, as well as the key aspects of the technology that result in such great performance optimization of transactional workloads:

  • New data structures and data access methods built around the assumption that the active working set resides in memory
  • Lock- and latch-free implementation that provides high scalability
  • Native compilation of T-SQL modules for more efficient transaction processing

The demo (starting 34:22) illustrates the potential performance optimization you can achieve for transaction processing workloads, as well as the tools available in SSMS to get started with In-Memory OLTP in an existing application. We go on to review indexes and index recommendations for memory-optimized tables (starting 50:18). Finally, we review all the new features for In-Memory OLTP in SQL Server 2016 as well as Azure SQL Database (staring 1:03:11), to make it easier to adopt the technology, and manage applications using the technology.

When and How to use In-Memory OLTP?

Session title: Explore In-Memory OLTP architectures and customer case studies

Link to the video: https://youtu.be/h111hyt5Ndk

Recap of the session:

This session addresses the when and how to use In-Memory OLTP from two different angles:
a) listing characteristics that make a workload suitable and those that are not so suitable for In-Memory OLTP
b) reviewing common application patterns and actual customer uses of In-Memory OLTP

We start the session with a brief recap of In-Memory OLTP, followed by:

  • 11:07 - discussion of durability options in SQL Server and impact on performance
  • 17:44  - demo illustrating performance with different levels of durability
  • 25:10 - when to use In-Memory OLTP - app characteristics indicating In-Memory OLTP may or may not be suitable
  • 34:52 - scenarios and case studies - common scenarios for using In-Memory OLTP with some example customer case studies and architecture diagrams

The scenarios we review are:

  • High throughput OLTP with low latency (37:08)
  • Shock-absorber for concurrent data load (42:42)
  • Internet of Things (IoT) data ingestion and analytics (49:22) with a demo (55:00) illustrating the use of memory-optimized temporal tables to both support ingesting and analyzing high volumes of IoT data, and also manage the memory footprint through automatic offload to disk of historical data.
  • Session state and caching (1:01:52)
  • Tempdb replacement (1:06:26), showing the benefits of replacing traditional table variables and temp table with memory-optimized table variables and SCHEMA_ONLY tables
  • ETL - staging tables for data load and transformation (1:11:40)

Resources

In-Memory OLTP Overview and Usage Scenarios

Demos used in the session

  • In-Memory OLTP perf demo: this client application is used in both sessions, first to illustrate the potential perf benefits of In-Memory OLTP, and then to show the perf implications of using various durability settings. The script that comes with the demo uses a standard NONCLUSTERED index for reasons of convenience - to show the highest possible perf numbers, use a NONCLUSTERED HASH index instead, with a BUCKET_COUNT of about 10,000,000.
  • Memory-optimized table variables and temp tables: this blog post includes all demo scripts and instructions used in the session. In addition, it has instructions on how to start memory-optimizing table variables and temp tables in your applications.
  • IoT SmartGrid sample: this sample illustrates the use of SQL Server temporal memory-optimized tables to handle the load of IoT devices (in this case smart meters) being ingesting into the database for reporting and analytics purposes

Samples

Documentation

Migrating apps

 

Find me on Twitter: @jdebruijn

Download and try some of the demos yourself, and let me know what you think!