| 0 comments | 4,612 views

VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)

CXPACKET waits may or may not be an issue for a system, but on an OLTP style system, they can indicate costly queries that are executing and requiring parallelized plans.

An alert is not defined, because the normal value for this metric can vary greatly from system to system, but you could add an alert once the baseline for your environment has been established.

OS Wait Time: CXPACKET, 5.0 out of 5 based on 1 rating

Metric definition

Metric Name:
CXPACKET waits indicate coordination of multiple threads from queries that have been parallelized and one or more of the subtasks have completed before others. These waits may or may not be an issue, but on an OLTP system, high CXPACKET waits may indicate too many complex queries needing parallel operations. Long wait times captured by this metric may indicate queries that are taking a long time to complete, even using parallel query plans. Note: Values are calculated/accumulated since the last restart of the server, or if they are reset using: DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
Enter the T-SQL query that will collect data:
SELECT  wait_time_ms / 1000.0 AS wait_time_sec
FROM    sys.dm_os_wait_stats 
WHERE   wait_type = 'CXPACKET';
Select instances to collect from:
Select all
Choose databases to collect from:
Specify databases and type
Set collection frequency:
Collect data every 10 minutes
Use collected or calculated values:
Turn on the Use a calculated rate of change between collections check box
Metric collection is:

Alert definition

You could add an alert once the baseline for your environment has been established, and you have a better idea of what thresholds to set.

VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)