No tags yet.

SEARCH BY TAGS: 

RECENT POSTS: 

FOLLOW ME:

  • Facebook - Black Circle
  • Twitter - Black Circle
  • Instagram - Black Circle
  • LinkedIn - Black Circle

In-Memory OLTP for Noobs Pt. 2

So let's jump right in and see if we can't make some of the In-Memory OLTP features work. Then I can work on elaborating the details.

Create Memory Optimized Filegroup

I’m going to go through a process of creating a memory optimized filegroup, then we'll add a container to said filegroup.

Create Tables:

Then I will go ahead and create a traditional table, and a In-Memory optimized table, and add it to the appropriate filegroup.

In the InMem table you'll notice that I create a hash index and a bucket count.

Quick note on Indexes for In-Memory OLTP tables:

Only two types of indexes can be created in In-Memory OLTP tables, Non-Clustered Hash Indexes and Non-Clustered Index. This is so that there are no bookmark lookups.

What is a hash index? Hash index is essentially a group or array of values separated into slots, or buckets as SQL Server calls it. These are optimized for index seeks for In-Memory OLTP tables.

What is bucket count? The concept of bucket count is very similar to indexes. It acts as a container for hash indexes to point to. However, they do not contain actual data, just the memory address in which the data/pages are kept.

Both of these concepts deserve their own blog post, so just take the above as a crude summary for now!

Create stored procs:

We will go ahead and create two stored procs to do some inserts (using a simple loop). one for the regular table, and one for the In-Memory table. Once that is done, we will execute the stored procs one at a time and compare the time it takes to complete.

As you can see, that is a significant difference in time. And this was just for a few thousand rows.

#sqlserver #hekaton #inMemoryOLTP #PerformanceTuning