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;

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.

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

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.

You can achieve same using SSMS also as shown below.

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

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;

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

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 Twitter |  Follow me on FaceBook