How do I use SQL Server's "Hekaton"?

Question

The SQL Server 2017 version on Linux comes packaged with a utility called mtlogreader.exe that is absent from the Windows distribution,

MtLog Reader - Dump Hekaton MtLog files

Usage: \\VBOXSVR\mssql\lib\sqlservr\Content\binn\mtlogreader.exe <file> [options]

Options:

        -verbose <int>  Verbosity (0-4) Default 1. (short form: -v)
        -buffio <bool>  Buffer stdout (true/false) Default true (short form: -b)
        -keySecret1 <string>    The first secret key blob used for crypto keys (short form: -k1)
        -keySecret2 <string>    The Second secret key blob used for crypto keys (short form: -k2)
        -instructionFile        Treat file as instructionfile. (short form: -i)
        -targetruntimeinseconds <int>   The target run time in seconds (only valid with -i). (short form: -t)
        -skipchksum     Skip checksum validation. (short form: -sc)
        -maxbsn <uint64>        Max BSN to dump. Default 0 (dump whole file). (short form: -m)

<file> has a different meaning depending on whether -instructionFile is specified
if -instructionFile is specified, <file> is a file containing checkpoint file
information extracted at dump time, containing the root file name followed by a
list of container directories (one per line).
 the -t parameter is an indication of how long to run the tool
when used with -i it is used to limit the processing of checkpoint files

I can't find much about Hekaton though. I've found some errors here and I see it has a pretty old wiki page where can I find documentation on the Hekaton subsystem but how to use it? Has Hekaton been rolled into the "Analysis Services"?

Answer 1

Hekaton was the internal Microsoft project that references the In-Memory OLTP feature, starting with SQL 2014. Some people still use the word Hekaton generically, to describe the In-Memory feature in any version of SQL server (2014, 2016, 2017).

In-Memory OLTP is a feature that stands alone - it is not part of any other feature, i.e. Analysis Services.

There is tons of info out there on the web about In-Memory OLTP, but I would caution you to avoid anything that references SQL 2014 (as you have above), because that was essentially v1.0, and much has changed since then.

First - have you proven through a valid POC that your workload is likely to benefit from using In-Memory OLTP?

I have blogged extensively about In-Memory OLTP, but you might be better off starting with the documentation, here:

https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/in-memory-oltp-in-memory-optimization

Answer 2

Microsoft IT: A Case Study on “Hekaton” against RPM – SQL Server 2014 CTP1 seems to demonstrate the syntax,

Migration to Hekaton, Procedure

1. Before creating memory optimized tables, create the file group and declare the database as memory optimized:

   ALTER DATABASE RPM ADD FILEGROUP rpm_mod CONTAINS MEMORY_OPTIMIZED_DATA;
   GO
   ALTER DATABASE RPM ADD FILE (NAME='rpm_mod', FILENAME='C:\RPMHekatonFilegroup\rpm_mod') TO FILEGROUP rpm_mod;
   GO
   

2. Determine the bucket size and create the memory optimized table: The Schedule table (candidate for migration in the AMR report) is converted to a memory-optimized Schedule table. Only NON CLUSTERED HASH indices can be used in a memory optimized table. A hash index consists of an array of pointers. Each element of the array is called a hash bucket. The bucket size is chosen as equal to, or greater than the expected cardinality (the number of unique values) of the index key column. There is a greater likelihood that each bucket only has rows with a single value in its chain. The memory optimized schedule table is created using the following:

   CREATE TABLE [dbo].[Schedule]
   (
     [ScheduleID] [int] NOT NULL,
     [BookingID] [int] NOT NULL,
     [ResourceID] [int] NOT NULL,
     [Date] [datetime] NOT NULL,
     [Minutes] [int] NOT NULL,
     [LastModifiedOnDate] [datetime] NOT NULL,
     [LastModifiedByID] [int] NOT NULL,
     [LastModifiedDate] [datetime] NULL
   
   INDEX [IX_Schedule_BkId_ScheId] NONCLUSTERED HASH 
   (
     [BookingID],
     [ScheduleID]
   )WITH ( BUCKET_COUNT = 33554432),
   INDEX [IX_Schedule_Covering_Index] NONCLUSTERED HASH 
   (
     [BookingID],
     [ResourceID]
   )WITH ( BUCKET_COUNT = 33554432),
   INDEX [IX_ScheduleDate] NONCLUSTERED HASH 
   (
     [Date]
   )WITH ( BUCKET_COUNT = 33554432),
    PRIMARY KEY NONCLUSTERED HASH 
   (
     [ScheduleID]
   )WITH ( BUCKET_COUNT = 33554432),
   INDEX [ScheduleIX1D] NONCLUSTERED HASH 
   (
     [ResourceID]
   )WITH ( BUCKET_COUNT = 33554432),
   INDEX [ScheduleIX2D] NONCLUSTERED HASH 
   (
     [BookingID]
   )WITH ( BUCKET_COUNT = 33554432),
   INDEX [UI_DATE_RESOURCE_BOOKING] NONCLUSTERED HASH 
   (
     [ResourceID],
     [BookingID],
     [Date]
   )WITH ( BUCKET_COUNT = 33554432)
   )WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
   
   GO
   

When the new memory-optimized Schedule table is created, data from the non-memory optimized Schedule table is inserted into this new table. The old Schedule table is dropped, renamed, and can be used as backup. All further transactions on the Schedule table are performed on the new memory-optimized version of the Schedule table. Since No UNIQUE indexes other than for the PRIMARY KEY are allowed, the insertions are tracked by using Sequences.