Jason McKittrick's Blog

SQL Server 2014 CTP 1 – Hekaton

Hekaton is the new SQL Server 2014 In-Memory database technology.  It is going to change the way we architect databases going forward. Decisions about which data or tables go into memory and which ones will function in the SQL Inter-Op mode.   Until we get a ton of detail here is a simple Hekaton ST-SQL Script for creating a In-Memory table.  In the example I provided below, it is assumed that you have a C:\data directory.

 

–Create Database with memory-optimized Data filegroup

–Drop Database HekatonDemoDB

CREATE DataBASE HekatonDemoDB

ON

PRIMARY(NAME = [HekatonDemoDB_Data],FILENAME = ‘C:\Data\HekatonDemoDB_Data.mdf’),

FILEGROUP [HekatonDemoDB_fg] CONTAINS MEMORY_OPTIMIZED_Data

(NAME = [HekatonDemoDB_dir],FILENAME = ‘C:\Data\HekatonDemoDB_dir’)

LOG ON (name = [HekatonDemoDB_log], Filename=‘C:\Data\HekatonDemoDB_log.ldf’,Size = 100MB)

GO

USE HekatonDemoDB;

GO

–Creation of a Simple Table to show Hekaton

Create Table HekatonT1

(

DataC1 int not Null,

DataC2 char(100) not null

Constraint [IdxT1DC1] Primary Key NonClustered Hash (DataC1) with (Bucket_Count =1000000)

) With (Memory_Optimized = ON, Durability = Schema_and_Data)

GO

–Insert 10,000 Rows into Hekaton1

Begin Tran

Declare @x int = 0

While (@x < 10000)

Begin

Insert into HekatonT1 Values(@x, replicate (‘1’,100))

Set @x = @X +1

End

Commit

–Exec Plan

Select * from HekatonT1

–Creation of a Simple Table to show Hekaton

Create Table HekatonT2

(

DataC1 int not Null,

DataC2 char(100) not null

Constraint [IdxT2DC1] Primary Key NonClustered Hash (DataC1) with (Bucket_Count =1000000)

) With (Memory_Optimized = ON, Durability = Schema_and_Data)

GO

–Insert 100,000 Rows into HekatonT2

Begin Tran

Declare @x int = 0

While (@x < 100000)

Begin

Insert into HekatonT2 Values(@x, replicate (‘1’,100))

Set @x = @X +1

End

Commit

–Exec Plan

Select * from HekatonT2

–Memory DMV

Select Object_name(object_ID) as TableName, * from sys.dm_db_xtp_table_memory_stats where Object_ID >0

–HekatonT1 309576141 1518 1406 8192 8192

–Delete Rows from HekatonT1

Declare @x int = 0

While (@x < 10000)

Begin

delete HekatonT1 where DataC1 = @X

Set @x = @X + 2

End

–Look at rows left

Select * from HekatonT1

–Creation of a Disk Table

CREATE TABLE [dbo].[DiskTable1](

[DataC1] [int] NOT NULL,

[DataC2] [char](100) NOT NULL,

CONSTRAINT [PK_DiskTable1] PRIMARY KEY CLUSTERED

(

[DataC1] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

–Insert 100,000 Rows into DiskTable1

Begin Tran

Declare @x int = 0

While (@x < 100000)

Begin

Insert into DiskTable1 Values(@x, replicate (‘1’,100))

Set @x = @X +1

End

Commit

–Join Memory and Disk

Select *

from HekatonT2 ht2 with (Snapshot)

Inner Join DiskTable1 dt1

on ht2.DataC1 = dt1.DataC1

—————————————————————————————————————-

–Create natively compiled stored procedure

CREATE PROCEDURE GetAllRowHekatonT1

WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER

AS

BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N’us_english’)

SELECT DataC1, DataC2 FROM [dbo].[HekatonT1]

END

GO

–Select from memory-optimized table through natively compiled stored procedure

EXECUTE GetAllRowHekatonT1

–Select from memory-optimized table through interpreted Transact-SQL

SELECT * FROM [HekatonT1] WITH (SNAPSHOT)

 

1 comment for “SQL Server 2014 CTP 1 – Hekaton

  1. Matt Smith
    September 6, 2015 at 9:15 am

    Great walkthrough. I was looking to get started with SQL In-Memory and it was just what I needed. Please keep blogging. Your material is helpful.

Leave a Reply

Your email address will not be published. Required fields are marked *