SQL SERVER – How to Fix High CPU Consumption on SQL Server 2017 and 2016

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.

SQL SERVER - How to Fix High CPU Consumption on SQL Server 2017 and 2016 highcpu-800x257

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)

, , , , ,
Previous Post
[Exclusive] Practical Real World Performance Tuning – Live Training Session for Limited Time
Next Post
SQLPASS Pre-Con: 21 Essential Scripts: Jump-Start Performance Tuning for Accidental DBAs

Related Posts

10 Comments. Leave new

  • I’m glad I read your daily blogs Pinal. I never knew about Common Criteria in SQL Server.

    Reply
  • Hi Pinal

    Thank you very much for letting us know about this problem and solution …much appreciated

    Is it safe to just disable the CCC option in the server configuration until the CU can be installed preferably outside business hours.

    Reply
    • CCC is more for compliance purpose. So, you really need to check with your security team if they are OK to disable that.

      Reply
  • Anshuman Saini
    June 30, 2018 3:31 pm

    Hi , So its good to have disable this trace -CCC ,isnt

    Reply
  • hi
    quick question
    We have sql server 2016 sp2 cu3 applied do you think by enabling traceflag 3427 will help to control the high cpu issue any suggestions will help

    Reply
  • Hi Pinal,

    If CCC is disabled, Will it still contribute for CPU high intensiveness?

    Regards,
    Chella

    Reply
  • Do you have to enable trace flag 3427 on the 2016 SP2 CU8? as this seems not applicable on the latest CUs

    Reply
  • Hi Pinal,

    I have same issue while upgrade from sql server 2016 to sql server 2019 on azure vm previously i was using the rackspace server with sql 2016 it’s working fine please help

    Reply
  • Hi we are using sql server 2017 and it takes around 100 gb usage of my server ? what should i do ?

    Reply

Leave a Reply

Menu