November 14, 2012
| 0 comments | 818 views
Categories: Performance
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.
|
Metric Name:
|
OS Wait Time: CXPACKET
|
|
Description:
| |
|
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 master
|
|
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:
|
Enabled
|
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.
