Northwind on Hekaton

At the NY Code camp we were discussing the NorthWind Hekaton database. Microsoft has adapted the Northwind to In Memory - scripts are here

The scripts are a good way to understand how to workaround the limitations of In-Memory. There are ample comments describing any workaround.

If you would like to compare with the original Northwind, you can find the original scripts here (you have to install the application and use the sql script deployed by the msi).

Presentation from NY Code Camp

Here is the presentation

Installing SQL Server 2014 on the VM

Note: This is part of a series on SQL Server 2014 In Memory. Start here

Download the CTP1 here.

Copy the ISO to the local drive on the VM if you downloaded on the host (via the Shared Folder - see the Windows Install step)

Start the installation and follow the prompts. Few Screenshots below:

Once installation is complete, open the Sql Server Management Studio located at C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe

Then follow the demos in this blog and other Videos and documents to learn about this feature!

Demo: Transaction Log Optimization for In Memory Tables

Note: This is part of a series on SQL Server 2014 In Memory. Start here

Courtesy - Jos de Bruijn's TechEd video

CREATE TABLE [dbo].[t2_inmem]
( [c1] int NOT NULL,
  [c2] char(100) NOT NULL,

  CONSTRAINT [pk_t2_inmem] PRIMARY KEY NONCLUSTERED HASH ([c1]) WITH (BUCKET_COUNT = 1000000)
) WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA)
go



CREATE TABLE [dbo].[t2_disk]
( [c1] int NOT NULL,
  [c2] char(100) NOT NULL)
go

CREATE UNIQUE NONCLUSTERED INDEX t2_disk_ix1 ON t2_disk(c1)
go

--
-- insert 100 recs into disk table
--
begin tran
declare @i int = 0
while (@i < 100)
begin
insert into t2_disk values(@i, replicate('1', 100));
set @i = @i + 1;
end;
commit;


-- 200 log records can be seen (b-tree and index)
select * from sys.fn_dblog(NULL, NULL)
where PartitionId in (select partition_id from sys.partitions where object_id = object_id('t2_disk'))
order by [Current LSN] asc

-- size of tx log bytes (32K)
select sum([Log Record Length]) from sys.fn_dblog(NULL, NULL)
where PartitionId in (select partition_id from sys.partitions where object_id = object_id('t2_disk'))

--
-- in mem table, insert 100 records
--
begin tran
declare @i int = 0;
while (@i < 100)
begin
insert into t2_inmem values (@i, replicate ('1', 100));
set @i = @i + 1;
end;
commit;

-- you will find just one record
select * from sys.fn_dblog(NULL, NULL)
order by [Current LSN] desc;

-- look inside the one record- will contain 100 rows; also size is smaller
select [Current LSN], [Transaction ID], Operation,
operation_desc, tx_end_timestamp, total_size, table_id
from sys.fn_dblog_xtp(null, null)
where [Current LSN] = '0000001f:0000067d:0002';

Demo: Create a Native Stored Procedure

Note: This is part of a series on SQL Server 2014 In Memory. Start here

--
-- create native insert sproc: inserts 1M rows!
--
IF EXISTS (SELECT 1 FROM sys.all_objects where name = 'InsertData_Destination_Memory')
begin
DROP PROCEDURE InsertData_Destination_Memory;
end;
GO

CREATE PROCEDURE InsertData_Destination_Memory
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
declare @i int;
select @i = 1;

delete from dbo.Destination_Memory;

while @i < 1000000
begin
insert into dbo.Destination_Memory values(@i, @i, @i);
select @i = @i + 1;
end;
END
GO

--

-- Now in-memory table insert - 1 Million rows. Look how fast it is!

--

EXECUTE InsertData_Destination_Memory;

GO

Demo: Create a Memory Optimized Table

Note: This is part of a series on SQL Server 2014 In Memory. Start here

--
-- Create memory-optimized table
--
CREATE TABLE Destination_Memory
(
--See the section on bucket_count for more details on setting the bucket count.
col1 INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
col2 INT NOT NULL,
col3 INT NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

--

-- Insert into memory table using T-SQL

--

SET NOCOUNT ON;

delete from Destination_Memory;

declare @i int;

select @i = 1;

while @i < 20000

  begin

insert into Destination_Memory values(@i, @i, @i);

select @i = @i + 1;

  end;

select count(*) from Destination_Memory;

Demo: Create a Database and enable for In Memory

Note: This is part of a series on SQL Server 2014 In Memory. Start here

--
-- Create database with memory-optimized data filegroup
-- Notice the Memory Optimized Filegroup (filestream)
-- Also notice the collation (BIN2)
--
USE MASTER;
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'Hekaton_Demo')
BEGIN
DROP DATABASE Hekaton_Demo;
END;
GO

CREATE DATABASE Hekaton_Demo
ON
PRIMARY(NAME = [hekaton_demo_data],
FILENAME = 'C:\DATA\hekaton_demo_data.mdf', size=500MB),
FILEGROUP [hekaton_demo_fg] CONTAINS MEMORY_OPTIMIZED_DATA(
NAME = [hekaton_demo_dir],
FILENAME = 'C:\DATA\hekaton_demo_dir')
LOG ON (name = [hekaton_demo_log], Filename='C:\DATA\hekaton_demo_log.ldf', size=500MB)
COLLATE Latin1_General_100_BIN2;
GO

USE Hekaton_Demo;
GO