Database Reference
In-Depth Information
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 cross join N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 cross join N2 as T2) -- 16 rows
,N4(C) as (select 0 from N3 as T1 cross join N3 as T2) -- 256 rows
,N5(C) as (select 0 from N4 as T1 cross join N4 as T2) -- 65,536 rows
,N6(C) as (select 0 from N5 as T1 cross join N4 as T2) -- 16,777,216 rows
,IDs(ID) as (select ROW_NUMBER() over (order by (select NULL)) from N6)
insert into dbo.FactSales(DateId, ArticleId, BranchId, OrderId
, Quantity, UnitPrice, Amount, DiscountPcnt, DiscountAmt, TaxAmt)
select ID % 727 + 1, ID % 1021 + 1, ID % 13 + 1, ID
,ID % 51 + 1, ID % 25 + 0.99
,(ID % 51 + 1) * (ID % 25 + 0.99), 0, 0
,(ID % 25 + 0.99) * (ID % 10) * 0.01
from IDs;
create nonclustered columnstore index IDX_FactSales_ColumnStore
on dbo.FactSales(DateId, ArticleId, BranchId, Quantity, UnitPrice, Amount);
Let's run several tests that select data from a facts table, joining it with one of the dimensions tables using
different indexes and different degrees of parallelism, which leads to serial and parallel execution plans. I am running
the queries in SQL Server 2012 and 2014 in 4-CPU virtual machines with 8GB of RAM allocated.
The first query shown in Listing 34-4 performs a
Clustered Index Scan
using a serial execution plan with row-
mode execution.
Listing 34-4.
Test query: Clustered Index Scan with MAXDOP=1
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
option (maxdop 1)
Both SQL Server 2012 and 2014 produce identical execution plans, as shown in Figure
34-8
.
Figure 34-8.
Execution plan with Clustered Index Scan and MAXDOP=1
