Tag Archives: % Processor Time counter

Troubleshooting CPU Bottlenecks

Posted in Troubleshooting, Uncategorized by

CPU bottleneck can be caused due to insufficient hardware resource for the load. But before you rush to buy more hardware consider other factors. Most commonly excessive CPU utilization can be reduced by query tuning, application design changes and optimizing system configuration.

Identify the largest consumer of CPU and see if they can be tuned. Performance Monitor is generally one of the easiest means to determine whether the server is CPU bound. You should look to see whether the Processor:% Processor Time counter is high; sustained values in excess of 80% of the processor time per CPU are generally deemed to be a bottleneck. Within SQL Server, you can also check for CPU bottlenecks by checking the DMVs. Requests waiting with the SOS_SCHEDULER_YIELD wait type or a high number of runnable tasks can indicate that runnable threads are waiting to be scheduled and that there might be a CPU bottleneck on the processor. The below query gives currently cached batches or procedures that are using the most CPU. The query aggregates CPU consumed by statements having same plan_handle.

If you have enabled the data collector, the SQL Server Waits chart on the Server Activity report is a very easy way to monitor for CPU bottlenecks over time. Consumed CPU and SOS_SCHEDULER_YIELD waits are rolled up into the CPU Wait Category in this report, and if you do see high CPU utilization, you can drill through to find the queries that are consuming the most resources.

select top 50
    sum(qs.total_worker_time) as total_cpu_time,
    sum(qs.execution_count) as total_execution_count,
    count(*) as  number_of_statements,
    qs.plan_handle
from
    sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc

Common CPU resource intensive operations are discussed below. Detection and Resolution of the issues are discussed for each.

  1. Excessive query compilation and optimization.
  2. Unnecessary Recompilation.
  3. Inefficient query plan.
  4. Intraquery Parallelism.
  5. Poor cursor usage.

Check this for more troubleshooting performance issues in Sql server