Database Reference
In-Depth Information
Figure 34-11.
Execution plan with Clustered Index Scan in SQL Server 2014
■
Note
Batch-mode execution works only in parallel execution plans.
Table
34-3
shows the execution statistics for the queries in both SQL Server 2012 and 2014. As you can see, SQL
Server 2014 performs slightly better due to batch-mode execution of the hash operators.
Table 34-3.
Execution statistics: Clustered Index Scan and parallel execution plan
Logical Reads
CPU Time (ms)
Elapsed Time (ms)
SQL Server 2012
50,941
6,317
2,141
SQL Server 2014
51,128
6,046
1,820
Finally, let's remove the index hint and allow SQL Server to use a columnstore index and parallel execution plan.
The query is shown in Listing 34-7.
Listing 34-7.
Test query: Columnstore Index Scan with parallel execution plan
select a.ArticleCode, sum(s.Amount) as [TotalAmount]
from
dbo.FactSales s join dbo.DimArticles a on
s.ArticleId = a.ArticleId
group by
a.ArticleCode
Figure
34-12
illustrates the execution plan of the query in SQL Server 2012. As you can see, it utilizes batch-mode
execution. It is worth noting that the
Exchange/Parallelism
(
Repartition Streams
) operators in the execution plan
do not move data between different threads, which you can see by analyzing the
Actual Number of Rows
operators'
property. SQL Server 2012 keeps them in the plan to support cases when a Hash Table spills to
tempdb
, which will
force SQL Server to switch to row-mode execution.
