Database Reference
In-Depth Information
Let's consider a simple example and assume that we have a parallel plan with two threads followed by the
Exchange/Repartition Streams
operator. When one parallel thread finishes its work, it waits for another thread to
complete. The waiting thread does not consume any CPU resources; it just waits, generating the
CXPACKET
wait type.
The
CXPACKET
wait type merely indicates that there is parallelism in the system and, as usual, it fits into the “It
Depends” category. It is beneficial when large and complex queries utilize parallelism, because it can dramatically
reduce their response time. However, there is always overhead associated with parallelism management and
Exchange
operators. For example, if a serial plan finishes in 1 second on a single CPU, the execution time of the
parallel plan that uses two CPUs would always exceed 0.5 seconds. There is always extra time required for parallelism
management. Even though the response (elapsed) time of the parallel plan would be smaller, the CPU time will always
be greater than in the case of the serial plan. You want to avoid such overhead when a large number of OLTP queries
are waiting for the available CPU to execute. A high percent of
SOS_SCHEDULER_YIELD
and
CXPACKET
waits is a sign of
such a situation.
One common misconception suggests that you completely disable parallelism in the case of a large percentage
of
CXPACKET
waits in OLTP systems and set the server-level
MAXDOP
setting to 1. However, this is not the right way to
deal with parallelism waits. You need to investigate the root-cause of parallelism in the OLTP system and analyze why
SQL Server generates parallel execution plans. In most cases, it occurs due to complex and/or non-optimized queries.
Query optimization simplifies execution plans and removes parallelism.
Moreover, any OLTP system has some legitimate complex queries that would benefit from parallelism. It is better
to increase the
Cost Threshold for Parallelism
configuration option rather than to disable parallelism by setting the
MAXDOP
setting to 1. This would allow you to utilize parallelism with complex and expensive queries while keeping
low-cost OLTP queries running serially.
There is no generic advice for how the
Cost Threshold for Parallelism
value needs to be set. By default, it is set
to five, which is very low nowadays. You should analyze the activity and cost of the queries in your system to find the
optimal value for this setting. Check the cost of the queries that you want to run serially and in parallel, and adjust the
threshold value accordingly.
■
Tip
You can check the plan cost for a query in the properties of the root (top) operator in the execution plan.
Speaking of the
MAXDOP
setting, as general advice, it should not exceed the number of logical CPUs per hardware
NUMA node. However, in some Data Warehouse/Decision Support Systems, you can consider using a
MAXDOP
setting that
exceeds this number. Again, you should analyze and test your workload to find the most optimal value for this setting.
Locking and Blocking
Excessive locking and blocking issues in a system presents various
LCK_M_*
wait types. Each lock type has its own
corresponding wait type. For example,
LCK_M_U
indicates update (U) lock waits, which can be a sign of non-optimized
data modification queries.
We have already covered how to troubleshoot locking and blocking issues in a system. You need to detect
what processes participated in the blocking chain with the
Blocked Process Report
,
Deadlock Graph
events, and
sys.dm_tran_locks
view and find the root-cause of the blocking. In most cases, it happens due to non-optimized queries.
Worker Thread Starvation
In rare cases, SQL Server can experience
worker thread starvation
, a situation where there are no available workers to
assign to new tasks. One scenario when this can happen is when a task acquires and holds a lock on a critical resource
that is blocking a large number of other tasks/workers, which stays in a suspended state. When the number of workers
in the system reaches the
Maximum Worker Thread
configuration setting, SQL Server is not able to create new
workers, and new tasks remain unassigned, generating
THREADPOOL
waits.
