In this blog post, we will discuss how to fix high CPU consumption on SQL Server 2016 and SQL Server 2017. High CPU consumption can significantly impact the performance of your SQL Server, causing slow query execution and overall system slowdown. Fortunately, there is a simple fix for this issue.

Part 1: Slow Server After Upgrade

Many organizations hold off on upgrading their critical databases due to known issues that often arise during the upgrade process. However, when the time comes to upgrade, there is an expectation that the new server configuration will improve performance. Unfortunately, this is not always the case.

One such organization recently upgraded their critical database to SQL Server 2017. Despite the superior server configuration and fresh installation, they experienced a significant decrease in performance. After weeks of troubleshooting, they decided to seek external help to tune their system.

As a database performance consultant, I have encountered similar scenarios multiple times. With my expertise, we were able to identify and fix the performance issues within a few hours. The organization was delighted with the results.

Part 2: High CPU Consumption

Two months later, the same organization reached out to me again. This time, they were not experiencing any performance issues, but they noticed a significant increase in CPU consumption compared to the previous month. They were curious to know if there was a specific reason behind this sudden change.

Upon further investigation, I discovered that the high CPU consumption was due to a new security feature introduced by Microsoft in SQL Server 2016 and 2017 called Common Criteria Compliance (CCC). While this feature is effective for auditing purposes, it adds overhead to every single insert operation executed outside of a transaction.

The solution to this issue is straightforward. By installing the latest SQL Server 2017 Cumulative Update, the high CPU consumption problem can be resolved. For those using SQL Server 2016, the latest SQL Server 2016 Cumulative Update needs to be installed, along with enabling trace flag 3427.

It is important to note that if your SQL Server 2016 or SQL Server 2017 is running slow after an upgrade, it is recommended to follow all the steps listed in the Comprehensive Database Performance Health Check. Installing the Cumulative Update will address the high CPU consumption in one specific condition.

If you are facing this issue, feel free to reach out to me for assistance. I am here to help you optimize your SQL Server performance.