18 January 2018
18 January 2018

How to monitor the impact of patching on SQL Server performance

In recent weeks, the IT industry has learned of two serious processor vulnerabilities, Meltdown and Spectre, which an attacker can exploit to obtain information to which they should not have access: specifically, information cached in the operating system kernel’s private memory.

I’ve included references at the end, for in-depth technical descriptions of the vulnerabilities, which result from fundamental CPU design flaws. In broad terms, it means that an unprivileged user could access information (passwords, encryption keys, confidential data) in one software program, from another, or in one virtual machine from another. These bugs can even be exploited from JavaScript.

High-profile bugs such as these serve to reinforce the fundamental responsibilities of the operations team on discovering any bug or vulnerability that could compromise the data in their care. Firstly, apply all appropriate patches as quickly as possible; protect the data. Secondly, assess the likely impact of patching on server performance and workload throughput. Thirdly, monitor the actual impact, and respond quickly if it is more severe than anticipated.

In this article, I use the Spectre/Meltdown bugs as means to demonstrate how you can use a tool like SQL Monitor to assess the impact of patching on your SQL Server estate.

Apply the security patches

The first and most immediate responsibility of the DBA in response to any discovered security bug is, of course, to apply all necessary OS and SQL Server patches to ensure that data is protected. As the ICO notes:

Failure to patch known vulnerabilities is a factor that the ICO takes into account when determining whether a breach of the seventh principle of the Data Protection Act is serious enough to warrant a civil monetary penalty.”

The usual advice applies: follow Microsoft’s recommendations about what security updates you should apply. In the case of the Meltdown and Spectre bugs, they have a good summary of what you should do in different scenarios, as patching isn’t necessarily required.

The most vulnerable environments are those that share a CPU with any code you don’t trust, possibly because you’re running in a virtualised environment where a different VM shares your underlying physical CPU.

Assess the likely impact of patching

The DBA’s next responsibility is to understand and predict any likely impact of the patches on throughput and performance. The patches for the Meltdown and Spectre bugs, for example, are known to hurt CPU usage, which will in turn impact performance. As a DBA, performance, regardless of the underlying cause, is ultimately your responsibility.

There are a few early benchmarks observing a performance impact of anywhere from 2-3%, ranging all the way up to 30%. The likely impact depends on the workload, and I’ve not done any benchmarking on SQL Server yet. However, as Terry Myerson points out in a recent update from Microsoft, patching in this case should have the greatest effect on workloads that cause significant IO, and a lot of context switching, back and forth with the kernel.

Given that SQL Server is an IO intensive application, you’d expect the impact of these patches on SQL Server to be meaningful, though you’re unlikely to see outcomes anywhere near as bad as that 30% figure (unless your server is CPU-bound).

In any case, it’s a good time to assess the size of CPU usage increase with which your servers could cope, without performance being affected significantly. Here, for example, is the CPU usage, over the last few months, for the machines that run SQLServerCentral.com. The busier primary machine usually hovers between 30 – 50% CPU usage, and rarely spikes above 60%.

The lucky DBAs will be running SQL Server on massively under-utilised hardware, providing room for any spikes in CPU activity. For example, StackOverflow’s database servers have an average 4% CPU usage / 15% peak.

Not everyone has that luxury, and servers that are already running hot, to minimize wasted capacity, or for some other reason, are likely to be most vulnerable to degraded performance. It’s a good idea to stay on top of figures like this so you know where you stand.

Monitor the real impact carefully

You’ll find it much easier to establish proper baselines for important SQL Server performance metrics if you use a monitoring tool. These metrics often vary throughout a day, week, or month in semi-predictable patterns, so looking at simple averages doesn’t cut it – you need to understand how these patterns have changed.

Baselining shouldn’t usually be limited to examining CPU usage, though in the case of Meltdown it’s the first thing I’d recommend examining. Here for example, SQL Monitor shows how a 24-hour period of CPU usage on that same SQLServerCentral.com machine, compared with several previous days’ figures.

When we patch this machine, the very first thing we’ll do is explore some metrics like this to make sure that any increase we see is within the bounds of acceptable. It looks like this machine will have enough spare capacity to cope, but in other environments we might need to consider moving some of the workload onto separate hardware, or looking at other performance tuning options.

A monitoring tool will help with performance impact analysis

When you make any change, whether to apply an OS or SQL Server patch, make a configuration change, or perform an application deployment, you should consider before-and-after performance. Of course, this rule is especially relevant when a patch, or any other change, comes wrapped in a big red “may hurt performance” warning.

Once a monitoring tool like SQL Monitor is up and running, it builds up a history of easily-visualisable diagnostic data. This means that when you need to make an urgent and significant change to the system, such as applying patches for bugs like Meltdown and Spectre, you’re able to see its impact. You can’t retrospectively build up that historic data on demand, so the best time to set something up is now.

As well as the baselining capabilities shown here, also available for dozens of other metrics, and other deep drill-down capabilities, SQL Monitor lets you build and schedule detailed reports. These help you understand the health of your entire SQL Server estate, while highly configurable alerts ensure you’ll know about unexpected behaviour whether you’re actively looking or not. In this case, we’d have quickly known about it if CPU usage went above or below thresholds for a sustained period.

You can download a free 14-day trial of SQL Monitor to start tracking an unlimited number of servers. And if you’d like any help, just get in touch.

Further Reading on Meltdown/Spectre

High-level:

  • http://www.theregister.co.uk/2018/01/04/intel_amd_arm_cpu_vulnerability/
  • http://sqlha.com/2018/01/04/no-good-terrible-processor-flaw-sql-server-deployments-nearly-everything-need-know/
  • https://iconewsblog.org.uk/2018/01/05/meltdown-and-spectre/

In-depth:

  • https://www.sqlpassion.at/archive/2018/01/06/understanding-the-meltdown-exploit-in-my-own-simple-words/
  • https://googleprojectzero.blogspot.co.uk/2018/01/reading-privileged-memory-with-side.html

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Spotting Unauthorized Configuration Settings in SQL Server

    An attacker of SQL Server likes to be able to change the SQL Server configuration settings. In an ideal world, you will have left everything open for the intruder, but generally, every DBA reduces the surface of attack as much as possible. Why would the attacker want to change these settings? You might think there’s

  • Webinar

    An introduction to distributed SQL Server monitoring

    As estates increase both in size and complexity, keeping on top of everything becomes both important and difficult. In this webinar, we talk you through the basics of distributed monitoring, and how you can set up a solution to help you keep on top of your entire SQL Server estate, regardless of how complex it becomes.

  • Webinar

    Extending DevOps to the database: branching and merging

    Join our webinar to learn how Redgate’s Database DevOps solution works to improve your database development and deployment processes. With a focus on branching and merging, we’ll show how Redgate tools plug into Visual Studio Team Services (VSTS) to enable the build, test and deployment of your database changes.

  • Article

    Using SQL Monitor with SCOM

    In my last blog post, I talked about how to get SQL Monitor to send alerts to Slack and how to send SNMP Traps. It’s easy to make SQL Monitor send SNMP traps, but SCOM is more challenging than most management tools to configure to receive these traps. This walkthrough shows how to configure SCOM

  • University

    Take the SQL Monitor course

    This course takes you from installation and configuration, all the way up to getting the most out of the advanced features in SQL Monitor to help you proactively monitor your SQL Server estate. Learn how to explore in-depth issues in your environment, run an analysis, manage alerts, create custom metrics, and more.

  • Forums

    SQL Monitor Forum

    Real-time SQL Server performance monitoring, with alerts and diagnostics