SQL Server 2014 New Features: How to Create a Hekaton In-Memory-Optimized Table

SQL Server 2014 trainingPractice: How to Create a Hekaton Memory-Optimized Table

In this installment, we will step through the actual process of creating an in-memory table for an SQL Server database. As mentioned in the Hekaton overview, in-memory tables are streamed into RAM when SQL Server starts up. We must therefore first create a special FILESTREAM filegroup for this purpose.

Important Point #1

You cannot change your mind!

Since many aspects of an in-memory database cannot be altered, only dropped and re-created, make sure you have appropriate backups before beginning your experiments.

How to Add In-Memory Optimization to An Existing Database

The same Management Studio dialog used to create regular filegroups can now define a mempry-optimized filegroup, necessary for your Hekaton tables. This dialog can be accessed by going to database properties and clicking on “Filegroups” in the “Select a Page” section on the left.

Management Studio Filegroup Dialog
Management Studio Filegroup Dialog

 

 

It seems a trifle odd that the Management Studio provides a means to create a memory-optimized filegroup but does not provide a means for creating a “file” for that filegroup. Nevertheless, I have been unable to discover a way to create the “file” using the graphical interface. You will have to create the “file” in T-SQL. Although you will be using the “Add File” statement, what you are adding turns out not to be a file at all, but rather a directory. SQL Server will create files within that directory when you define a memory-optimized table.

ALTER DATABASE Northwind

ADD FILE ( NAME = N’hkNorthiwnd’

, FILENAME = N’C:\DATA\hkNorthwind’

–, SIZE = 3072KB , FILEGROWTH = 1024KB — Size and Filegrowth cannot be specified for hekaton files

)

TO FILEGROUP NorthwindTest

Note that you cannot set values for SIZE and FILEGROWTH as you would for a normal database file, which makes sense considering you are not actually creating a file at all.

If you are writing a script to create a new database from scratch, you can define the memory-optimized filegroup right in the CREATE DATABASE statement.

DECLARE @DB NVARCHAR(4000), @Log NVARCHAR(4000)

SELECT

@DB = CAST(serverproperty(‘InstanceDefaultDataPath’) AS NVARCHAR(4000)), — serverproperty function returns sql_variant

@Log = CAST(serverproperty(‘InstanceDefaultLogPath’) AS NVARCHAR(4000))

EXECUTE (‘CREATE DATABASE NorthwindHekaton

CONTAINMENT = NONE

ON PRIMARY

( NAME = N”NorthwindHekaton”, FILENAME = ”’ + @DB + N’NorthwindHekaton.mdf” , SIZE = 5312KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

FILEGROUP [hkNorthwind] CONTAINS MEMORY_OPTIMIZED_DATA DEFAULT

( NAME = N”hkNorthwindInMemFG”, FILENAME = ”’ + @DB + N’hkNorthwind” , MAXSIZE = UNLIMITED)

LOG ON

( NAME = N”NorthwindHekaton_log”, FILENAME = ”’ + @Log + N’NorthwindHekaton_log.ldf” , SIZE = 1344KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)’)

GO

You can download a complete script to create a Northwind database with in-memory capabilities and fill it with data here.

Creating Memory-Optimized Tables in SQL Server

Once you have defined a memory-optimized filegroup, you are now in a position to create your first memory-optimized table.

Important Point #1 – For the Second Time!

You cannot change your mind!

Memory-optimized tables can never be altered, they can only be dropped and re-created. Therefore, even in an experimental environment, it pays to think things through before hitting F5.

One of the more important decisions you must commit to is the choice of the bucket count when creating a hash index. To achieve the best performance enhancement, there should be sufficiently many buckets that the probability of having two rows with the same hash value is small. Your hash bucket count should exceed the number of rows by at least a factor of two. Remember that you cannot change this value once the table is defined, so choose a bucket count not only large enough for the data you have today, but also for the new rows you will be adding in the future.

Whatever number you pick, SQL Server will round it up to the nearest even power of two. We will discuss hash buckets in greater detail in the upcoming Theory section.

Here is the table creation SQL for the in-memory version of the Northwind products table.

CREATE TABLE dbo.ProductsInMem

(

ProductID INT IDENTITY(1,1) NOT NULL,

ProductName NVARCHAR(40) COLLATE Latin1_General_100_BIN2 NOT NULL,

SupplierID INT NULL,

CategoryID INT NULL,

QuantityPerUnit NVARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

UnitPrice MONEY NULL CONSTRAINT [DF_Products_UnitPrice] DEFAULT ((0)),

UnitsInStock SMALLINT NULL DEFAULT ((0)),

UnitsOnOrder SMALLINT NULL DEFAULT ((0)),

ReorderLevel SMALLINT NULL DEFAULT ((0)),

Discontinued BIT NOT NULL DEFAULT ((0)),

INDEX IX_ProductName NONCLUSTERED

(

ProductName ASC

),

CONSTRAINT PK_ProductsInMem PRIMARY KEY NONCLUSTERED HASH

(

ProductID

)WITH ( BUCKET_COUNT = 128)

)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

GO

A few points are worthy of emphasis.

Note that the ProductID column still has the IDENTITY attribute. Despite assertions to the contrary in some blogs, IDENTITY still works in memory-optimized tables.

Note also that the BUCKET_COUNT is very low, but hey, c’mon, this is Northwind.

A more subtle but very important point is the assignment of the Latin1_General_100_BIN2 collation to the ProductName column. This is the only collation allowed if you want to create an index on a character column.

Monitoring Your Hekaton Memory-Optimized Tables

With new SQL Server technology comes new dynamic managlement views (DMVs). The DMVs providing information relevant to in-memory technology all contain “xtp” in their prefixes. Here is the complete list:

sys.dm_db_xtp_checkpoint_stats

sys.dm_db_xtp_checkpoint_files

sys.dm_db_xtp_gc_cycle_stats

sys.dm_db_xtp_hash_index_stats

sys.dm_db_xtp_index_stats

sys.dm_db_xtp_memory_consumers

sys.dm_db_xtp_merge_requests

sys.dm_db_xtp_table_memory_stats

sys.dm_db_xtp_transactions

sys.dm_xtp_gc_stats

sys.dm_xtp_gc_queue_stats

sys.dm_xtp_system_memory_consumers

sys.dm_xtp_transaction_stats

At the moment, we’ll only take the time to mentioned sys.dm_db_xtp_hash_index_stats.

SELECT OBJECT_NAME(object_id) AS [Table Name], * FROM sys.dm_db_xtp_hash_index_stats

The output allows you to monitor your tables’ use of hash buckets, which, as we have mentioned, is an important determinant of performance.

Results from  sys.dm_db_xtp_hash_index_stats
Results from sys.dm_db_xtp_hash_index_stats

 

Stay tuned! In the near future we will take a look at some of the technological details that lie behind the memory-optimized table we just created.

Please do not hesitate to drop me a note if you have any comments or questions.

To learn more about new enhancements to SQL Server 2014 including Hekaton tables, have a look at our new 3-day hands-on course – SQL Server 2014 Performance Enhancements.  You can take the course from one of our global Education Centers or online from home or office.

 

 

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.