Since January 3, 2018, there has been a lot of conflicting and possibly alarming information published about the speculative execution side-channel vulnerabilities also known as Meltdown and Spectre that affect most modern processors in varying degrees. The Meltdown exploit (CVE-2017-5754) in particular only affects Intel processors. Protecting your systems from these vulnerabilities involves a number of steps for most systems depending on the environment that SQL Server is running in and what functionality is being used.
The guiding principle is that security concerns should override performance concerns. Ignoring these security vulnerabilities and not doing the required patching because of possible performance issues would be a big mistake (and possible legal liability) for most organizations. There has already been published research on new variations of Meltdown and Spectre, so this type of issue is not going to disappear anytime soon. Additionally, security vendors have been reporting evidence of Spectre/Meltdown attacks in the wild.
Unfortunately, deciding what you should actually do to your systems to protect them against these vulnerabilities has been increasingly complex and confusing as time has gone on, with changing information about the vulnerability being released by Intel and AMD, and with both CPU microcode and operating system patches being released and then shortly later being withdrawn.
Performance Impact of Patching
Depending on your server hardware, operating system, workload, and which patches you end up installing, it is likely that you will see some negative performance impact from these mitigation measures. Microsoft’s Terry Myerson has a pretty detailed post on this subject, while Netflix’s Brendan Gregg has some detailed results on Linux. Brandon Lee has done some synthetic benchmark testing in a VMware environment here.
The good news is that most performance regressions that result from this patching effort can be reduced with proper configuration and workload tuning in SQL Server. Using monitoring products like SentryOne’s SQL Sentry can help you identify the performance bottlenecks that exist in your environment.
For many organizations, getting fully current on their deployed SQL Server build (as a side effect of patching for Spectre/Meltdown) will solve many other issues and potentially improve their performance enough to help compensate for any performance regressions they see from the complete set of Spectre/Meltdown patches. Reading the fix list for every SQL Server CU usually reveals a number of performance-related fixes that can have a substantial performance impact for SQL Server.
Modern Intel processors have PCID and INVPCID support, which significantly reduces the performance impact of the Meltdown operating system patch. This means you will get Windows OS support for the PCID performance optimization in Intel Xeon E5-2600 v3 product family (Haswell-EP) and later processors, along with the Intel Xeon E7 v3 product family (Haswell-EX) and later processors.
If your Intel processors are older than the Haswell microarchitecture (which was released in Q3 2014 for two-socket servers), this gives you all the more reason to be planning for a hardware upgrade. I have written about how to use Microsoft CoreInfo to easily check whether your processor has PCID and INVPCID support. I have also done some synthetic benchmark testing on a newer Intel Kaby Lake system.
Microsoft has a new Windows Analytics feature that you can use to check the Spectre/Meltdown patch status of all of your machines. Microsoft also has a PowerShell module that you can use to check the overall patch status (from a Windows and hardware perspective) that I discussed here. If you want to do a quick and easy check of a client operating system for an end-user (or your Mom) without having to deal with PoSH, you can download and run the InSpectre utility (with an easy GUI) to check the patch status of your operating system and your processor microcode.
Checking Your SQL Server Instance
Finally, you need to check your SQL Server patch status. I have developed a T-SQL script that will check your SQL Server instance to see whether you have installed the relevant SQL Server patches or not. This script will work on SQL Server 2008 through SQL Server 2017 for on-premises instances or for Azure IaaS instances. This is not designed to work on Azure SQL Database. You can download it here.
One possible benefit of this issue is that it may give you more justification for getting your organization to get your SQL Server instances current with their Service Pack and Cumulative Updates, which is what Microsoft explicitly recommends anyway.
Spectre/Meltdown Mitigation Steps
Here are the mitigation steps you should strongly consider taking:
- Install the relevant operating system patch from Microsoft (if available)
- Available for Windows Server, version 1709, Windows Server 2016, Windows Server 2012 R2, and Windows Server 2008 R2
- Not yet available for Windows Server 2012 or Windows Server 2008 (as of February 15, 2018)
- Make the necessary configuration changes (registry settings) to enable the operating system protection on server operating systems
- If you are running on a Hypervisor, install the relevant Hypervisor patches
- Install the relevant SQL Server patch from Microsoft
- Available for SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012 SP4, SQL Server 2008 R2 SP3 and SQL Server 2008 SP4
- Not available for SQL Server 2005 or earlier
- Install a BIOS update (which has a CPU microcode update) from your server vendor (if available)
- This depends on what processor you are using, along with your environment and feature usage
- These BIOS updates are currently not available for most servers (they were initially released for some newer servers and then withdrawn)
- Evaluate what SQL Server extensibility features you may be using and what additional mitigation steps you may need to take. These include:
- SQL CLR assemblies
- R and Python packages running through the external scripts mechanism or run from the standalone R/Machine Learning studio on the same physical machine as SQL Server
- SQL Agent extensibility points running on the same physical machine as SQL Server (ActiveX scripts)
- Non-Microsoft OLE DB providers used in Linked Servers
- Non-Microsoft Extended Stored Procedures
- COM objects executed within the server (accessed via sp_OACreate)
- Programs executed via xp_cmdshell
Thank you for the detailed post. I have two questions. 1) Do I need to follow the order mentioned in "Spectre/Meltdown Mitigation Steps"? i.e. what if I apply SQL Server patches first and then windows and other patches later. Does the sequence matter? 2) What if OS Updates and KVAS are not applied and only SQL Server patches are applied? Does it still give you protection at SQL Server and are there any downsides of it?