In this blog post, we are going to discuss how to fix high CPU Consumption on SQL Server 2016 and SQL Server 2017. One of the large multinational corporations recently hired me for Comprehensive Database Performance Health Check. Usually, customer hires me once and we are able to fix all of their problems in very little time. However, this customer had a very unique scenario and I had to engage twice to help them out.
Let us first understand their problem statement and later on we will see the simple fix to their problem.
Part 1: Slow Server After Upgrade
The customer had a very critical database which they were holding off to upgrade for a while. It is very common practice for organizations to hold off upgrades as there are many known issues often surfaces. Last month they decided to upgrade their critical database to SQL Server 2017.
The new server configuration was very much superior to the previous server. Additionally, as it was fresh install they were very much confident that the performance of the server will be better.
However, in reality, the server was much much slower than before. It was pretty crawling compared to the older installation. After spending a couple of weeks, they decided to hire me to help them to tune their system.
Well, this is a very common issue I have observed across most of the upgrades. I have done similar consultancy multiple times Comprehensive Database Performance Health Check so I exactly knew how to fix these issues.
After 3 hours of our engagement, we were able to fix almost all of their performance issues. They were pretty delighted.
However, after 2 months, I have received their another email.
Part 2: High CPU Consumption
The same organization hired me again after two months. It was a bit strange for me as well as their all the queries were running faster as well they were facing absolutely no performance issues at all. When I asked how come they have hired me whereas they have no performance issue, they mentioned that while they compared their last months CPU consumption they realized it is much higher than before.
Though their query performance was much better due to consulting done in part 1, they were wondering if there was an obvious reason behind the high CPU consumption.
Honestly, when I had done part 1 of consulting, I was actually not aware of this solution as well. However, by the time when they return for further consultation, I was pretty familiar with the exact issue which they were mentioning.
The reason for the high CPU consumption was because Microsoft has introduced new security feature in SQL Server 2016 and 2017 known as Common Criteria Compliance (aka CCC). This new feature is pretty good at what it does at auditing but it also adds overhead to every single insert executing outside of the transaction.
It was very easy to fix this issue – we just had to install latest SQL Server 2017 Cumulative Update. If you are using SQL Server 2016 and you are facing the same issue you can also download and install the latest SQL Server 2016 Cumulative Update. However, for SQL Server 2016, you will also have to enable trace flag 3427.
Additional Note
Please note that if your SQL Server 2016 or SQL Server 2017 is running slow after an upgrade, you need to follow all the steps listed in the Comprehensive Database Performance Health Check. By just installing Cumulative Update, you help CPU in one specific condition.
Do reach out to me if you are facing this issue and I will be happy to help you!
Reference: Pinal Dave (https://blog.sqlauthority.com)
