Common server waits on SQL server & what they mean

CXPACKET

SQL Server waits provide information about what the database engine is waiting on while extracting queries on a monitored server. While there are many different wait types, CXPACKET waits are a commonly misinterpreted data measurement and require additional clarification.

CXPACKET waits often result when parallelized query threads have an unequal workload, causing a block on the entire query until the longest-running thread is complete. Note that the trigger for the CXPACKET wait occurs not with parallelism, but with the distribution of work on the individual threads.

While you can decrease the number of CXPACKET waits by reducing the MAXDOP on the monitored server, this is often not a recommended course of action as it may cause the wait to disappear and the underlying query to take longer to execute. For example, this issue may result from large parallel table scans, which you can verify using other wait types on the server, such as PAGIOLATCH_XX. This may also result from out-of-date statistics that cause a bad query plan and the unequal workload among threads.

Before adjusting MAXDOP, review your indexes and statistics for accuracy and make sure there are no other underlying issues. If you choose to adjust MAXDOP, recommended settings include 4, 2, or 1. For a NUMA system, use the number of cores available in a single NUMA node. You may want to make these updates on the single query instead of the entire instance to avoid any additional problems.

ASYNC_NETWORK_IO

This happens when SQL server is done processing all the data it needs to output and waiting for the client/application to consume the data. The waits typically could come from network latency, which causes the data to be processed by the client slowly across the network. It could also happen if the client/application on the other end is consuming the data on row-by-row basis (RBAR operation).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s