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.
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.
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.
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)
@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
( 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)
( NAME = N”NorthwindHekaton_log”, FILENAME = ”’ + @Log + N’NorthwindHekaton_log.ldf” , SIZE = 1344KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)’)
You can download a complete script to create a Northwind database with in-memory capabilities and fill it with data here.
Once you have defined a memory-optimized filegroup, you are now in a position to create your first memory-optimized table.
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
CONSTRAINT PK_ProductsInMem PRIMARY KEY NONCLUSTERED HASH
)WITH ( BUCKET_COUNT = 128)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
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.
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:
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.
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.