In Memory - what is the performance like ?

If you are curious as to what you gain by giving up FKEYS and Constraints ;) I have a layman's performance test which you can try yourself using the demo scripts I provided in my earlier post.

Here is what I see:

Table type	Action	Time Taken
Disk	Insert 20K Rows using T-SQL	18 secs
Disk	Insert 20K Rows using T-SQL Stored Procedure	18 secs
In-Memory	Insert 20K Rows using T-SQL	15 secs
In-Memory	Insert 1Million Rows using Native	3 secs

Pretty impressive!

Note: I did this on a VM using a 2 year old i3 laptop. I urge you to try this out and see what you get. I intend to do more tests using other scenarios in future

Collations in In Memory tables and stored procedures

If you do string comparisons in your database and rely on case-insensitive search, beware that In-Memory requires BIN2 collation which is case sensitive.

Read more here on Bob Beauchemin's blog: Thinking about Hekaton? Then think about collations.

Additional information and workarounds described on MSDN Online

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

Install Windows Server 2012 R2 on the VM

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

Download Windows Server 2012 R2 Preview here

Once you download the ISO file, open the Virtual Machine and point to the file:

Use the prompts and install. Select the DataCenter with GUI option. Also use the Custom Install option.

You will need the Guest Additions. Download within the VM using this link. You will need guest additions to create a share with your host and few other things - very handy if you want to share files between the host and the VM etc.

You can add shared folder once you install Guest Additions via the "Shared Folder" menu on the VM (make it permanent and Auto-mount):

Then you can see the shared folder under \\VBOXSVR:

Also on the VirtualBox menu enable Clipboard sharing. You may need to restart the VM.

Creating a Virtual Box VM

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

Download VirtualBox from the Oracle site (Note I am assuming you are still oldschool and use Windows, so everything that follows is verified on Windows)

The version I downloaded was 4.2.18.

I always unblock the software I download:

Install the VirtualBox software.

Create a New VM and follow the various prompts (select default options on almost all screens).

Select a suitable size for memory; I suggest 4GB if you have 8 GB or more.

When you start the newly created VM, you should see a screen like below. Which means you have to download Windows Server 2012 R2. More on that in the next post.

SQL Server 2014 In memory - Links to reference material

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

Tech-ed North America Videos (Sunil Agarwal et al, Sunil is a SQL Server PM)

• Microsoft SQL Server In-Memory OLTP: Overview of Project “Hekaton”
• Microsoft SQL Server In-Memory OLTP Project “Hekaton”: App Dev Deep Dive
• Microsoft SQL Server In-Memory OLTP Project “Hekaton”: Management Deep Dive

Tech-ed Europe Videos (Jos de Bruijn, SQL Server PM)

• Microsoft SQL Server 2014 In-Memory OLTP: Overview
• SQL Server In-Memory OLTP: DB Developer Deep Dive
• SQL Server In-Memory OLTP: DBA Deep Dive

Microsoft SQL Server 2014 CTP1 Evaluation Resources
Hekaton: SQL Server’s Memory-Optimized OLTP Engine (SIGMOD 2013 article by Microsoft Team)
Hekaton Whitepaper for CTP 1 by Kalen Delaney
žHigh-Performance Concurrency Control Mechanisms for Main-Memory Databases, Microsoft Research
žSQL Server 2014 - MSDN Online Documentationž
Blog articles by Bob Beaucheminž
Getting Started with SQL Server 2014 In-Memory OLTP (SQL Server Team blog)
žAdditional links

SQL Server 2014 In Memory

Since I work in the financial industry, computing speed fascinates me (not just the hardware kind). There are times when a project or product is deemed nonviable because it is, well too slow.

So seeing one of my two most favorite RDBMS, SQL Server has added the In memory feature was exciting. Some of the recent products out of Microsoft are well thought out and this is one of them. The design is quite simple and integrates well with the base RDBMS features. It runs on commodity hardware which should let customers adopt quickly. Also, license cost is built into Enterprise edition, which should help as well.

Anyway, I intend to make good use of my couch time and write a few blog entries. The plan is to blog about:

SQL Server 2014 In memory - Links to reference material
Creating a Virtual Box VM
Installing Windows 2012 R2 Server on the VM
Installing SQL Server 2014 on the VM
Demo: Create a Database and enable for In Memory
Demo: Create a Memory Optimized Table
Demo: Creating Native Stored Procedures
Demo: Tx Log optimization for In Memory transactions
Demo: Data and Delta File layout for Memory Optimized tables
Demo: Transaction Isolation Levels with Memory Optimized tables
More demos..

So look back very soon..

Note: SQL Server 2014 is still in CTP1. While CTP1 has many restrictions, CTP2 is expected to remove some of those restrictions and contain more features.