How to generate SQL server in memory table ?
I am using Microsft SQL Server 2016. I have an SQL table with 140000 rows. It is used only for selecting data. I am trying to increase it's selection performance by making it In-Memory table (it is worth to mention that this is my first time using In-Memory tables). The results I am getting in SQL Server profiler are rather dissatisfying. Compared data of 'CPU', 'Reads' and 'Duration' columns: SELECT from In-Memory table has 'CPU' a bit higher, 'Reads' up to 10 times lower but unfortunately 'Duration' stays pretty much the same.
I've tried generating In-Memory OLTP migration checklist - the results were successful. Could You please tell me if this is a good use of SQL server In-Memory tables? Maybe I am doing something wrong since duration stays the same? What are sql server in memory table?
Thanks in advance!
SQL server in memory table
In-Memory OLTP is a specialized, memory-optimized relational data management engine and native stored procedure compiler, integrated into SQL Server. Microsoft designed In-Memory OLTP to handle the most demanding OLTP workloads.
The results you got in the SQL Server profiler are dissatisfying because you did not read what issues memory-optimized tables do address. They were not invented to "just keep your data in memory", if your table is relatively small and frequently used, it will be in memory after the first reading from disk without any "optimizing".
Memory-optimized tables are lock free, it means in highly concurrent environment instead of beeing blocking on modifying when someone reads the data, new versions of rows are inserted, so there is no blocking. Your table is readonly, as you said, so what did you want to achieve? There is no blocking at all on this your table, so what did you want to resolve?
If you just did something without good understanding of what you are doing, I can suppose that your bucket count was also chosen wrong, and wrong bucket number slows down your SELECT.