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:
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 
Query optimization « MSSQLWIKI said
[…] Troubleshooting SQL Server high CPU usage […]
Query to find SQL Server CPU utilization 2012. « SQLServerScribbles said
[…] 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 https://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/ […]
Chri said
Excellent Article. Managed to find the culprit based on your suggestions. Thanks very much
Sanya said
Impressive Article.
Bikram said
Nice One Bro…
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] Troubleshooting SQL Server high CPU usage […]
Dhanabal Ayyenperumal said
Good article. I got right solution for my current situation issue in windows server 2008 R2. Thank you so much.
sexe said
Јe publie ce petit com afiո de congratuler son auteur
Balakrishna.B said
good one karthik .i have resolved on issue with help of you post..thanks man..
Karthick P.K said
Only one 😦
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.
Aminos said
Thanks a lot, I resolve my problem up to you. Excellent article !
How To Fix Cpu Usage High Errors - Windows Vista, Windows 7 & 8 said
[…] Troubleshooting SQL Server high CPU usage « … – 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 …… […]
Abuelshabab said
Thanks, but did you blog about high SQL compilation/sec?
DongJin Kim said
Good Article, Thanks
naveentfsalm said
Excellent
Brian Kang said
Hello Karthick,
I’m getting lots of SOS_SCHEDULER_YIELD waits with follwing stacks that I’m not sure why. Can you help me on this?
thanks a lot!
XeSosPkg::wait_info::Publish+138 [ @ 0+0x0
SOS_Task::PreWait+176 [ @ 0+0x0
SOS_Task::Sleep+130 [ @ 0+0x0
YieldAndCheckForAbort+c3 [ @ 0+0x0
CQScanHash::ConsumeBuild+18d [ @ 0+0x0
CQScanHash::Open+8e [ @ 0+0x0
CQScanProfileNew::Open+141 [ @ 0+0x0
CQScanHash::ConsumeProbe+1bf [ @ 0+0x0
CQScanHash::Open+a4 [ @ 0+0x0
CQScanProfileNew::Open+141 [ @ 0+0x0
CQScanNew::OpenHelper+41 [ @ 0+0x0
CQScanXProducerNew::Open+c8 [ @ 0+0x0
FnProducerOpen+44 [ @ 0+0x0
FnProducerThread+889 [ @ 0+0x0
SubprocEntrypoint+a59 [ @ 0+0x0
SOS_Task::Param::Execute+21e [ @ 0+0x0
SOS_Scheduler::RunTask+a8 [ @ 0+0x0
SOS_Scheduler::ProcessTasks+29a [ @ 0+0x0
SchedulerManager::WorkerEntryPoint+261 [ @ 0+0x0
SystemThread::RunWorker+8f [ @ 0+0x0
SystemThreadDispatcher::ProcessWorker+372 [ @ 0+0x0
SchedulerManager::ThreadEntryPoint+236 [ @ 0+0x0
BaseThreadInitThunk+1a [ @ 0+0x0
RtlUserThreadStart+21 [ @ 0+0x0
XeSosPkg::wait_info::Publish+138 [ @ 0+0x0
SOS_Scheduler::UpdateWaitTimeStats+30c [ @ 0+0x0
SOS_Task::PostWait+90 [ @ 0+0x0
SOS_Task::Sleep+147 [ @ 0+0x0
IndexPageManager::GetNextPage+335 [ @ 0+0x0
IndexRowScanner::MoveKeyOrderToRowOnNextPage+16c [ @ 0+0x0
IndexRowScanner::MoveToRowOnNextPage+23b [ @ 0+0x0
IndexDataSetSession::GetNextRowValuesInternal+105b [ @ 0+0x0
RowsetNewSS::FetchNextRow+197 [ @ 0+0x0
CQScanRowsetNew::GetRowWithPrefetch+51 [ @ 0+0x0
CQScanRangeNew::GetRow+15b [ @ 0+0x0
CQScanProfileNew::GetRow+d7 [ @ 0+0x0
CQScanXProducerNew::GetRowHelper+366 [ @ 0+0x0
CQScanXProducerNew::GetRow+15 [ @ 0+0x0
FnProducerOpen+57 [ @ 0+0x0
FnProducerThread+889 [ @ 0+0x0
SubprocEntrypoint+a59 [ @ 0+0x0
SOS_Task::Param::Execute+21e [ @ 0+0x0
SOS_Scheduler::RunTask+a8 [ @ 0+0x0
SOS_Scheduler::ProcessTasks+29a [ @ 0+0x0
SchedulerManager::WorkerEntryPoint+261 [ @ 0+0x0
SystemThread::RunWorker+8f [ @ 0+0x0
SystemThreadDispatcher::ProcessWorker+372 [ @ 0+0x0
SchedulerManager::ThreadEntryPoint+236 [ @ 0+0x0
100 Cpu Usage Fix Sql Server – Allinthenewspapers said
[…] Troubleshooting SQL Server high CPU … – 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 … […]
Becky S said
I need to find someone that can help me troubleshoot why my CPU is at 100% on my SQL Server. Can anyone contact me at sales@opticfast.com and we will pay you?
SQL Server – CXPacket Waits – Compare Across SQL Server Instances | Learning in the Open said
[…] Troubleshooting SQL Server high CPU usage Link […]
FirstSergio said
I see you don’t monetize your blog, don’t waste your traffic, you can earn additional cash every month because you’ve got hi quality content.
If you want to know how to make extra $$$, search for: Mrdalekjd methods for $$$
Codi Dilello said
Troubleshooting SQL Server high CPU usage
[…]Please feel free to look through our Gallery and skim our Testimonials page to see for yourself exactly what Distinctive Design Remodeling can do for you![…]
discuss said
my company
Troubleshooting SQL Server high CPU usage
Discuss said
discuss
Troubleshooting SQL Server high CPU usage
Dlozi said
Excellent
Michel sliws said
I could never imagine that a wring execution plan memory desire would make the cpu spike.
https://good2manage.com/sqlserver-cpu-100-because-of-wrong-memory-estimates%E2%80%8B/
SQL Server uses CPU too much - Boot Panic said
[…] from the link https://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/ I’ve checked the possible issue with timer, that can be caused by power plan or other […]
Resolved: SQL Server uses CPU too much - Resolved Problem said
[…] no queries and attached DBs? How can I at least learn what’s happening? Also, from the link https://mssqlwiki.com/2012/10/04/troubleshooting-sql-server-high-cpu-usage/ I’ve checked the possible issue with timer, that can be caused by power plan or other […]