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
    distribution = hash(c1)


with  filterData as 
    from [DW_reporting].[table1] 
    where num > 0

        ,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       

1 Answer 1


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 and acknowledge you have read our privacy policy.

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