Last time my tests showed no major difference on simple inserting between disk-based table and memory-optimized table. On the opposite side, there is a Microsoft published script and test process to prove how much performance gain we can get using memory-optimized tables. This test, shows positive performance gains. Below is the test result I got from my VM:
This test had been done under different workload (5K, 10K and 15K) and different parallel connections (10 threads, 20 threads and 30 threads). The test result of this test shows memory-optimized table is actually faster than disk-based table, and it’s much more scalable than disk-based table.
Where did the difference come from? In Microsoft’s testing script, all the inserts are done through stored procedures, either native compiled procedures or traditional stored procedures. The performance gain we saw in this test is actually coming from compiled procedures. It also demonstrates traditional stored procedure is less memory-friendly!
Microsoft’s testing script can be downloaded here: http://msftdbprodsamples.codeplex.com/releases/view/114491
Memory-optimized tables didn’t show much performance gain during the test last time. So I have decided to do more tests – just inserting tests and see how well memory-optimized tables perform.
Last time my VM had 2GB RAM and memory-optimized table could only hold 150K rows. To be able to test more cases, I added another 6GB and made it 8GB this time. In the meantime, I have also added 3 more logical CPU cores.
The tests I’m going to do this time is to compare insert performance of memory-optimized tables and disk-based tables under different workload: 50K, 100K, 150K, 200K, 250K and 300K row inserts. And I’m going to run these tests 3 times:
The results seem to be consistent – memory-optimized table showed performance gain only under 200K to 300K workload. For less heavy workload (<200K), disk-based table can perform just as well as memory-optimize table.
From this test, memory-optimized table seems to be working better under heavy workload. To prove this, I once again, added 4 GB to the testing VM and expand the test row set up to 450K. Again, 3 times:
The results are close – disk-optimized table can perform just as well as memory-optimized table under given workload. Unfortunately, I don’t have a power server handy for more tests. For now, I will consider memory-optimized table has no apparent performance gain over traditional disk-based table.