Rakesh Patil

Three Steps to quickly solve SQL Server High CPU usage

September 20, 2018 by

Introduction

SQL High CPU is one of the most important performance issues when monitoring SQL Server

High CPU usage in SQL Server issue may be caused by slow query performance, load on server and/or some configuration settings which affects entire application performance also.

Any High SQL Server CPU usage may be caused due to issues related to Windows (OS) or SQL Server itself

In this article, we will go through, how to resolve or fix High CPU usage from SQL Server side.

Check Fragmentation on Database tables

First check, which are major databases for your SQL instances. Run SP_Helpdb in query optimizer which will provide you information about all database size information as below.

Run the script below to check fragmented table indexes for databases as below

As per output,If avg_fragmentation_in_percent >30% and page_count >1000 then Rebuild Indexes

E.g.

If avg_fragmentation_in_percent <30% then Reorganize Indexes

E.g.

Update Statistics for Table

To improve he query execution plan, the query optimizer automatically updates statistics as necessary. When statistics are updated, the query optimizer provides an accurate execution plan which helps to read data more efficiently.

Use the below script to check outdated Statistics for tables of databases.

Check the LastUpdated column and update statistics of tables which are not updated since many days as shown above fig.

Use the below script to update statistics in above cases

Check value of Max Degree of Parallelism & Cost Threshold for Parallelism

When SQL Server runs on a computer with more than one processor or CPU, that is the number of processors employed to run a single statement, for each query that has a parallel execution plan. You can use the max degree of parallelism option to limit the number of processors to use for parallel plan execution and to prevent run-away queries from impacting SQL Server performance by using all available CPUs.

If Max degree Parallelism (MaxDop) is zero, means it is taking all processor to run single SQL statement in parallel.

How to check MaxDOP & Cost Threshold for Parallelism value

Right Click SQL Instance > Go to Properties > Advanced

There are two main reasons that we need to consider for High CPU

  • Cost threshold for parallelism
  • Max degree parallelism

Cost threshold for parallelism

Set Cost Threshold for Parallelism value to 50

Max degree parallelism

How to decide Max Degree Parallelism value?

Run the below script on your SQL instance, which will advise you, what value of MAXDOP is suitable for your server and set that value

Once the above script has been executed, it will provide suggestions for MAXDOP value.

So the next time you experience high SQL Server CPU usage, hopefully these techniques will help!

See more

To get 3 free licenses to a SQL Server monitoring tool, download ApexSQL Monitor and fill out this simple survey




 

Rakesh Patil

Rakesh Patil

Data Architect at Phoenix Innovations
I am Rakesh Patil, having 6+ years of experience in SQL database Administration, worked with Top MNCs with major Financial clients. currently working as Data Architect with Phoenix Innovations.
I have hands-on Experience in Performance tunning, Database Disaster recovery,
AlwaysOn,Replication and AWS.
Rakesh Patil
Performance tuning

About Rakesh Patil

I am Rakesh Patil, having 6+ years of experience in SQL database Administration, worked with Top MNCs with major Financial clients. currently working as Data Architect with Phoenix Innovations. I have hands-on Experience in Performance tunning, Database Disaster recovery, AlwaysOn,Replication and AWS.

2,036 Views