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

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?

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?

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

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.

Resources

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