1

I have the same query that shows as running in batch mode when run as a select statement, but row mode when the same query is the select part of a CTAS operation. Both tables (plus the resulting table) are all distribution aligned.

Why is this? Obviously I would like it to run in batch mode if possible.

Causes row mode execution:

create table mytable1
with
(
    distribution = hash(c1)
)
as

query:

with  filterData as 
(
    select 
        a
        ,b
        ,c
        ,d
        ,DateStamp
    from [DW_reporting].[table1] 
    where num > 0
)

    select 
        p.[DateStamp]
        ,p.[a1]
        ,p.b1
        ,p.c1
        ,p.d1
        ,e.a
        ,e.b
        ,case when e.d is not null then 1 else 0 end as Flag
    from SOmeOtherTable p
    left join filterData e on e.a = p.a1
                                            and e.c = p.c1
                                            and e.DateStamp < p.DateStamp       
2

Running explain on the query showed that something underneath was setting MAXDOP to 1.

Forcing the query to use 0 overcame this, as columnstores require DOP >= 2

Therefore adding the below hint enables batch mode

    OPTION (MAXDOP 0)   

I don't understand why this is happening - my understanding is that CTAS and building a new columnstore should always be parallel, but this resolves the issue.

Edit: This appears to be the default setting as it's aiming for the best optimum columnstore quality. So you have to make your choice between read performance and index quality or use an intermediate store, with each query having a different maxdop hint.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.