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.