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 >>& 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
Uncategorized How It WorksSQL 2016
