Leverage the in-memory capabilities of SQL Server 2014 for better performance

SQL Server 2014 provides a comprehensive high-end database solution that can meet the emerging demands of the businesses and enterprises alike. It marks the release of Microsoft’s flagship database product that provides you a platform for hybrid cloud – you can now build, deploy and manage your databases that reside both on premise as well as in the cloud. SQL Server 2014 includes many interesting new features. One of such new features is the new OLTP engine.

I was intrigued on reading and exploring the in-memory capabilities of SQL Server 2014. Microsoft’s SQL Server 2014 promises to reap the benefits of 64-bit computing. The in-memory capabilities in SQL Server 2014 is facilitated by the In-Memory OLTP Engine – it’s integrated right into SQL Server 2014. This engine is code named as “Hekaton” and is an in-built feature in SQL Server 2014. Hakaton is a Greek word that implies “100”. Microsoft’s SQL Server 2014 was earlier code named as Hekaton because the software giant had the goal of boosting performance by 100 times. The MSDN states: “New in SQL Server 2014, In-Memory OLTP can significantly improve OLTP database application performance. In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine, optimized for OLTP.” (Reference)

You can download a trial version of SQL Server 2014 from Technet.

To use memory optimized tables in SQL Server 2014, all of the following requirements should be fulfilled:

1.) SQL Server 2014 (64-bit) Enterprise or Developer or Evaluation version installed 2.) You should have enough memory to allow for storing memory optimized database tables and indexes 3.) You should have enough of free disk space – preferably twice the size of your memory optimized tables 4.) 64-bit processor to support the instruction cmpxchg16b to use In-Memory OLTP should be available

Please refer to this MSDN article for more details in this regard

One of the most important differentiators amongst SQL Server 2014 and its earlier counterparts is in the former’s support for in-memory database tables. When our computer systems go slow we often increase the physical memory in the system so that you would get more RAM. Increasing memory does improve the performance to a certain extent. However, this doesn’t solve your problem always primarily because you need to know whether the microprocessor would support the amount of memory installed in your system, etc.

The newly introduced In-Memory OLTP engine in SQL Server 2014 enables you to create in-memory tables and then perform input output operations (I/O) against them in memory. Performing I/O operations against these in-memory tables result in faster reads and writes as your application can read from and write to the memory much faster compared to disk reads and writes. In essence, with SQL Server 2014 you can now move your database tables into memory for lower latency and improved performance. To enable your database to support in-memory OLTP, you should add a filegroup for the memory optimized data and also add a container to the filegroup you added.

Considering that you have a database named infoworld, here’s the script that you need to execute to enable this support.

ALTER DATABASE infoworld ADD FILEGROUP infoworld group CONTAINS MEMORY_OPTIMIZED_DATA ALTER DATABASE infoworld ADD FILE (name=’ infoworldoltp’, filename=’C:Joydip infoworldoltp’) TO FILEGROUP infoworld group GO

Note that any database in SQL Server 2014 that uses memory-optimized data must have at least one file group associated. This file group should be created using the CONTAINS MEMORY_OPTIMIZED_DATA option. Now that you have the database in place, you can create your memory optimized table with the script given below.

CREATE TABLE dbo.Payslip ( EmployeeId int not null primary key nonclustered hash with (bucket_count=4000000), UserId int not null index ix_UserId nonclustered hash with (bucket_count=2000000), Salary money ) WITH (MEMORY_OPTIMIZED=ON) GO

Note that the above table is a durable table, i.e., the contents of this database table wouldn’t be lost in the event of a server crash or server restart. If you would like only the schema to be preserved in the event of a server restart or crash, you should specify DURABILITY=SCHEMA_ONLY as shown in the script that follows.

CREATE TABLE dbo.Payslip ( EmployeeId int not null primary key nonclustered hash with (bucket_count=4000000), UserId int not null index ix_UserId nonclustered hash with (bucket_count=2000000), Salary money ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) GO

Read this MSDN blog post has more details on SQL Server 2014’s OLTP engine

It should be noted that the memory optimized database tables in SQL Server 2014 doesn’t support the following data types:-

Datetimeoffset Geography Hierarchyid Image Ntext Sql_variant Text Varchar(max) Xml User data types (UDTs)