Database Reference
In-Depth Information
In the next group of tests, we will remove the
MAXDOP
hint, which will allow SQL Server to generate parallel
execution plans. The first test would force SQL Server to use a
Clustered Index Scan
with the index hint, as shown in
Listing 34-6.
Listing 34-6.
Test query: Clustered Index Scan with parallel execution plan
select a.ArticleCode, sum(s.Amount) as [TotalAmount]
from
dbo.FactSales s with (index = 1) join dbo.DimArticles a on
s.ArticleId = a.ArticleId
group by
a.ArticleCode
Figure
34-10
illustrates the execution plan for the query running in SQL Server 2012. Even though SQL Server
generated a parallel execution plan, it used row-mode execution for all operators.
Figure 34-10.
Execution plan with Clustered Index Scan in SQL Server 2012
If you ran the same query in SQL Server 2014, you would see different results, as shown in Figure
34-11
. SQL
Server still used row-mode execution during the
Clustered Index Scan
; however,
Hash Join
and
Hash Aggregate
operators are used in batch-mode execution.
