Posts Tagged ‘high cpu usage sql server 2005’

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 LINKto 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

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