Adding SQL Server Data Collectors to SQL Server is very important for Troublshooting issues and Root cause analysis of many issues normally occuring on SQL… Read More »Adding Data Collectors on SQL Server 2008/2008 R2/2012/2014/2016
monitor server performance
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
Monitoring SQL Server Performance is one of important task for a DBA using Performance Microsoft Management Console (MMC) snap-in.
Performance monitoring can by done by many ways including monitoring SQL Performance Counters below which are for %Processor utilization and Disk Queue Monitoring. Perfmon analysis is important step in troubleshooting issues when a application goes slow.
Performance Monitor in SQL Server 2012 , 2014, 2016 etc are also very capable
|S.NO||Performance Counter Name||Comments|
|9||Processor – %Processor Time||The percentage of time the processor spent executing a non-idle thread. This value is subtracted from the time the processor was idle 100 percent. This is an indicator to the overall CPU utilization in the system.|
|9||Processor – %Interrupt Time||The percentage of time the processor spent servicing hardware interrupts. This can be any activity on the server machine. This needs to be 0 while our testing process.|
|9||Processor – Processor Queue Length||This counter indicates the number of threads that are waiting in the processor queue. It can be also interpreted as the number of threads that are waiting to be run by the processor. If this value is greater than the number of processor then we have aCPU bottleneck in the system.|
|9||Processor – Context Switches/sec||This is an interesting counter. A typical context switch occurs when the OS or the application is forced to change the executing thread on one processor to another thread executed on another processor. This value has to be as small as possible. Context switches are not avoidable in multi-processor machines. Hence any value below 10000 is fine.|
|9||PhysicalDisk – %Disk Read Time||Time spent by the disk for read operation. We can have disk by disk analysis also to narrow down any read IO bottlenecks.|
|9||PhysicalDisk – %Disk Write Time||Time spent by the disk for write operation. We can have disk by disk analysis also to narrow down any write IO bottlenecks.|
|9||PhysicalDisk – Avg. Disk Queue Length||Average number of requests that waited for the read and write requests in the particular disk. A high value suggests we have a IO bottleneck.|
|9||Memory – Page Faults/sec||Total number of faulted pages handled by the processor per second. This value needs to as small as possible.|
|9||Memory – Pages/sec||The number of pages written to disk or from disk to resolve page faults. This would be the sum of page reads/sec and page writes/sec counter.|