CXPACKET Waits and High CPU Usage Bottleneck SQL Server 2008 2012 2014 2016
When you suspect SQL Server is waiting on CPU and CPU is constantly spiked on 90-100% or SQL server high cpu usage query then it would wise to check the Top Waits Stats on SQL Server to see if the Top Waits is on “CXPACKET” by using Top Waits Rollup Query here.
Most of the time, DBA needs to find the culprit queries which are using high CPU and try to optimize it them by designing proper indexes or by rewriting the SQL more efficiently. SQL Server High CPU Usage query below can be very useful.
The queries useful in troubleshooting CPU Bottleneck issues, which are used by me are given below:
SQL Server High CPU Usage query below is also very useful and to find queries which are using the most cumulative CPU for SQL Server High CPU Usage
from (select top 50
from sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
SQL Server High CPU Usage query below is also very useful and if high runnable tasks count is found by below query then we have is a symptom of a CPU bottleneck.
where scheduler_id < 255
SQL Server High CPU Usage query below is also very useful and use below query to find Signal Waits (CPU waits) for instance(above 10-15% is usually a sign of CPU pressure)
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%signal (cpu) waits],
CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms) / SUM (wait_time_ms) AS NUMERIC(20,2)) AS [%resource waits]
FROM sys.dm_os_wait_stats WITH (NOLOCK) OPTION (RECOMPILE);
SQL Server High CPU Usage query below is also very useful and use the below query to Get Average Task Counts (run multiple times, note highest values)
SELECT AVG(current_tasks_count) AS [Avg Task Count],
AVG(runnable_tasks_count) AS [Avg Runnable Task Count],
AVG(pending_disk_io_count) AS [AvgPendingDiskIOCount]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255 OPTION (RECOMPILE);
Read More »CXPACKET Waits and High CPU Usage Bottleneck SQL Server 2008 2012 2014 2016