Upgrading SQL Server–Mitigating Performance Regressions

As I discussed yesterday, it is sometimes possible to experience performance regressions after upgrading to a new version of SQL Server, even when it is running on a new, presumably faster database server. This is much less likely to happen if you have done a good job of analysis, planning, testing, benchmarking, and post-migration configuration activity, but it is still possible.

Hardware Selection and Configuration

Depending on the age and hardware specifications of your new database server compared to your old database server, it is definitely possible for a new database server to be significantly slower (in terms of single-threaded CPU performance) than your old server was. For example, lets say that your old database server had two eight-core Intel Xeon E5-2667 v2 processors, while your new database server has two eight-core Intel Xeon Silver 4108 processors. Even though the Xeon Silver is three generations newer than your old Xeon E5 v2, there is a huge base clock speed difference between the two specific processors in question. The Xeon E5-2667 v2 has a base clock speed of 3.3 GHz, while the Xeon Silver 4108 has a base clock speed of 1.8 GHz, so even with the generational improvements, the newer processor is not going to be nearly as fast for single-threaded performance. That particular new processor was a very bad choice for SQL Server, but if you have been reading my blog, I don’t think you would make such a poor choice!

From a configuration perspective, you want to make sure that you have the appropriate BIOS settings on your new database server. You want to make sure that power management is set to OS control or disabled, and you want to make sure that NUMA has not been disabled. You also want to make sure that Turbo Boost is enabled and that Hyper-Threading is enabled (except in some types of reporting/DW workloads).

If your server is a virtualization host, you want to make sure that VT-x and VT-d are enabled so that you can take advantage of the virtualization support built into your processor. Speaking of that, newer generation Intel processors have much better virtualization performance compared to legacy processors, which is another reason to upgrade your hardware. You also want to make sure that your hypervisor is using the High Performance power policy.

You need to make sure that Windows Server is using the High Performance Power Plan rather than the default Balanced Power Plan. After doing all of this, you should run CPU-Z to confirm how fast your processors are running and whether Turbo Boost is being used or not. While you are at it, you can use the CPU benchmark feature in CPU-Z to run a quick CPU test on your old database server compared to your new database server.

SQL Server Configuration and Performance

After validating your hardware configuration and performance, you will need to start looking at how your new SQL Server instance is configured and how it is performing. Fortunately, SQL Server 2016 and newer do a much better job of instance configuration during the installation process, so there are less things that need to be changed after a typical installation compared to older versions of SQL Server.

Microsoft has a great document called “Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads” that is full of useful settings to consider and perhaps change based on your workload. Even though Microsoft claims that SQL Server 2016 doesn’t really need as much manual configuration as previous versions, looking at that document is still a useful exercise.

Microsoft also has a very useful Post-migration Validation and Optimization Guide which is a pretty good starting point for diagnosing and alleviating post-migration performance issues.

Additional Resources

My new Pluralsight course, SQL Server: Upgrading and Migrating to SQL Server 2016 has just been published. This is my eleventh course for Pluralsight, but the complete list of my courses is here.

Building on this online course is a new three day class, IEUpgrade: Immersion Event on Upgrading SQL Server, taught by myself and Tim Radney. The first round of this course will be taught in Chicago from October 11-13, 2017.

Finally, I will be presenting a half-day session called Migrating to SQL Server 2017 at the PASS Summit 2017 in Seattle, WA from October 31- November 3, 2017.

Here is a link to the complete series about upgrading SQL Server.