After gaining approval to switch to new hardware (at last) and a newer SQL Server version, you start to make preparations. But what will you do if the performance worsens after you finally do the migration?
Hardware upgrades are often preferred over MySQL SQL performance tuning but the transition may not necessarily take place smoothly. In this post, we will consider five of the most common reasons why a hardware upgrade may actually worsen performance, and how to identify these reasons.
Hardware Upgrade Over MySQL SQL Performance Tuning: 5 Things to Check
Using a SQL Server query optimizer tool might not be the best bet here since the hardware has been changed. In such cases, consider the five most common ways given below to troubleshoot this problem:
Check a Few Sys.configurations Settings
The default setting for “max degree of parallelism” is zero in SQL Server. This means all the cores get used by queries that go parallel, so your workload on the new instance may be facing tasks struggling for CPU usage due to this setting.
You can find out if this is the case by comparing all the configurations in sys.configurations between instances and searching for anything that stands out. Some users might accidentally set the lowest memory value for a query.
Investigate for Bottlenecks in Hardware Settings
Certain unexpected situations can lead to a few problems that might not get detected until after hardware upgrading or migration. For instance, the server might not be receiving sufficient power, which can affect CPU operation as well. They become less capable and therefore, show greater utilization when checked in Windows Task Manager.
Identification can be done in two ways:
- Keep an eye on the Windows System log. You may find messages related to improperly plugged power sources.
- Try running a free tool to gauge the clock speed of the processors and whether all of them are operational. Different from a SQL Server query optimizer tool, you can find one online nevertheless.
Check if Your Processors are on Power Saving Mode
It might not be the sole cause behind greater CPU usage but you can expect it to be one of the elements contributing to the issue, aside from a need for MySQL SQL performance tuning. Fortunately, this is something you can resolve rather quickly, so it’s certainly worth a glance.
This setting can be checked in the BIOS – disable power savings in case it is enabled. However, you may have to confirm the change with the help of a server management tool such as HP System Insight Manager and a planned outage that will give you time to make the desired change in the BIOS and reboot the server.
Check the New SQL Server Version for Stack Dumps
A stack dump, also known as a “15-second error” is responsible for leaving the storage unresponsive for fifteen seconds, followed by increased load.
To check for a stack dump, you will have to open the SQL Server error log and look for errors in the span of some days. You can filter using the terms “15 seconds” or “stack dump” to locate the culprit.
Inspect Factors Outside SQL Server for CPU Usage
Although you may have already looked into this, experts recommend double-checking to ensure the database professional doesn’t miss anything. You may be surprised to see a user testing with the server or using a SQL Server query optimizer tool. They may neglect to inform you – or forget to revert to the original settings. Sometimes, users may forget about previously running tasks while a new instance goes live.
To find out what went wrong, open the Windows Task Manager to take a look at all the processes that are utilizing memory and CPU. There shouldn’t be any agent tasks or scheduled jobs that were absent on the previous server.