The usual solution for SQL SERVER cxpacket-parallelism Wait Type

Recently my two library appeared, appeared more Cxpacket waits, looked for the information on the net. One of the SQL Server columnist's articles is good, but also solves some of my doubts, translation here.

Translation is used only for the purpose of disseminating information.

Original source: http://blog.sqlauthority.com/2011/02/06/sql-server-cxpacket-parallelism-usual-solution-wait-type-day-6-of-28/

Translation finishing: joe.tj

Cxpacket has become the most common type of all wait types. I usually see cxpacket in the first five-bit wait type statistics for multi-CPU systems.

Books Online:

occurs when an attempt is to synchronize a query processor Exchange iterator. If contention for this wait type becomes a problem, consider reducing the degree of parallelism.

Cxpacket Explanation:

When you create a parallel operation for a SQL query, there are multiple threads to execute the query. Each query handles different sets of data or rowsets.

For some reason, one or more threads lag, resulting in a cxpacket wait state.

There is an organization/coordination (ORGANIZER/COORDINATOR) thread (thread 0) that waits for all threads to complete and aggregate data to render to the client.

The organization thread must wait for all threads to finish processing to proceed to the next step. Because the organization thread waits for a slow thread to finish processing the wait, it is called Cxpacket wait.

Please note that not all cxpacket wait types are bad things. You may encounter a cxpacket wait is a case of complete significance, and sometimes it is inevitable.

If you prohibit such a wait on any query, the query may become slower because it cannot perform parallel operations on it.

Reduce Cxpacket wait:

We cannot leave out the server load type to discuss reducing cxpacket waits.

OLTP: on a pure OLTP system, its transactions are short and queries are not long, but usually very fast. Set "Maximum degree of Parallelism" (MAXDOP) to 1.

Doing so ensures that the query never has to run in parallel and does not result in more database engine overhead.

EXEC sys.sp_configure n ' cost threshold for parallelism ', n ' 1 '
GO
RECONFIGURE with OVERRIDE
GO

data-warehousing/reporting Server: because the query execution time is generally longer, it is recommended to set "Maximum degree of Parallelism" (MAXDOP) to 0.

This way, most queries will take advantage of parallel processing, and queries that take longer to execute will also benefit from multiprocessor and improve performance.

EXEC sys.sp_configure n ' cost threshold for parallelism ', n ' 0 '
GO
RECONFIGURE with OVERRIDE
GO

Mixed System (OLTP & OLAP): This environment can be a challenge and must find the right balance point. I took a very simple approach.

I set "Maximum degree of Parallelism" (MAXDOP) to 2, which means that the query still uses parallel operations but only 2 CPUs are used.

However, I set the "Parallel query threshold" to a higher value, so that not all queries are eligible to use parallelism, except those queries that are expensive to query.

On a system that has an OLTP query and a report server, I find this to work well.

Here I will set "' Cost Threshold for Parallelism '" to 25 (). You can select any value. But you can only find the right value by doing experiments on the system.

In the following script, I set "Max degree of Parallelism" to 2, so that those with higher cost queries (here is 25) will execute parallel queries on 2 CPUs.

At the same time, no matter how many CPUs the server has, the query selects only two CPUs to execute.

EXEC sys.sp_configure n ' cost threshold for parallelism ', n ' 25 '
GO
EXEC sys.sp_configure n ' max degree of parallelism ', n ' 2 '
GO
RECONFIGURE with OVERRIDE
GO

--------------------------------------------

If reproduced or quoted, please keep the following:
Joe ' s blog:http://www.cnblogs.com/joe-t/

Complete Maintenance Process:

There are two values involved:

Cost threshold for parallelism is the default setting of 5S. The estimated cost is higher than 5S to schedule concurrency

sp_configure ' show advanced options ', 1;

GO

RECONFIGURE with OVERRIDE;

GO

sp_configure ' max degree of parallelism ', 4;--if 8 (CORE) CPU

GO

RECONFIGURE with OVERRIDE;

GO

Max degree of parallelism can limit the latency of short queries caused by parallel CPU unavailability due to maximum control

sp_configure ' show advanced options ', 1;

GO

RECONFIGURE with OVERRIDE;

GO

sp_configure ' cost threshold for parallelism ', 10;--will increase this time

GO

RECONFIGURE with OVERRIDE;

GO

Option (MAXDOP 1) can also be specified separately to limit

Preferred solution for SQL SERVER cxpacket-parallelism Wait Type