SELECT Test Summary on SQL Server 2016

Q

What are performance differences of running the SELECT statement with different criteria and table storage options on SQL Server 2016?

✍: FYIcenter.com

A

Here is a summary of performance differences of running the SELECT statement with different criteria and table storage options on SQL Server 2016. Throughput is calculated as number of bytes retrieved per second in unit of MB/s.

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

              |                Throughput MB/s
 Size   Count | CURSOR  BIGINT  PRIMARY KEY  MEMORY_OPTIMIZED
------------------------------------------------------------- 
  256     100 |   1.53    1.88         8.14           
  256     500 |   9.39    1.11         3.39              7.63
  256    1000 |   7.40    0.54         2.37             15.26
  256    5000 |   7.83    0.12        19.38             16.06
  256   10000 |   8.25    0.18        19.85             14.11
  512   10000 |  15.60    0.28        38.15             25.84
 1024   10000 |  28.47    0.44        69.21             62.11
 2048   10000 |  54.25    0.37       125.20            114.89
 4096   10000 |  83.11    0.41       250.40            229.78
 8192   10000 | 119.09    0.79       384.48            384.48
16384   10000 | 227.77    1.44       557.76            769.33
32768   10000 | 434.03    2.80       868.06           1250.00
32768   20000 | 425.17    1.33       889.05           1329.79
32768   50000 |                      945.25           1284.95
32768  100000 |                      888.79           1235.18
32768  500000 |                      794.92           1101.13

As you can see:

  • The worst case is the BIGINT case, where the SELECT statement is executed with a criteria column that is not the primary key or indexed.
  • The best case is the MEMORY_OPTIMIZED case, where the SELECT statement is executed with the primary key as the criteria on an in-memory (MEMORY_OPTIMIZED) table.
  • Actually, the MEMORY_OPTIMIZED table only improve performance on tests larger than 10,000 records of 16,384 bytes per record.

 

? SQL Server Storage Tutorials

? SELECT with MEMORY_OPTIMIZED Table on SQL Server 2016

? I/O Tests on SELECT Statements

?? SQL Server Storage Tutorials

2019-05-15, 177👍, 0💬