What The Hek?

I’ve got a few experiments that I’ve been thinking up that I’d like to run against the Hekaton In-Memory OLTP engine that’s supposed to be the new hotness in the upcoming SQL Server 2014 release.

I’ve got a Windows Server 2012 R2 (Core) VM that I decided to use for this instance. Since I’m exploring new features, I created a separate disk which I formatted with the ReFS filesystem. I’ve heard some neat things about it so I wanted to give it a try.

CREATE DATABASE WhatTheHek;
GO

USE WhatTheHek;
GO

ALTER DATABASE WhatTheHek
	ADD FILEGROUP hek_fg CONTAINS MEMORY_OPTIMIZED_DATA;

So far so good. This is getting exciting!

Alter failed for database ‘WhatTheHek’

Uh oh. What the heck? Additionally, the file appears in the both the OS folder and in the FILES panel of the properties of the database. This perplexed me for a while until I thought of the big variable in the equation: ReFS. Sure enough, ReFS doesn’t support named streams or sparse files. Additionally, ReFS isn’t officially supported for SQL Server 2012 and now it appears unlikely that SQL Server 2014 will be supported on ReFS.

After a bit of retooling (and reformatting to NTFS) I was able to successfully run my DDL.

CREATE DATABASE WhatTheHek 
GO

USE WhatTheHek

ALTER DATABASE WhatTheHek
	ADD FILEGROUP hek_fg CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASE WhatTheHek 
	ADD FILE (NAME=hek_fg_file,
				FILENAME='D:\MSSQL11.MSSQLSERVER\MSSQL\DATA\hek_fg_file') 
	TO FILEGROUP hek_fg

CREATE TABLE sample_data(
	sample_data_id bigint NOT NULL,		
	sample_set_id int NOT NULL,
	sample_set_time datetime2(0) NOT NULL,
	offset int NOT NULL, 
	CONSTRAINT pk_sample_data_hek PRIMARY KEY NONCLUSTERED HASH (sample_data_id,sample_set_id) 
		WITH (BUCKET_COUNT = 10240)
) WITH (MEMORY_OPTIMIZED=ON);

Now it’s time to create my actual tables and to run some experiments.

As an aside, this issue was addressed by Visa Tikkanen, but the images weren’t search indexed and I couldn’t find it and I never thought to link it to ReFS.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s