SQL Server In-Memory OLTP (MS Docs article) is a powerful engine integrated into the SQL Server Engine (version 2014 and later), optimized for Online Transaction Processing (OLTP). This article helps you getting started with SQL Server In-Memory OLTP and it is the first part in a series of articles focusing on SQL Server In-Memory OLTP.
An Introduction to SQL Server In-Memory OLTP
In-Memory OLTP (codenamed “Hekaton”) is a powerful engine which is fully integrated into SQL Server’s Database Engine and introduces new data structures for optimizing the performance of OLTP workloads. So, you can have memory-optimized tables, natively-compiled stored procedures, memory-optimized table variables, etc.
In-Memory OLTP offers a significant performance boost when it comes to processing large amounts of information, especially in data environments with high levels of concurrency.
In this series of articles, we will see in plain words, how we can take advantage of this technology and start using it for boosting performance of heavy workloads.
In this first article, we are going to see how easy is to create an In-Memory OLTP-enabled database and memory-optimized tables.
Learn more about In-Memory OLTP in SQL Server – Enroll to the Course!
We recommend enrolling to our online course titled “Boost SQL Server Database Performance with In-Memory OLTP” and learn how to harness the power of SQL Server’s In-Memory Optimization and boost the performance of your data processes!
In this course, you will learn all about SQL Server’s In-Memory Database Processing Engine also known as In-Memory OLTP. To this end, you will learn how to enable In-Memory OLTP in SQL Server, what memory-optimized tables and natively-compiled stored procedures are, as well as, how to boost the performance of your data processes using this powerful SQL Server feature.
How to Create a SQL Server In-Memory OLTP-Enabled Database
OK, enough talking, let’s see some code! The below T-SQL script creates a new In-Memory OLTP-enabled database.
--create database CREATE DATABASE InMemOLTPDB; GO --use the database USE InMemOLTPDB; GO --add memory-optimized file group to the database ALTER DATABASE InMemOLTPDB ADD FILEGROUP InMemOLTPDB_mofg CONTAINS MEMORY_OPTIMIZED_DATA; --add file for memory-optimized objects --you can set any path you like - in this example: c:memoptdata --if the directory does not exist you will need to create it ALTER DATABASE InMemOLTPDB ADD FILE (name='InMemOLTPDB_mofg1', filename='C:memoptdataInMemOLTPDB_mofg1') TO FILEGROUP InMemOLTPDB_mofg; --set the isolation level for memory-optimized tables to SNAPSHOT ALTER DATABASE InMemOLTPDB SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON; GO
How to Create Memory-Optimized Tables in SQL Server
The next step, is to create memory-optimized tables.
When creating memory-optimized tables you have two options:
- Option 1: Create a durable memory-optimized table which means that in the case of server crash or failover the data will be available as they will be recovered from transaction logs.
- Option 2: Create a non-durable table which means that in the case of a server crash or failover the data in the table will be lost.
The below script creates a sample durable memory-optimized table:
--Memory-Optimized Table: Durable CREATE TABLE [dbo].[Person_Durable] ( ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000), FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Remarks VARCHAR(50) NOT NULL, )WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); GO
The below script creates a sample non-durable memory-optimized table:
--Memory-Optimized Table: Non-Durable CREATE TABLE [dbo].[Person_Non_Durable] ( ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000), FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, Remarks VARCHAR(50) NOT NULL, )WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY); GO
Stay tuned as in the next article we are going to run some simple experiments with In-Memory OLTP and get an indication on the performance benefits.
Video: What are Memory-Optimized Tables in SQL Server?
Our Online Course Video Preview: Boost SQL Server Database Performance with In Memory OLTP
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to SQL Server Machine Learning Services
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
- Introducing In-Memory Optimization in SQL Server
- Frequently Asked Questions About SQL Server In-Memory OLTP
- Boost SQL Server Database Performance with In-Memory OLTP (Course Preview)
- Introducing In-Memory OLTP Simulator
- In-Memory Optimization in SQL Server: Will my Workload Execute Faster?
- In-Memory OLTP Simulator – A Tool that helps you benchmark SQL Server’s Memory-Optimized Tables against your own workload
- Simple Experiment with SQL Server In-Memory OLTP is 79 Times Faster
Check our other related SQL Server Performance articles.
Subscribe to our newsletter and stay up to date!
Check In-Memory OLTP Simulator: Our special tool for easily benchmarking memory-optimized tables in SQL Server!
Check out our latest software releases!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior Database and Software Architect, Certified Database, Cloud and AI professional, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Moreover, Artemakis teaches on Udemy, you can check his courses here.