Creating and using memory optimized tables in an application

While doing an Internet of Things pilot project for a technology engineering group in Scandinavia we got a chance to try out In-Memory OLTP in SQL Server 2014.
What we found was a simple way that can be expanded upon to provision, use and, deprovision the memory optimized objects for the duration of our experiments.

One challenge that the project faced was that the equipment performed and buffered up to 100’000 measurements per second. So we would initially see if we were able to put all this information into SQL Server.
The first thing you need to do to be able to put this functionality into your SQL Server is to make sure you are running SQL Server 2014 or higher in developer edition or enterprise edition.
After that, connect to an instance and either create a new database or use an old one, then add a file-group for memory optimized data

USE [master];

CREATE DATABASE [IoT];
GO
ALTER DATABASE [IoT] ADD FILEGROUP [IoT1] CONTAINS MEMORY_OPTIMIZED_DATA
Go
ALTER DATABASE [IoT] ADD FILE(name=’IoT1′,filename=’C:\data\IoT1′) TO FILEGROUP [IoT1] –Make sure that the folder “c:\data\” exists, this is where the schema, and data are placed for durability.
Go
ALTER DATABASE [IoT] SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON

The objects that we needed for our use case was a table, a table type and a stored procedure that uses the table type as a parameter and inserts that content into the table.
We also need an additional procedure to get the max RowId value since we cannot auto increment but must keep track of what we insert.

Here is an example of a structure that our program can create automatically.
You can use this as a reference for setting this up yourself.

This particular example is for monitoring parking garage events.

--An in-memory table
CREATE TABLE [dbo].[Events]
(
[RowID] [int] NOT NULL,
[EventTime] [datetime2](0) NOT NULL,
[ParkingSpotId] [int] NOT NULL,
[ParkingSpotLane] [int] NOT NULL,
[ParkingSpotStatus] [bit] NOT NULL,
[VehicleClass] [varchar](20) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[DistanceFromEntrance] [numeric](18, 12) NOT NULL,

INDEX [IX_ParkingSpotID] NONCLUSTERED
(
[ParkingSpotId] ASC
),
CONSTRAINT [PK_Events] PRIMARY KEY NONCLUSTERED
(
[RowID] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )

–An in-memory table type
CREATE TYPE [dbo].[BulkInsertType] AS TABLE(
[RowId] [int] NOT NULL,
[EventTime] [datetime2](0) NOT NULL,
[ParkingSpotId] [int] NOT NULL,
[ParkingSpotLane] [int] NOT NULL,
[ParkingSpotStatus] [bit] NOT NULL,
[VehicleClass] [varchar](20) COLLATE Finnish_Swedish_CI_AS NOT NULL,
[DistanceFromEntrance] [numeric](18, 12) NOT NULL,
PRIMARY KEY NONCLUSTERED
(
[RowId] ASC
)
)
WITH ( MEMORY_OPTIMIZED = ON )
GO

–A natively compiled stored procedure that copies data from the table valued parameter to the table
CREATE PROCEDURE [dbo].[BulkInsertEvents] @tvp dbo.BulkInsertType READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER –CALLER
AS BEGIN ATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’us_english’)

INSERT INTO dbo.[Events]
( RowID
, [EventTime]
, [ParkingSpotId]
, [ParkingSpotLane]
, [ParkingSpotStatus]
, [VehicleClass]
, [DistanceFromEntrance]
)
SELECT
RowId,
[EventTime]
, [ParkingSpotId]
, [ParkingSpotLane]
, [ParkingSpotStatus]
, [VehicleClass]
, [DistanceFromEntrance]
FROM @tvp;
END

What is so incredible about a natively compiled procedure is that it reduces the amount of work that our system needs to perform to make sure that the command completes. It limits us of course in that we can’t use any disk-based tables or any other non-memory optimized objects.

Here is what calling this particular procedure might look like in T-SQL:

DECLARE @tvp [dbo].[BulkInsertType]
insert into @tvp(
RowID, EventTime, ParkingSpotId, ParkingSpotLane, ParkingSpotStatus, VehicleClass, DistanceFromEntrance
)
values (1,getdate(),1,1,1,'Sedan',302.1);
EXECUTE [dbo].[BulkInsertEvents] @tvp;

If we need to figure out what to set the RowId to we need that extra procedure that I mentioned earlier. For this table structure it would look like this:

CREATE procedure [dbo].[GetTopEventId]
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')

SELECT
ISNULL(MAX(RowId),0) AS RowId
FROM dbo.[Events];
END

Now to call this from a .net application in c# we need to pass on a data structure that is compatible with or table valued parameter.
DataTables are excellent for this:

private static void CreateInMemDataTable()
{
RawEventsInMemTable = new DataTable();
DataColumn RowId = new DataColumn();
RowId.DataType = typeof(int);
RowId.AutoIncrement = true;
RowId.AutoIncrementSeed = GetMaxRowId() + 1; //This method uses our other stored procedure to get the RowId out from the database.
RowId.AutoIncrementStep = 1;
RowId.ColumnName = "RowId";
RawEventsInMemTable.Columns.Add(RowId);
RawEventsInMemTable.Columns.Add(new DataColumn("EventTime", typeof(DateTime)));
RawEventsInMemTable.Columns.Add(new DataColumn("ParkingSpotId", typeof(int)));
RawEventsInMemTable.Columns.Add(new DataColumn("ParkingSpotLane", typeof(int)));
RawEventsInMemTable.Columns.Add(new DataColumn("ParkingSpotStatus", typeof(bool)));
RawEventsInMemTable.Columns.Add(new DataColumn("VehicleClass", typeof(string)));
RawEventsInMemTable.Columns.Add(new DataColumn("DistanceFromEntrance", typeof(double)));
}

What you do is you find an appropriate way of expressing the data in your application as a DataTable . One way of doing it is buffering with Reactive Extensions and converting to a DataTable using a FastMember ObjectReader.
Another simpler way of converting a list is by a row by row basis which does consume a lot of cpu and memory and scales rather poorly if you are doing really high throughput:

//A simple method that maps a list of events from our parking garage to our stored procedure by looping over the contents to make it conform to what SQL Server is happy with.
internal static void SendRawToInMemTable(IList data)
{
CreateInMemDataTable();
/*...*/
foreach (ParkingSpotEvent p in data)
{
int i = 0;
DataRow dr = RawEventsInMemTable.NewRow();
foreach (object o in p.ToArray())
{
i++;
dr[i] = o;
}
RawEventsInMemTable.Rows.Add(dr);
}

/*…*/

//Connection to local database from properties
string InMemSQLConnectionString = Properties.Settings.Default.InMem_ConnectionString;
InMemSQLConnection = new SqlConnection(InMemSQLConnectionString);
InMemSQLConnection.Open();

//Run the command using the table as an argument, then clear out the buffer table so that the next call won’t create duplicates.
string commandText = “[dbo].[BulkInsertEvents]”;
SqlCommand com = new SqlCommand(commandText, InMemSQLConnection);
com.CommandType = CommandType.StoredProcedure;
com.Parameters.Add(“@tvp”, SqlDbType.Structured).Value = RawEventsInMemTable;
com.ExecuteNonQuery();
RawEventsInMemTable.Clear();
}