Hello there,
In-Memory concept, which was first introduced into our lives with SQL 2014, was made more useful and compatible with SQL 2017.
In this article I will describe how to convert existing tables into an in-memory table.
Database Compatibility Level control is done first and SQL 2017 (140) must be selected.
Then, to open the In-Memory feature on a database basis, we have to run the following script.
We cannot open this setting from the SSMS interface, so we have to open it this way.
After making these adjustments, our database is ready to create the In-Memory table.
When we click on any table and right click, we enter the Memory Optimization Advisor.
A wizard comes and we continue.
Firstly, the In-Memory table should not be related to other tables.
For this reason, the next step is the In-Memory compatibility of the table.
This step specifies that we must consider these warnings before Memory optimization is performed.
For example; The present procedure is as necessary to rebuild the functions.
Then we come to the area where we will make basic In-Memory adjustments.
Here is a special file group for the in-memory table to create and save the disk space, file group name and a copy of the original state of the table are available.
Then the existing Primary Key or Index is converted to in-memory.
Hash Index has also been added to the indexes created in the in-memory table to access the data faster.
Another area determined here is the Bucket Count field. Currently, this field is specified by the number of records in the table and is usually specified to be twice the number of records in the table.
Finally, we say Migrate and start the In-Memory table conversion process.
Here, the original table name is the name of the _old name and the new table is created with the current name as in-memory and the old table data is inserted into the new table.
You can do the same with T-SQL as below.
It is also possible to set the Memory Optimization option to be open in both schema and data and to determine the number of Bucket Count of Hash Primary Key.
We can follow the steps below to add a Columnstore Index and further improve our performance.
At this point, I will give examples from different environments.
You can add the Columnstore Index with the T-SQL code as follows and then add it through the original table and then use it in the In-Memory table.
In the index migration area, this time, we are questioning the usage status of the existing Columnstore Index.
In the examples below, the effect of the same query on the system and the query result times are shared over the normal and In-Memory tables.
The results show that especially the In-Memory table queries do not make any logical reads and the query result is 10 times faster.
I gave an example for a single query. When I get to experiment with various ad-hoc queries, I can say that the speed difference has really reached 10 times.
In-Memory Table
Table
