SQL 2016 - It Just Runs Faster: Column Store Uses Vector Instructions (SSE/AVX)

  • Article

SQL Server's Column Store feature is designed to accommodate large amounts of data and associated compression capabilities. Microsoft studied numerous data layouts associated with column store and the CPU instructions required to process that data. Various algorithms have been optimized throughout the column store code base.

 

A specific, SQL Server 2016 enhancement is the use of vector based hardware capabilities. When compressing, building dictionaries and processing data a vectored design is leveraged. SQL Server 2016 detects the level of CPU support for the following extensions and when supported SQL Server 2016 takes full advantage of the hardware scalability and performance capabilities.

 

SQL Server uses the order of the following list to select the hardware extension.

 

  1. AVX 2 (Advanced Vector Extensions)
  2. SSE 4 (Streaming SIMD Extensions 4)
  3. AVX and SSE are not supported: Software based, using common CPU instruction set

 

'It Just Runs Faster' - SQL Server 2016 detects the CPU capabilities for AVX or SSE and leverages the hardware, based vector capabilities to improve scalability and performance.

 

Bob Dorr - Principal SQL Server Software Engineer

Comments

  • Anonymous
    April 24, 2016
    Any estimations concerning the performance gains from SIMD optimization?
  • Anonymous
    April 27, 2016
    It varies based on data and hardware but in some specific queries more than 20%
  • Anonymous
    April 28, 2016
    Does the Columnstore code use older versions of SSE if SSE 4.1 is not supported (for older processors).
  • Anonymous
    May 03, 2016
    This white paper contains some technical detailshttp://www.vldb.org/pvldb/vol8/p1740-Larson.pdf