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.