Introduction to Columnstore Indexes - Pro SQL Server Internals - page 700

Table 34-1 shows the execution statistics for the queries.

Table 34-1. Execution statistics: Clustered Index Scan and MAXDOP=1

Logical Reads CPU Time (ms) Elapsed Time (ms)

50,760 5,644 5,944

In the next step, let's remove the index hint and allow SQL Server to pick a columnstore index to access the data,
still using the serial execution plan. The query is shown in Listing 34-5.

Listing 34-5. Test query: Columnstore Index Scan with MAXDOP=1
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
option (maxdop 1)

Again, both SQL Server 2012 and 2014 generated an identical execution plan, as shown in Figure 34-9 . The plan
utilizes a Columnstore Index Scan in row-mode execution.

Figure 34-9. Execution plan with a Columnstore Index Scan and MAXDOP=1

Table 34-2 shows the execution statistics for the queries. As you can see, even with row-mode execution, the
Columnstore Index Scan introduces more than a four times reduction in the number of reads, and it allowed the query
to complete almost two times faster as compared to the Clustered Index Scan .

Table 34-2. Execution statistics: Columnstore Index Scan and MAXDOP=1

Logical Reads CPU Time (ms) Elapsed Time (ms)

12,514 3,140 3,191