SQL Server In Memory OLTP, Hekaton

Dear Friends,

Memory optimized database technology is an important feature of SQL Server 2014 also known as Hekaton to optimize SQL Server In Memory OLTP workloads. Today we will find out how we can implement this in SQL Server 2014 using a simple example. As part of this demonstration, we will follow the steps mentioned below;

  • Create a database
  • Enable our database for in memory OLTP
  • Create memory optimized table
  • Load some data to respective table
  • Check how it reflects in reports pre/post data insert

Let us start by creating a database;

1_SQL_Server_In_Memory_OLTP_Hekaton

Now, we are going to add a filegroup to hold memory_optimized_data for our database. In case of server crash, unexpected restart  this filegroup will ensure durability. In such scenario, data recovered from this filegroup to memory.

2_SQL_Server_In_Memory_OLTP_Hekaton

3_SQL_Server_In_Memory_OLTP_Hekaton

If you are doing through SSMS, use filetype as FILESTREAM data as shown below.

4_SQL_Server_In_Memory_OLTP_Hekaton

We would now create our memory optimized table, GuestUsers as non durable one i.e. contents are stored in memory and are lost when server is restarted.

5_SQL_Server_In_Memory_OLTP_Hekaton

You can achieve same using SSMS also as shown below.

6_SQL_Server_In_Memory_OLTP_Hekaton

Ok, before I start inserting some records to the table, let’s find out how this report looks at present.

7_SQL_Server_In_Memory_OLTP_Hekaton

You can view this report by right clicking on the database then selecting Reports -> Standard Reports -> Memory Usage By Memory Optimized Objects.

Now, we will have to insert few records to the table, let’s proceed;

8_SQL_Server_In_Memory_OLTP_Hekaton

Time to find out how it reflects in report post data insertion;

9_SQL_Server_In_Memory_OLTP_Hekaton

 

From above picture, we can very easily understand used/unsed memory of the object. DMV dm_db_xtp_table_memory_stats returns usage stats for each in memory objects. This report does provide a quick overview of what is happening and I hope in future releases of SQL Server 2014 we will get to see more descriptive reports are being introduced.

Important point to note here is, these memory optimized objects does not support auto_update_statistics so you will have to manually run them.

 

Regards

Kanchan Bhattacharyya

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

Avatar

About Kanchan Bhattacharyya

Kanchan is an astute IT professional, a seasoned SQL Database Administrator with 13+ years of industry experience. A calculated risk taker with deep technical knowledge and has gained proficiency in database consulting and solutions across different environments. Kanchan holds MBA degree in IT and an International Executive MBA in Project Management. He is very passionate about SQL Server and holds MCSE (Data Platform), MCSA – SQL 2012, MCITP – SQL 2008 certifications. Currently he is focusing on cloud and latest releases of SQL Server. When not working, Kanchan likes to spend his time reading on new technical developments specifically on SQL Server and other related technologies.

View all posts by Kanchan Bhattacharyya →

2 Comments on “SQL Server In Memory OLTP, Hekaton”

  1. Great work Kanchan (Y)…..But heard that there is no locking machanism in IN MEMORY OLTP….is that true?

  2. Hi Debjeet,

    Thanks for the feedback. You are correct, no locks or latches are taken to guarantee transaction isolation. Transaction isolation level implemented using optimistic concurrency control and so is lock-free. What it means is, SQL Server doesn’t use locks to maintain consistency in case of multiple transactions are attempting to access data and they use snapshot isolation in combination with conflict detection to maintain consistency.

    Thanks,

    Kanchan

Leave a Reply

Your email address will not be published. Required fields are marked *