MSSQLWIKI

Karthick P.K on SQL Server

Troubleshooting SQL Server high CPU usage

Posted by Karthick P.K on October 4, 2012

Troubleshooting SQL Server high CPU usage

 

First thing to determine when there is High CPU on systems is, if SQL server is consuming the CPU resource or other applications/service.

 

Use query in  THIS LINK to get CPU usage history (or) Task manager (or) Perfmon counter to determine that. In Perfmon, Process %Process time can also be used. Remember this counter is not based on 100%.  It is based on number of processor.  If you see 200 for sqlservr.exe and the system has 8 CPU, CPU consumed by sqlservr.exe is 200 out of 800 (only 25%).)

 

If the CPU spike is caused by other application involve application team.

 

Next step is to determine if the CPU consumed is kernel time or user time.

 

We can use Process %Privileged  time and %user Time counters in perfmon. Task manager will show kernel times which will also help us understand

 

Kernel CPU:  In general, if kernel CPU remains below 10%, it’s normal.  But if you see sustained kernel CPU at 30% or above, you should start looking at system drivers , Antivirus etc.  some known issues which can increase Kernel CPU time are

1.       Few Anti-virus software’s can cause high kernel time.  Temporarily disable anti-virus software to rule this out

 

2.       We have seen high resolution timer in SQL 2008 or SQL 2005 SP3 caused high kernel time in Virtual Machines because of outdated BIOS .  Temporarily disabling high resolution timer by turning on trace flag 8038 (configure as startup parameter) to prove this. Check for BIOS update and do not use 8038 in long term.

 

High user CPU: Some of the most common causes for High CPU in SQL Server  are

1.       Query execution causing CPU spike (Most commonly caused by optimizer picking bad plan).

 

2.       High compiles and recompiles. (schema, Stats change, Use of Temp table, Recompile hint).

 

3.       System threads spiking CPU (Ghost cleanup, Lazy writer, Resource monitor).

 

4.       Running many traces.

 

 

1. Query execution causing CPU spike:

 

Query execution  takes long times and spikes CPU commonly because of in-correct cardinality estimates caused by outdated statistics, Lack of Index, Server configuration, Distributed queries, etc.

 

When the server is experiencing this problem run the query in below link to list all the queries which are executing in the server order by CPU time desc along with plan.

{

Get SQL Text and Query Plan for statements which are executing now

}

 It could be one query which is driving the majority CPU time or Multiple queries each driving the CPU. Look at the CPU time of the above query output.

 

If it is single query/Store procedure which is driving the majority of CPU.

 

1.        Update the stats of tables and indexes used by the query (If the stats are up to date Estimated rows and estimated execution will  be approximately

same in execution plan .If there is huge difference stats are out dated and requires update) .

 

2.       Identify if the query has used bad plan because of parameter sniffing (If the ParameterCompiledValue and ParameterRuntimeValue is different in XML plan). Refer THIS LINK to know more about Parameter Sniffing

 

3.        If updating the stats and fixing the parameter sniffing doesn’t resolve the issue it is more likely optimizer is not able to create efficient plan because of lack of indexes and correct statistics. Run the query which is driving the CPU in database tuning advisor and apply the recommendations. (You will find missing index detail in xml plan but DTA is more efficient). You can follow the steps in Tune queries using SQL Server Database tuning advisor .

 

4.       If the query which is spiking the CPU is linked server query try changing the security of linked server to ensure linked server user has ddl_admin or dba/sysadmin on the remote server. More details regarding the issue in THIS LINK.

 

5.       Ensure optimizer is not aborting early and creating bad plan. For details refer THIS LINK.

 

6.       Ensure the query which is spiking the CPU doesn’t have plan guides (xml plan will have PlanGuideDB attribute.  Also sys.plan_guides will have entries) and query hints(index= or (option XXX join) or inner (Join Hint) join).

 

7. Ensure that SET options are not changed.

 

If it is Multiple queries/Store procedure are driving the CPU together.

 

1.       Update the stats of all the tables and indexes in the database. Using the query in below link Rebuild index and update statistics for all the tables in database

 

2.       If updating stats doesn’t help and rebuilding the indexes doesn’t bring down the CPU we have to tune the queries 1 by 1.

 

3.       Ensure Large amount of RAM is not causing optimizer to choose inefficient plan http://support.microsoft.com/kb/2413549

 

4.       Ensure that we do not run many traces at same time (commonly from monitoring tools). Use query in below link to list all the active traces.

{

Find all the profiler traces running on SQL Server

}

 

2. If the system thread is consuming most of the CPU.

 

1.       If none of the SQL queries are consuming majority of CPU,  we can identify if the back ground threads is consuming the majority of CPU by looking at  sysprocesses output for background threads. select * from sys.sysprocesses where spid<51.

 

2.       Check if you are hitting any of the known issues.

{

Resource Monitor may consume high CPU: http://support.microsoft.com/kb/968722

The Ghost Cleanup task uses 100% of the CPU on an idle system in SQL Server 2008 or in SQL Server 2005: http://support.microsoft.com/?id=978430

}

 

3. High compiles and recompiles: I will blog about high compiles and recompiles shortly

                               

4. Other factors which can impact SQL Server query performance

1.       Maximum degree of parallelism. Ensure MAX DOP is set properly (you can follow the steps in How to set Max degree of parallelism (MAXDOP)

 

2.       Priority boost. (Do not enable priority boot)

 

3.       Do not enable Fiber mode.

 

4.       Tweaking affinity mask (Spikes few CPU).

 

5.       TokenAndPermUserStore. http://support.microsoft.com/kb/927396

 

6.       CPU power plan degrade the server performance http://support.microsoft.com/kb/2207548

 

7.       SQL Server that’s uses  .Net Framework can cause high CPU Refer THIS LINK

 

 

If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group

Related blogs: 

Tuning SQL Server query

Thank you,

Karthick P.K |My Facebook Page |My Site| Blog space| Twitter

Disclaimer

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

About these ads

11 Responses to “Troubleshooting SQL Server high CPU usage”

  1. [...] Troubleshooting SQL Server high CPU usage [...]

  2. [...] Query to find SQL Server CPU utilization 2012. This query will help you to find the CPU utilization of server and SQL. If like to know the steps to bring down the SQL Server CPU utilization follow http://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/ [...]

  3. Chri said

    Excellent Article. Managed to find the culprit based on your suggestions. Thanks very much

  4. Sanya said

    Impressive Article.

  5. Bikram said

    Nice One Bro…

  6. […] Troubleshooting SQL Server high CPU usage […]

  7. Dhanabal Ayyenperumal said

    Good article. I got right solution for my current situation issue in windows server 2008 R2. Thank you so much.

  8. sexe said

    Јe publie ce petit com afiո de congratuler son auteur

  9. Balakrishna.B said

    good one karthik .i have resolved on issue with help of you post..thanks man..

  10. ekistic said

    ” This creates intense & long-lasting anxiety, which
    can lead the child to spend the rest of their life trying to create that missing link with
    someone – anyone, so they can stop that terrible, relentless anxiety, SO.
    There are several ways to restore a beautiful smile, if you have a cracked tooth or if the series of them were cracked,
    dental fillings can be applied to each teeth in order
    to reshape them and make them look natural, Veneers
    also add a beautiful touch to the entire reconstructive procedure because they can’t
    even things out and they can make you look like you’ve never had
    a problem before, not to mention that they can make your smile look a lot
    brighter than it did before. Just remember to keep text
    toward the blues and greens and away from yellows which
    might end up being very hard to read.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 2,134 other followers

%d bloggers like this: