How It Works: SQL Server 2016 SSE/AVX Support

My recent posts

• https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-column-store-uses-vector-instructions-sseavx/
• https://blogs.msdn.microsoft.com/bobsql/2016/06/03/sql-2016-it-just-runs-faster-bulk-insert-uses-vector-instructions-sseavx/

have generated discussions about the SSE and AVX support boundaries.

As indicated, SQL Server 2016 added the use of SSE and AVX instructions in various code paths to improve performance.  The inquiries have centered around the support boundaries of the SSE and AVX instructions.   This post will outline SQL Server’s decisions around SSE/AVX or software algorithm use.

SSE/AVX

SQL Server asks the CPU if it supports SSE or AVX in order to determine the level of hardware support present on the system.  The SOS layer added helper functions which invoke the CPUID instruction to determine the level of support.  The CPU support intrinsic __cpuidex is called to retrieve instruction set support information.  Based on the returned information the type of support is determined.

// Information = 1
hasSSE = ((functionID1_EDX >> 25) & 1) > 0;
hasSSE2 = ((functionID1_EDX >> 26) & 1) > 0;
hasSSE3 = (functionID1_ECX & 1) > 0;
hasSSSE3 = ((functionID1_ECX >> 9) & 1) > 0;
hasSSE41 = ((functionID1_ECX >> 19) & 1) > 0;
hasSSE42 = ((functionID1_ECX >> 20) & 1) > 0;
hasAVX = ((functionID1_ECX >> 28) & 1) > 0;

// Information = 7
hasAVX2 = ((functionID7_EBX >> 5) & 1) > 0;
hasPOPCNT = ((functionID1_ECX >> 23) & 1) > 0;
hasBMI1 = ((functionID7_EBX >> 3) & 1) > 0;
hasBMI2 = ((functionID7_EBX >> 8) & 1) > 0;

For AVX/AVX2 the CPU must also indicate that bit 6 is enabled from the _xgetbv call, _XCR_XFEATURE_ENABLED_MASK lookup.

For Row Bucketing in Column Store

• If AVX2 or AVX use AVX
• Else If SSE42 or below use SSE
• Else use Software algorithms

Bulk Import

SSE4 and Below – Use SSE for seeking delimiters and string to integer conversions

Numeric from String

Any string to numeric conversion.

SSE4 and Below – Use SSE for converting strings to numeric values

Encryption

The use of on-board encryption and possible SSE/AVX usage is all determined by Windows.  Windows 2012 R2 and newer releases have been enlightened the Crypto* APIs.  Version of SQL Server 2012, 2014, and 2016 using the Crypto* APIs take advantage of the hardware implementations through the API support.  Reference APIs such as: CryptDecrypt

Reference: AVX vs SSE instructions

Bob Dorr - Principal Software Engineer SQL Server

Comments

• Anonymous
  June 09, 2016
  Check out the great work from Glenn related to this subject:http://www.sqlskills.com/blogs/glenn/hardware-extension-support-in-sql-server-2016/