Home » SQL Performance Tuning

SQL Performance Tuning

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

The Activity Monitor in SQL Server Management Studio is a great tool which can be used by DBA’s and database developers to get a quick overview of SQL Server system performance and connections or in simple words it is one of the free sql server performance monitoring tools. The Activity Monitor tool in SQL Server older versions than 2008 used to display information related to Processes, Lock by Objects and Locks by Process. There are many enhancements in Activity Monitor in SQL Server 2008 onwards like graphical display of Processor Time, Processes, Waiting Tasks, Database I/O’s, Data File I/O’s,Batch Requests, Resource Waits and also information on most expensive queries.

It is Best SQL Monitoring software / Application which ships free with SSMS (SQL Server Management Studio). It is widely used for Performance monitoring in SQL Server by analyzing SQL Performance monitor metrics for individual SQL database performance monitoring like number of connections , Top processor utilization etc..

The best sql monitoring tool above Activity Monitor is very important for a dba or developer working on SQL Server.

To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission.

2 Different Ways to Open up Activity Monitor in SQL Server 2008 are mentioned below:

Open up Activity Monitor Using Object Explorer

 

Opening SQL Server Activity Monitor method1

SSMS Activity Monitor Method1

 

 

In Object Explorer, right click the SQL Server 2008 Instance and click on Activity Monitor.

 

 

 

 

Also can be opened from SQL Server 2008 Management Studio’s toolbar, by clicking Activity Monitor

Opening SQL Server Activity Monitor method2

SSMS Activity Monitor by Method2

It shows the graphical display of Processor Time (%), Number of Waiting Tasks, Database I/O (MB/Sec) and the Number of Batch Requests/second.

Red Gate SQL Monitor is a paid version of SQL Monitoring tool / Application which can be used for monitoring multiple SQL Servers without connecting to each one. It is useful both for Monitoring Performance tuning.

Below is a list of SQL Server Performance Tuning and Monitoring Tools/Application Softwares:

  1. Solarwinds DB Performance Analyzer
  2. Idera SQL Diagnostic Manager
  3. Lepide SQL Server Auditing
  4. Heroix Longitude®
  5. SQL Power Tools
  6. Red-Gate SQL Monitor
  7. Sentry One (SQL Sentry)

idera sql diagnostic manager review- It is good tool for monitoring SQL Server and is one of the best sql monitoring tools. Comparing idera vs solarwinds, Idera is better than Solarwinds in many ways.