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.