SQL Server Parallelism (DOP >= 2) is Required to Get Batch Processing

Only parallel queries can run in batch mode.  Normally this is not a problem because the large majority of servers running SQL Server Enterprise Edition, and being used for reasonably large data warehouses and data marts, have more than one core these days, and queries on larger data volumes will always typically get a parallel query plan. But in some circumstances, say if you are running in a virtual machine (VM) with only one logical processor allocated to it, or some outside force like resource governor, or the query execution scheduler under heavy system load, is constraining query execution to DOP 1, SQL Server can't use a parallel plan. This will prevent batch processing.

To be safe, to get batch processing, always use a machine or VM with at least two logical cores, and avoid situations that can force DOP to 1.

As an example of the problem, consider this query:

select m.MediaId, COUNT(p.IP) PurchaseCount 
from dbo.Media m join dbo.Purchase p on p.MediaId=m.MediaId 
group by  m.MediaId 
order by  COUNT(p.IP) desc
option(maxdop 2)

dbo.Purchase has 100M rows and a columnstore index. The query above runs in 1/4 second. It runs in parallel in batch mode on two hardware cores, as forced by the maxdop 2 hints.

Now, consider this query:

select m.MediaId, COUNT(p.IP) PurchaseCount 
from dbo.Media m join dbo.Purchase p on p.MediaId=m.MediaId 
group by  m.MediaId 
order by  COUNT(p.IP) desc
option(maxdop 1)

This runs serially, which prevents batch mode, and it takes 12 seconds, not the 1/2 second or so you'd expect.

<