Database Reference
In-Depth Information
Figure 25-8.
Variable-length columns and memory grant: Execution Plan
■
Tip
you can monitor data spills to
tempdb
with Sort and hash Warnings in SQL trace and extended events.
Blocking operators can negatively affect the performance of queries when they are present in parallel sections
of the execution plan. The
parallelism
operator, which merges data from parallel executing threads, would wait until
all threads finish their execution. Thus the execution time would depend on the slowest thread. Blocking operators
can contribute to delays especially in the case of
tempdb
spills. Such conditions often happen when a parallel thread
workload has been unevenly distributed due to cardinality estimation errors.
■
you can see the distribution of workload between threads when you open the “properties” window for the
operators in the parallel section of the graphical execution plan in SQL Server management Studio.
Tip
In some cases, adding indexes can remove blocking operators from execution plans. For example, if you added
the index
CREATE INDEX IDX_Customers_Name ON dbo.Customers(Name)
, SQL Server would not need to sort
customer' data anymore and the query from Listing 25-3 would end up with an execution plan without blocking
operators, as shown in Figure
25-9
.
