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

select highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from (select top 50
qs.plan_handle,
qs.total_worker_time
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.

select scheduler_id,
current_tasks_count,
runnable_tasks_count
from sys.dm_os_schedulers
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);

SQL Server High CPU Usage query below is also very useful and the below query can be used to find Top 50 SQL Statements taking a high cpu.

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

The below query useful in finding time spent optimizing query

select *
from sys.dm_exec_query_optimizer_info

To check plan_generation_num -- amount of time query was recompiled

select top 25 sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num >1
order by plan_generation_num desc

To check all active requests that are running in parallel for a given session

select r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where s.is_user_process = 0x1
group by r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0


To check how much memory SQL Server has allocated through AWE mechanism

select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks