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:
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.
MSSQLWIKI 