Why i choose to use memory optimized table in MS SQL Server in some circumstances

MSSQL SERVER

I was supporting an application which will be making a lot of concurrent calls to the database. The application which will be widely used and it will be receiving a lot of hit at every interval.

The table was initially created using the traditional way, however when the application went live. We were experiencing timeout between the application and the database. However, there are a lot of database in the database servers but it is only the database for this particular application that is experiencing this timeout. The following were done in order to improve this issue but, it did not solve the problem:

1. Some of the HDD that were not on SSD were converted to SSD, this simply means all the database log, data files, temp db log and datafiles are all running on SSD
2. The memory and CPU was upgraded (increased)
3. Created indexes were applicable
4. The database server was moved to a non-congested host

Partitioned table is not an option at this particular interval because the application just needs to only read and not write to this table. The application is only doing a lookup from this table

Apparently, while drilling down. SQL server profiler was turned on and i observed that when the application is trying to make calls to the Store Procedure (SP), it is taking a lot of unreasonable time and it is just affecting the lookup table.

Memory optimized table to the rescue, immediately we created a memory optimized table and migrated the data to the newly created table. All the issues we were having was resolved, response time was better. Everybody was happy..

Below is the script used to create a memory optimized table

Alter Database testdb
  Add FileGroup transactions_fg 
  Contains MEMORY_OPTIMIZED_DATA;
Alter Database testdb 
  Add File (name='transactions', filename='F:\SQLSERVER\transaction_file') 
  To FileGroup transactions_fg;

This script below creates the table transactions`

Create Table [dbo].[Transactions] (
  id Int Not Null Primary Key Nonclustered,
  OrderId NChar(8) Not Null,
  Date DateTime Not Null,
  QuantityOnHand Decimal(8,2) Not Null ) 
  With (Memory_Optimized = On);

Memory optimized table also have it’s pros and crons. In order to take advantage of using memory optimized table, the most frequently used rows should be kept in the memory optimized table and the less frequently once which be kept in the traditional table..