Home » sql performance counters

sql performance counters

CXPACKET Waits and High CPU Usage Bottleneck SQL Server 2008 2012 2014 2016

  • by

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);

Read More »CXPACKET Waits and High CPU Usage Bottleneck SQL Server 2008 2012 2014 2016

The SQL Server Wait Types with their descriptions

  • by

The SQL Server Wait Types with their descriptions

Wait Statistics in SQL Server indicate sql server performance in relation to different resources like CPU, Disk usage, Memory etc..

SQL Server Activity monitor waiting tasks is another important place to observe what SQL Server is waiting on. Activity monitor has different sections like Processes, Resource Waits, Recent Expensive Queries and Data IO Stats.

“Wait_Type, Area, Usage, Version” Description Action
“ASYNC_IO_COMPLETION
I/O
Resource
SQL 2005-2008”
Used to indicate a worker is waiting on a asynchronous I/O operation to complete not associated with database pages Since this is used for various reason you need to find out what query or task is associated with the wait. Two examples of where this wait type is used is to create files associated with a CREATE DATABASE and for “zeroing” out a transaction log file during log creation or growth.
“CHECKPOINT_QUEUE
Buffer
Background
SQL 2005-2008”
Used by background worker that waits on events on queue to process checkpoint requests. You should be able to safely ignore this one as it is just indicates the checkpoint background worker is waiting for work to do. I suppose if you thought you had issues with checkpoints not working or log truncation you might see if this worker ever “wakes up”. Expect higher wait times as this will only wake up when work to do
“CHKPT
Buffer
Background
SQL 2005-2008”
Used to coordinate the checkpoint background worker thread with recovery of master so checkpoint won’t start accepting queue requests until master online You should be able to safely ignore. You should see 1 wait of this type for the server unless the checkpoint worker crashed and had to be restarted.. If though this is technically a “sync” type of event I left its usage as Background
“CXPACKET
Query
Sync
SQL 2005-2008”
Used to synchronize threads involved in a parallel query. This wait type only means a parallel query is executing. You may not need to take any action. If you see high wait times then it means you have a long running parallel query. I would first identify the query and determine if you need to tune it. Note sys.dm_exec_requests only shows the wait type of the request even if multiple tasks have different wait types. When you see CXPACKET here look at all tasks associated with the request. Find the task that doesn’t have this wait_type and see its status. It may be waiting on something else slowing down the query. wait_resource also has interesting details about the tasks and its parallel query operator
“DISKIO_SUSPEND
BACKUP
Sync
SQL 2005-2008”
Used to indicate a worker is waiting to process I/O for a database or log file associated with a SNAPSHOT BACKUP High wait times here indicate the SNAPSHOT BACKUP may be taking longer than expected. Typically the delay is within the VDI application perform the snapshot backup.

The Top Waits Stats rollup query can be used to identify Top Waits Stats with running percentage here. It is used to Isolate top waits for server instance since last restart.

Read More »The SQL Server Wait Types with their descriptions

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

 Important Perfmon Counters %Processor and Disk monitoring counters

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.