<div style="margin:0 0 8pt;">WHAT</div>In-memory OLTP is a memory-optimized database engine integrated with SQL Server engine. It optimized OLTP operations. It massively improve performance and scalability of databases and by that applications will be speed up.
<div style="margin:0 0 8pt;">Its main mechanism is that it allows you to declare a table to be stored in main memory which is called memory optimized table. That reduce OLTP workload and provide faster accessibility. Different data and index structure are introduced in memory optimized tables and no data pages, no locking or latching, index pages or buffer pool are used. SQL Server was designed to store data in the disk based tables for persistence and bring data in memory when needed for serving query requests. In memory optimized tables data are already stored in the memory.</div><div style="margin:0 0 8pt;">WHY </div><ol><li>Data insertion rate is memory-optimized tables is high from different connection. <li>All the data for memory-optimized tables is in memory no buffer pool or cache is used. <li>Durable in-memory tables are fully ACID-compliant so no data loss in committed records. <li>It uses latch-free data structures and optimistic, multi-version concurrency control. <li>No blocking is happened in concurrent transactions which enabling fully utilization of CPU. <li>You will get major performance using natively compiled stored procedures than interpreted stored procedures at the time of accessing a memory-optimized table. <li>Code execution time become lower by reducing latency and improved throughput. <li>You use new memory-optimized tables with old disk-based tables together in same database. <li>Logical locks are eliminated through Optimistic concurrency control. <li>It has extremely high session concurrency for OLTP type of transactions driven from a highly scaled-out middle-tier.</li></li></li></li></li></li></li></li></li></li></ol>
<div style="margin:0 0 8pt;">How</div>
<div style="margin:0 0 8pt;">Step 1: You have a prerequisite for creating memory optimized table:</div>
<div style="margin:0 0 8pt;"> When create a new database:</div>
<div style="margin:0 0 8pt;"> Create database with a filestream filegroup </div>
<div style="margin:0 0 8pt;"> (Using CONTAINS MEMORY_OPTIMIZED_DATA) </div>
<div style="margin:0 0 8pt;"> When you work on existing database:</div> Alter an existing database to add a filestream filegroup
Step 2: We are going to create a traditional Database so that we may differentiate :
<div class="reCodeBlock" style="-ms-overflow-y:auto;border:1px solid rgb(127,157,185);"><div style="background-color:white;">USE master </div><div style="background-color:#f8f8f8;">GO </div><div style="background-color:white;">CREATE DATABASE DBInMemoryOLTP1 </div><div style="background-color:#f8f8f8;">ON</div><div style="background-color:white;">PRIMARY( </div><div style="background-color:#f8f8f8;">NAME = [DBInMemoryOLTP_data], </div><div style="background-color:white;">FILENAME = 'c:\database\DBInMemoryOLTP_data.mdf', SIZE = 1000MB </div><div style="background-color:#f8f8f8;">) </div><div style="background-color:white;">LOG ON ( </div><div style="background-color:#f8f8f8;">NAME = [DBInMemoryOLTP_log], </div><div style="background-color:white;">FILENAME = 'C:\database\DBInMemoryOLTP_log.ldf', SIZE = 50MB </div><div style="background-color:#f8f8f8;">)</div></div>
<div style="margin:0 0 8pt;">Step 3: Add file Group having memory-optimized tables :
</div>
<div class="reCodeBlock" style="-ms-overflow-y:auto;border:1px solid rgb(127,157,185);"><div style="background-color:white;">Use DBInMemoryOLTP </div><div style="background-color:#f8f8f8;">Go </div><div style="background-color:white;">ALTER DATABASE DBInMemoryOLTP ADD FILEGROUP [DBInMemoryOLTP_data] </div><div style="background-color:#f8f8f8;">CONTAINS MEMORY_OPTIMIZED_DATA </div><div style="background-color:white;">GO </div><div style="background-color:#f8f8f8;">ALTER DATABASE DBInMemoryOLTP ADD FILE </div><div style="background-color:white;">( </div><div style="background-color:#f8f8f8;">NAME = [DBInMemoryOLTP_FG1], </div><div style="background-color:white;">FILENAME = 'c:\database\DBInMemoryOLTP_FG1'</div><div style="background-color:#f8f8f8;">) TO FILEGROUP [DBInMemoryOLTP_data] </div><div style="background-color:white;">GO </div><div style="background-color:#f8f8f8;">ALTER DATABASE DBInMemoryOLTP ADD FILE </div><div style="background-color:white;">( </div><div style="background-color:#f8f8f8;">NAME = [DBInMemoryOLTP_FG2], </div><div style="background-color:white;">FILENAME = 'c:\database\DBInMemoryOLTP_FG2'</div><div style="background-color:#f8f8f8;">) TO FILEGROUP [DBInMemoryOLTP_data] </div><div style="background-color:white;">GO</div></div>
<div style="margin:0 0 8pt;">Step 4: Now we are going to create a memory-optimized table:</div>
<div class="reCodeBlock" style="-ms-overflow-y:auto;border:1px solid rgb(127,157,185);"><div style="background-color:white;">CREATE TABLE [techforum_member_list]( </div><div style="background-color:#f8f8f8;">[TfmID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 500000), </div><div style="background-color:white;">[Name] NVARCHAR(50) COLLATE Latin1_General_100_BIN2 NOT NULL INDEX [IName] HASH WITH (BUCKET_COUNT = 500000), </div><div style="background-color:#f8f8f8;">[JoiningDate] DATETIME NULL</div><div style="background-color:white;">) </div><div style="background-color:#f8f8f8;">WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); </div></div>
<div style="margin:0 0 8pt;">Step 5: To understand the advantage we are going to input sample data into the newly created table. Make Select query to feel the new experience :</div>
<div class="reCodeBlock" style="-ms-overflow-y:auto;border:1px solid rgb(127,157,185);"><div style="background-color:white;">Declare @i as bigint=1 </div><div style="background-color:#f8f8f8;"> </div><div style="background-color:white;">While ( @i < 500000) </div><div style="background-color:#f8f8f8;"> Begin</div><div style="background-color:white;"> Insert into techforum_member_list values (@i,'techforum'+cast(@i as nvarchar(50)),GETDATE() ) </div><div style="background-color:#f8f8f8;"> Set @i+=1 </div><div style="background-color:white;"> End </div><div style="background-color:#f8f8f8;"> </div><div style="background-color:white;"> Select * from techforum_member_list</div></div>
<div style="margin:0 0 8pt;">You will be wonder that all data will be retrieved from database within 1 sec.</div>
<div class="separator" style="clear:both;text-align:center;">
</div>