Every developer has heard the complaint “This app is so slow!!!”
At Gulf Coast Solutions, most of our apps are heavily data-driven and dependent on the database. For over 70% of our projects, the primary database we use is Microsoft SQL Server. SQL Server released Memory-Optimized Tables or MOTs in SQL Server 2014 with the promise (hope) of improved speed by a factor of 10. Did Microsoft live up to the hype? We are about to find out.
The Case Study
(The following actions were done with SQL Server 2017.)
First things first. How do we know if a table is a good candidate for becoming an MOT?
SQL Server has created a very handy report/chart that looks at your database and shows you the tables that are hit enough and that have the upside to be migrated to an MOT.
The results of this report are easy to read and understand.
Clearly one table stands out as a great candidate for this case study. Our candidate table is hit quite a bit and has some metrics already in place to evaluate the results from the migration to an MOT.
Let’s start the migration process
The following are the steps we performed to convert our candidate table from a standard table into an MOT.
- Renamed candidate table to [candidate_2]
- Created a memory-optimized filegroup for the MOT to live in
- Created the new MOT table with the same schema as [candidate_2] table
- Inserted all data from [candidate_2] into the new MOTs table
- Done
Issues and drawbacks from MOTs
There are a few drawbacks in using MOTs, but two that stand out to me the most are identity and foreign key “limitation”.
First, the identity columns can only be incremented by 1 for MOTs. It is a weird limitation, in my opinion. We rarely, if ever, have incremented an identity by anything other than 1.
The next limitation, foreign keys, is a bigger pain point and at this time most likely the biggest reason to stop the adoption of MOTs. You can have FK, with MOTs, but the table you are relating to must also be an MOT. Our candidate table doesn’t have any foreign keys (which kind of surprised me. It really should have a couple of them).
Some might ask why not make all the tables MOTs then? As stated previously there are a number of other limitations with MOTs for example triggers, default constraints, computed columns, etc. However, I would think that it might be a possibility one could do.
While researching MOTs I found several sources that stated ALTER TABLE couldn’t be used with MOTs. That would be a huge limitation for us as we are constantly changing the database to fit customer’s needs and wants. Testing this I was able to use ALTER TABLE to ADD and DROP columns without issue. In SQL Server Management Studio you couldn’t use the designer, but that isn’t a deal-breaker at least to us.
Results
As stated, previously our candidate table is hit very frequently by multiple clients. In addition, we have an existing testing platform in place to measure the latency in retrieving data from the candidate table.
The average latency measured with the standard SQL Server table in a controlled testing environment:
Average Latency: 687ms
The average latency with MOTs in the same controlled testing environment:
Averge Latency: 424ms
Conclusion
The MOTs did seem to perform better by the numbers, but I would be curious to see how this would perform in the wild with real users hitting the table. Stay tuned.
