CXPACKET Wait in SQL Server
CXPacket wait in sql server occurs during parallel query execution, when
a session is waiting on a parallel process to complete.
MSDN says
that CXPACKET "Occurs when trying to synchronize the query
processor exchange iterator. You may consider lowering the degree of
parallelism if contention on this wait type becomes a problem". Whilst
this is true, it's also often the case that CXPACKET waits are the result of inefficient
queries or stored procedures.
Here is
an example of a SQL Server instance with high CXPACKET wait:
In this real-life example, several stored procedures had high CXPACKET waits because they included inneficient SQL statements which were not correctly indexed. A typical well-tuned database instance would not parallelize a query unless there was a missing index or there is an incomplete WHERE clause etc.
Potential Solutions to CXPACKET Wait
To
resolve long CXPACKET waits you first of all need to establish:
1. Is the
problem related to inefficient SQL which can be tuned?
Use a
tool such as DBTuna to quickly find out which stored procedures or batches are
taking the time, and which have high CXPACKET wait. Once these have been
identified, drill-down to establish which individual SQL's the wait is on. Once
isolated, use a tool such as the SQL Server Index Tuning Wizard to check
for missing indexes, or out of date statistics. Fix if possible. This was the
process used to solve the above real-life example. The top stored procedure
included multiple select statements, but just one was the bottleneck which
included an unindexed sub-query.
2. If the
problem cannot be tuned with Indexing
If the
statement cannot be tuning using normal mechanisms e.g. Indexing, re-writing
etc. then it may be that the solution is to turn off parallelism, either for an
individual query or for the whole server.
To find
out the current configuration of parallelism you can run the following command:
sp_Configure "max degree of parallelism".
If max
degree of parallelism = 0, you might want to turn off parallelism completely
for the instance by setting max degree of parallelism to 1. You could also
limit parallelism by setting max degree of parallelism to some number less than
the total number of CPUs. For example if you have 4 processors, set max degree
of parallelism to 2.
No comments:
Post a Comment