Wait Types 是什麼?我遇到的Wait Type — CXPACKET - SQL Server

Posted by: Max Chen | in SQL Server | 1 year, 9 months ago |

今天查看Spotlight時，發現CXPACKET佔滿了所有的Wait Types???

100%肯定有什麼問題。

What are Wait Types

Basically, wait types are nothing but wait performed to execute any task created by SQL Server. As per BOL, there are three types of wait types, namely:

Resource Waits. Resource waits occur when a worker requests access to a resource that is not available because that resource is either currently used by another worker, or it’s not yet available.
Queue Waits. Queue waits occur when a worker is idle, waiting for work to be assigned.
External Waits. External waits occur when an SQL Server worker is waiting for an external event.

To check the wait types for any server, just run the following simple statistics:

SELECT * FROM sys.dm\_os\_wait\_stats

You can get the Wait Stats and identify which of the Wait Stats is causing the issue that troubles you.

The link to Book On Line where you can read all the Wait Stats is over here: SQL Server 2008 R2, SQL Server 2008, SQL Server 2005.

All of the Wait Types

This collection of content will describe SQL Server wait types that can be encountered more or less commonly, which includes the description, steps that should be taken and additional resources that could be used for more serious troubleshooting.

A B C D E F H I L M O P R S T W X

ASYNC_DISKPOOL_LOCK
ASYNC_IO_COMPLETION
ASYNC_NETWORK_IO
ASYNC_OP_COMPLETION

BACKUP
BACKUP_OPERATOR
BACKUPBUFFER
BACKUPIO
BACKUPTHREAD
BROKER_CONNECTION_RECEIVE_TASK
BROKER_DISPATCHER
BROKER_ENDPOINT_STATE_MUTEX
BROKER_EVENTHANDLER
BROKER_FORWARDER
BROKER_INIT
BROKER_MASTERSTART
BROKER_RECEIVE_WAITFOR
BROKER_REGISTERALLENDPOINTS
BROKER_SERVICE
BROKER_SHUTDOWN
BROKER_TASK_SHUTDOWN
BROKER_TASK_STOP
BROKER_TASK_SUBMIT
BROKER_TRANSMISSION_OBJECT
BROKER_TRANSMISSION_TABLE
BROKER_TRANSMISSION_WORK
BROKER_TRANSMITTER

CLR_AUTO_EVENT
CLR_MONITOR
CLR_RWLOCK_READER
CLR_RWLOCK_WRITER
CLR_TASK_START
CMEMTHREAD
COMMIT_ACT
COMMIT_TABLE
CXPACKET

DAC_INIT
DBMIRROR_DBM_EVENT
DBMIRROR_DBM_MUTEX
DBMIRROR_SEND
DBMIRROR_WORKER_QUEUE
DBMIRRORING_CMD
DBSEEDING_FLOWCONTROL
DBSEEDING_OPERATION
DEADLOCK_ENUM_MUTEX
DEADLOCK_TASK_SEARCH
DIRTY_PAGE_POLL
DISKIO_SUSPEND
DISPATCHER_PRIORITY_QUEUE_SEMAPHORE
DLL_LOADING_MUTEX
DTC
DTC_ABORT_REQUEST
DTC_RESOLVE
DTC_STATE
DTC_TMDOWN_REQUEST
DTC_WAITFOR_OUTCOME
DTCPNTSYNC

EXCHANGE
EXECSYNC

FCB_REPLICA_READ
FCB_REPLICA_WRITE

HADR_AG_MUTEX
HADR_AR_CRITICAL_SECTION_ENTRY
HADR_AR_MANAGER_MUTEX
HADR_AR_UNLOAD_COMPLETED
HADR_ARCONTROLLER_NOTIFICATIONS_SUBSCRIBER_LIST
HADR_BACKUP_BULK_LOCK
HADR_BACKUP_QUEUE
HADR_COMPRESSED_CACHE_SYNC
HADR_CONNECTIVITY_INFO
HADR_DATABASE_FLOW_CONTROL
HADR_DATABASE_VERSIONING_STATE
HADR_DATABASE_WAIT_FOR_RESTART
HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING
HADR_DB_COMMAND
HADR_DB_OP_COMPLETION_SYNC
HADR_DB_OP_START_SYNC
HADR_DBR_SUBSCRIBER
HADR_DBR_SUBSCRIBER_FILTER_LIST
HADR_DBSEEDING
HADR_DBSEEDING_LIST
HADR_DBSTATECHANGE_SYNC
HADR_FABRIC_CALLBACK
HADR_FILESTREAM_BLOCK_FLUSH
HADR_FILESTREAM_FILE_CLOSE
HADR_FILESTREAM_FILE_REQUEST
HADR_FILESTREAM_IOMGR
HADR_FILESTREAM_MANAGER
HADR_GROUP_COMMIT
HADR_LOGCAPTURE_SYNC
HADR_LOGCAPTURE_WAIT
HADR_LOGPROGRESS_SYNC
HADR_NOTIFICATION_DEQUEUE
HADR_NOTIFICATION_WORKER_EXCLUSIVE_ACCESS
HADR_NOTIFICATION_WORKER_STARTUP_SYNC
HADR_NOTIFICATION_WORKER_TERMINATION_SYNC
HADR_PARTNER_SYNC
HADR_READ_ALL_NETWORKS
HADR_RECOVERY_WAIT_FOR_CONNECTION
HADR_RECOVERY_WAIT_FOR_UNDO
HADR_REPLICAINFO_SYNC
HADR_SYNC_COMMIT
HADR_SYNCHRONIZING_THROTTLE
HADR_TDS_LISTENER_SYNC
HADR_TDS_LISTENER_SYNC_PROCESSING
HADR_TIMER_TASK
HADR_TRANSPORT_DBRLIST
HADR_TRANSPORT_FLOW_CONTROL
HADR_TRANSPORT_SESSION
HADR_WORK_POOL
HADR_WORK_QUEUE
HADR_XRF_STACK_ACCESS

IO_COMPLETION

MSQL_DQ
MSSEARCH
MSSQL_XP

OLEDB

PAGEIOLATCH_DT
PAGEIOLATCH_EX
PAGEIOLATCH_KP
PAGEIOLATCH_NL
PAGEIOLATCH_SH
PAGEIOLATCH_UP
PAGELATCH_DT
PAGELATCH_EX
PAGELATCH_KP
PAGELATCH_NL
PAGELATCH_SH
PAGELATCH_UP
PREEMPTIVE_OS_AUTHENTICATIONOPS
PREEMPTIVE_OS_BACKUPREAD
PREEMPTIVE_OS_CLUSTEROPS
PREEMPTIVE_OS_DEVICEOPS
PREEMPTIVE_OS_DTCOPS
PREEMPTIVE_OS_ENCRYPTMESSAGE
PREEMPTIVE_OS_FILEOPS
PREEMPTIVE_OS_FLUSHFILEBUFFERS
PREEMPTIVE_OS_GENERICOPS
PREEMPTIVE_OS_GETDISKFREESPACE
PREEMPTIVE_OS_GETPROCADDRESS
PREEMPTIVE_OS_LIBRARYOPS
PREEMPTIVE_OS_LOOKUPACCOUNTSID
PREEMPTIVE_OS_PIPEOPS
PREEMPTIVE_OS_QUERYREGISTRY
PREEMPTIVE_OS_SECURITYOPS
PREEMPTIVE_OS_SERVICEOPS
PREEMPTIVE_OS_SQLCLROPS
PREEMPTIVE_OS_WRITEFILEGATHER

REPLICA_WRITES
REQUEST_FOR_DEADLOCK_SEARCH
RESOURCE_QUEUE
RESOURCE_SEMAPHORE
RESOURCE_SEMAPHORE_MUTEX
RESOURCE_SEMAPHORE_QUERY_COMPILE

SLEEP_TASK
SOS_SCHEDULER_YIELD
SP_SERVER_DIAGNOSTICS_SLEEP
SQLTRACE_BUFFER_FLUSH
SQLTRACE_FILE_BUFFER
SQLTRACE_FILE_READ_IO_COMPLETION
SQLTRACE_FILE_WRITE_IO_COMPLETION
SQLTRACE_INCREMENTAL_FLUSH_SLEEP
SQLTRACE_PENDING_BUFFER_WRITERS
SQLTRACE_SHUTDOWN

TEMPOBJ
THREADPOOL
TRACEWRITE

WAITFOR
WRITE_COMPLETION
WRITELOG

XACT_OWN_TRANSACTION
XACTLOCKINFO
XE_LIVE_TARGET_TVF

我遇到的Wait Type — CXPACKET

The SQL Server CXPACKET wait type is one of the most misinterpreted wait stats.

The CXPACKET term came from Class Exchange Packet, and in its essence, this can be described as data rows exchanged among two parallel threads that are the part of a single process.

單進程,兩線程間,data交換的等待

One thread is the “producer thread” and another thread is the “consumer thread”. This wait type is directly related to parallelism and it occurs in SQL Server whenever SQL Server executes a query using parallel plan.

只要執行平行處理,就會產生CXPACKET等待類型(為了控制平行處理)

This is not necessarily a bad thing but it does consume additional CPU resources. If processor utilization is very high (typically above 80%) this wait should be investigated

通常一定存在CXPACKET,這是正常的, 介於50~80%都不需要處理, 但如果太高就需要調查了

When high CXPACKET values are encountered, a possible issue, even in case when parallelism is evenly distributed, is when the cost of creating the parallel plan is higher than the cost of the serialized thread.

因為平行處理耗費效能可能高於序列化處理

Cost Threshold for Parallelism (CTFP)

It is important to know that SQL Server’s query optimizer is using the Cost Threshold for Parallelism (CTFP) to determine when the query should be parallelized, or in other words, when the serialized query plan cost exceeds the cost threshold for parallelism it will create a parallel query plan.

The CTFP is set by default to 5, which mean that even not so expensive query plan could initiate the parallel plan to be created.

Cost Threshold for Parallelism (CTFP)決定何時要用平行處理,預設為5表示5秒, 表是只要預估超過5秒的Query, Optimizer會決定使用平行處理

To prevent unwanted parallelism, the CTFP number could be increased and by the aforementioned rule of thumb, a minimum value of 25. Recent analysis indicates that 50 should be the optimal minimal number for modern computers.

5預設值已經跟不上時代,現代經驗法則顯示CTFP最少25,最佳為50

如何找到最佳CTFP值,參考:Tuning ‘cost threshold for parallelism’ from the Plan Cache

MAXDOP

The default setting for MAXDOP is 0 and it means that all CPU cores should be used for processing. With modern machines featuring 8, 12, 32, 64 or even more cores, it is not advisable to allow that single query take over all the cores.

MAXDOP預設0,意思是盡可能使用所有CPU core, 但是並不建議,因為這樣有機會發生單個query使用所有CPU core.

如何找到最佳MAXDOP值,參考:Recommendations and guidelines for the “max degree of parallelism” configuration option in SQL Server.

以下是為什麼太多平行處理可能導致效能低落的範例之一

註:每個Thread的資料量不同,導致大部分的資料處理落在某一個Thread上(比如3,5)

### CXPACKET Wait Type too high 解決方法

So to sum the things up, these are the steps that are recommended in diagnosing the cause of high CXPACKET wait stats values (before making any knee-jerk reaction and changing something on SQL Server):

[普通版]

Examine high CPU queries to see if they can be optimized.
Investigate changing default MAXDOP and parallelism settings (links below)
Indexing, look for high CPU parallel queries

[高級版]

Do not set MAXDOP to 1, as this is never the solution(At least, Should be the last resource used in troubleshooting excessive CXPACKET wait times.)
Investigate the query and CXPACKET history to understand and determine whether it is something that occurred just once or twice, as it could be just the exception in the system that is normally working correctly
Check the indexes and statistics on tables used by the query and make sure they are up to date
Check the Cost Threshold for Parallelism (CTFP) and make sure that the value used is appropriate for your system
Check whether the CXPACKET is accompanied with a LATCH_XX

if the LATCH_XX waits is ACCESS_METHODS_DATASET_PARENT or ACCESS_METHODS_SCAN_RANGE_GENERATOR

AND

possibly with PAGEIOLATCH_XX or SOS_SCHEDULER_YIELD as well.

then it is highly possible.

If this is the case than the MAXDOP value should be lowered to fit your hardware

Check whether the CXPACKET is accompanied with a LCK_M_XX (usually accompanied with IO_COMPLETION and ASYNC_IO_COMPLETION). If this is the case, then parallelism is not the bottleneck. Troubleshoot those wait stats to find the root cause of the problem and solution

參考來源:

CXPACKET - SQL Shack - articles about database auditing, server performance, data recovery, and…
A lot of the long running queries are being parallelized. This is not necessarily a bad thing but it does consume…www.sqlshack.com SQL Server wait types - SQL Shack
This collection of content will describe SQL Server wait types that can be encountered more or less commonly, which…www.sqlshack.com Troubleshooting the CXPACKET wait type in SQL Server
The SQL Server CXPACKET wait type is one of the most misinterpreted wait stats. The CXPACKET term came from Class E…www.sqlshack.com https://blog.sqlauthority.com/2010/09/13/sql-server-what-are-wait-types-wait-stats-and-its-importance/

Tags:
SQL Server

Authors

Max Chen (159)

Feeds

RSS / Atom