SQL Server 2014 introduced many features to improve performance OLTP transactions, In-Memory OLTP is one of the most exciting feature in SQL Server 2014 which allows to move individual tables to special in-memory structures to improve query perfomance.
What is In Memory OLTP Engine of SQL Server 2014 ?
This is better than other in-memory solutions that require the entire database to be placed in memory.
You can get more performance by converting existing stored procedures into in-memory procedures, too. Here memory optimized table data is stored in memory, Now everytime user executes query will return data faster using buffer pool or cache due to In-Memory OLTP Engine. Not like normal table where pages needs to brought into Buffer pool or Cache and then providing result to user.
The In-Memory OLTP engine is designed for high concurrency and uses a new optimistic concurrency control mechanism to eliminate locking delays. Microsoft states that customers can expect performance to be up to 20
times better than with SQL Server 2012.
To help you evaluate how the In-Memory OLTP engine will improve database performance, Microsoft includes the new Analysis, Migrate, and Report (AMR) tool. Like its name suggests, the AMR tool analyzes your database and helps you to identify the tables and stored procedures that would benefit from moving them into memory.
How to use In-Memory OLTP Feature for your database in SQL Server 2014.
1. Connect to SQL Server 2014 Instance using SQL Server Management Studio, Once you connected to SQL Server 2014 instance, Expand the Databases node.
2. Here select your database, right-click on it and Click on Properties.
3. For In-Memory OLTP feature, Now you have to create separate Filegroup under MEMORY OPTIMIZED DATA option, to do so select File groups option from Database Properties page.
Here at the bottom, you will see one addition section named as MEMORY OPTIMIZED DATA in SQL Server 2014, click on Add file group and then enter the values for the file group.
4. Now to add file to this newly created file group for MEMORY OPTIMIZED DATA, Go to Files option and click on add under Database Files option. Enter the file name and file type as FILESTREAM Data.
5. Note this memory option is only available on databases that have a Memory-Optimized file group.
So either you create new database on SQL server 2014 or Alter the existing database to use Filestream filegroup, both options will work for you.
Run the below query using SQL Server Management Studio to create new database using Memory optimized file group.
CREATE DATABASE InMemoryDB
NAME = [InMemoryDB_data],
FILENAME = ‘D:\data\InMemoryDB_data.mdf’, SIZE = 1024MB
FILEGROUP [InMemoryDB_MOTdata] CONTAINS MEMORY_OPTIMIZED_DATA
NAME = [InMemoryDB_data_Folder],
FILENAME = ‘D:\data\InMemoryDB_data_
LOG ON (
NAME = [InMemoryDB_log],
FILENAME = ‘D:\log\InMemoryDB_log.ldf’, SIZE = 300MB
Run the below query using SQL Server Management Studio to alter existing database so that it uses newly created Memory optimized file group.
ALTER DATABASE InMemoryDB
ADD FILEGROUP [InMemoryDB_MOTdata] CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE InMemoryDB
NAME = [InMemoryDBDB_folder],
FILENAME = ‘c:\data\InMemoryDBDB_folder’
) TO FILEGROUP [InMemoryDBDB_MOTdata]
Note : Only one MEMORY_OPTIMIZED_DATA filegroup is allowed per database.
To learn more about how to create Memory optimized table for In Memory OLTP Engine, Follow this post : SQL Server 2014 – Create Memory Optimized table In Memory OLTP
To know more about limition of Memory optimized tables : SQL Server 2014 -Limition of Memory optimized tables