SELECT with MEMORY_OPTIMIZED Table on SQL Server 2016

Q

What is the data read performance with the SELECT statement with a MEMORY_OPTIMIZED table on SQL Server 2016?

✍: FYIcenter.com

A

To see the data read performance with the SELECT statement with a MEMORY_OPTIMIZED table on SQL Server 2016, you can run the SQL script, fyi_select_in_memory.sql, presented in the last tutorial with different @Count and @Size.

The following table shows the summary of the test results. Some fields are removed to save space. additional fields are added with calculations on existing fields.

@@version = Microsoft SQL Server 2016 - 13.0.5292.0 (X64) 

 Size   Count  Duration        Bytes  Throughput MB/s 
  256     100         0        25600    
  256     500        16       128000             7.63
  256    1000        16       256000            15.26
  256    5000        76      1280000            16.06
  256   10000       173      2560000            14.11
  512   10000       186      5040000            25.84
 1024   10000       156     10160000            62.11
 2048   10000       170     20480000           114.89
 4096   10000       170     40960000           229.78
 8192   10000       203     81840000           384.48
16384   10000       203    163760000           769.33
32768   10000       250    327680000         1,250.00
32768   20000       470    655360000         1,329.79
32768   50000      1216   1638400000         1,284.95
32768  100000      2530   3276800000         1,235.18
32768  500000     14190  16384000000         1,101.13

As you can see the peak throughput of reading data, 1,329.79 MB/s reached at @Size=32768 and @Count=20000.

 

? SELECT Test Summary on SQL Server 2016

? Test Script for SELECT with MEMORY_OPTIMIZED

? I/O Tests on SELECT Statements

?? SQL Server Storage Tutorials

2019-05-25, 144👍, 0💬