Introduction
Back in 2016, Microsoft’s Bob Dorr consolidated a series of CSS posts about many low-level performance improvements in SQL Server 2016. One of these, How It Works: SQL Server 2016 SSE/AVX Support talks about how SQL Server 2016 added the use of SSE and AVX instructions in various code paths to improve performance when your processor supports one of those instruction sets. This post will review Hardware Instruction Support in SQL Server 2016.
You might be thinking this is not relevant anymore, since SQL Server 2016 is pretty old, and is out of Mainstream Support. That is true, but SQL Server 2022 has some similar improvements related to AVX-512 support in newer processors. I’m going to talk about that in a subsequent post, but let’s review what Microsoft did in SQL Server 2016.
Hardware Instruction Support in SQL Server 2016
Unless you are a hardware enthusiast, you might be wondering what these instructions are and how you can determine whether your processor supports them or not. First, we have some basic definitions:
Advanced Vector Extensions 2
Advanced Vector Extensions 2 (AVX2) is a newer version of AVX that was introduced with the Intel Haswell-EP/EX architecture. If you have a Haswell-EP/EX, Broadwell-EP/EX or newer processor, then you will have AVX2 support.
Advanced Vector Extensions
Advanced Vector Extensions (AVX) were introduced in the server space with the Intel Sandy Bridge-EP architecture, which was released in Q1 2012. This was the Intel Xeon E5-2600 Product Family.
This means that if you have a server with Intel Xeon E5-2600 (Sandy Bridge-EP), Intel Xeon E5-2600 v2 (Ivy Bridge-EP), Intel Xeon E5-2600 v3 (Haswell-EP), Intel Xeon E5-2600 v4 (Broadwell-EP) or newer processors, you will have AVX support which SQL Server 2016 will use for better performance in some scenarios.
You will also have AVX support with the Intel Xeon E7-4800/8800 v2 (Ivy Bridge-EX), Intel Xeon E7-4800/8800 v3 (Haswell_EX), Intel Xeon E7-4800/8800 v4 (Broadwell-EX) and newer product families. Any Intel processor older than these families will not have AVX support. This was yet another reason to use a new server when you upgraded to SQL Server 2016 (which was a good idea anyway for lots of other reasons).
Streaming SIMD Extensions
Streaming SIMD Extensions (SSE) are a much older instruction set that were introduced back in 1999. Since then, there have been several newer versions such as SSE2, SSE3, SSE4.1, and SSE4.2. The latest SSE4.2 version was introduced in the Nehalem-EP/EX architecture, which came out in Q1 2009 and Q1 2010 respectively. This means that the Intel Xeon 5500 (Nehalem-EP) and Intel Xeon 7500 (Nehalem-EX) Product families are the oldest models that will have SSE4.2 support. From Bob’s post, it appears that the same code path in SQL Server 2016 will be used if you have any version of SSE (but you don’t have AVX or AVX2 support), so even much older Xeon processors will have some level of SSE support. Just to be clear, AVX and AVX2 are newer and better than any version of SSE.
The CPU-Z utility shows the various extension instructions supported by your processor. As you can see in Figure 1, the Intel Xeon E5-2690 supports the AVX instruction set.
Figure 1: Intel Xeon E5-2690 CPU Details
A 2016-vintage Intel Core i7-6700K Skylake desktop processor supports the newer FMA, and Transactional Synchronization Extensions (TSX) instructions, as shown in Figure 2. These were both introduced with the Haswell microarchitecture, and they might have been useful in a later version of SQL Server. As it turned out, TSX was affected by security issues in 2018, and they are usually disabled in later BIOS updates.
Figure 2: Intel Core i7-6700K CPU Details
Final Words
You might have noticed that I did not mention AMD server processors at all in this discussion. That is because back when SQL Server 2016 was released, AMD was still selling their Opteron server processors, which were extremely bad for SQL Server usage. Despite that, AMD Opteron processors that were available after 2011 have SSE and AVX support.
The upcoming AMD EPYC Genoa processors will have AVX-512 support, which will be useful for SQL Server 2022.
If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!