Today we can already say with certainty that the most striking innovation in the MS SQL 2014 is the In-Memory OLTP. In-Memory OLTP (project Hekaton) functionality was designed to expedite the processing of typical OLTP-operation in SQL Server. As you know, server load can be divided into two classes: data collection and analysis of what is collected. The first class of problems called OLTP (On-Line Transactional Processing). It is characterized by a short updating transaction affecting a relatively small number of tables in the database. The second class of problems called OLAP (On-Line Analytical Processing) and is characterized by massive prolonged reading, covers a large number of tables and collecting of them, as a rule, the maximum entries. In-Memory OLTP – fully transactional technology and supports failover.
Let`s go to some practical examples and see how it works.
Firstly, we should create In-Memory database InMemoryDataBase and file it under the group In-Memory OLTP – InMemoryExample_FileGroup as follows:
CREATE DATABASE InMemoryDataBase
CONTAINMENT = NONE
ON PRIMARY
(NAME = N'InMemoryDataBase', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemoryDataBase.mdf',
SIZE = 1GB, MAXSIZE = UNLIMITED, FILEGROWTH = 2GB),
FILEGROUP [InMemoryExample_FileGroup] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT
(NAME = N'InMemoryExample_Container', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\INMEMORYDB_CONTAINER', MAXSIZE = UNLIMITED)
LOG ON
(NAME = N'InMemoryDataBase_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\InMemoryDataBase_Log.ldf',
SIZE = 1GB, MAXSIZE = 2048GB, FILEGROWTH = 1GB)
GO
As you can see from script above, a new database includes the CONTAINS MEMORY_OPTIMIZED_DATA option to store one or more containers for data files or delta files.
Then we will create an In-Memory table and stored procedure to insert some into that In-Memory table:
USE [InMemoryDataBase]
GO
CREATE TABLE dbo.Users_InMemory (
UserId INT IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
TypeId INT NOT NULL INDEX ix_TypeId NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
Name nvarchar(255) NOT NULL,
CreatedDate DATETIME NOT NULL DEFAULT GETDATE()
) WITH (MEMORY_OPTIMIZED=ON)
GO
The last option (WITH (MEMORY_OPTIMIZED = ON)) just means that the table will be placed in memory. Each MEMORY_OPTIMIZED table must have at least one index. Overall number should not exceed 8. Included in the index of the field should not be nullable. For in the index fields n (var) char collation should be used BIN2. Clustered index, by definition, does not exist. The structure of the classic B-Tree too. For tables in memory indexes are HASH (best suited for point search) and the RANGE (as the name suggests, is better suited for range-scans).
Then we will create the On-Disk table Users_OnDisk as follows:
USE [InMemoryDataBase]
GO
CREATE TABLE dbo.Users_OnDisk (
UserId INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
TypeId INT NOT NULL INDEX ix_TypeId NONCLUSTERED,
Name nvarchar(255) NOT NULL,
CreatedDate DATETIME NOT NULL DEFAULT GETDATE()
)
GO
Next we will create the In-Memory OLTP stored procedure spUsersAddInMemory to insert data into the In-Memory table Users_InMemory:
CREATE PROCEDURE dbo.spUsersAddInMemory
@Count int = 100000
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE=N'us_english')
DECLARE @i int = 1
WHILE @i < @Count BEGIN INSERT INTO dbo.Users_InMemory(TypeId, Name) SELECT @i, 'User_' + CAST(@i AS varchar) SET @i = @i + 1 END END GO
In-Memory stored procedure has new option NATIVE_COMPILATION, which indicates that the procedure is natively compiled. Stored procedure must be bound to the schema of the objects it references by using SCHEMABINDING clause, and with EXECUTE AS OWNER to set the default execution context. NATIVE_COMPILATION, SCHEMABINDING and EXECUTE AS can be specified in any order. Finally, stored procedure must set the atomic block: the isolation level - SNAPSHOT and the language - us_english.
Then we create the disk based stored procedure named spUsersAddOnDisk as follows:
CREATE PROCEDURE dbo.spUsersAddOnDisk
@Count int = 100000
AS
BEGIN
DECLARE @i int = 1
WHILE @i < @Count BEGIN INSERT INTO dbo.Users_OnDisk(TypeId, Name) SELECT @i, 'User_' + CAST(@i AS varchar) SET @i = @i + 1 END END GO
Now we are ready to execute both stored procedures and compare performance. Firstly, we are executing stored procedure for InMemory table:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXECUTE [dbo].[spUsersAddInMemory]
GO
Result of execution:
SQL Server Execution Times:
CPU time = 657 ms, elapsed time = 2478 ms.
As you can see In-Memory stored procedure spUsersAddInMemory puts 100000 rows into the In-Memory table Users_InMemory in 2 seconds.
Then we can execute stored procedure for OnDisk table:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
EXECUTE [dbo].[spUsersAddOnDisk]
GO
Result of execution:
SQL Server Execution Times:
CPU time = 20766 ms, elapsed time = 884586 ms.
Table based stored procedure spUsersAddOnDisk put 100000 rows into the disk based table Users_OnDisk for 14 minutes and 44 seconds.
Based on results of execution of the insert statement above, we can say that the insert in In-Memory table is much faster than a classic table.
