Round robin vs Demand partitioning

GokhanVarol 2017-06-20 20:13:38

I started the queries within the same try at the same time in SQL Server. When the queries running I monitored dm_exec_query_profiles dmv constantly. I noticed the Round robin versions start a lot faster, they are inserting a lot more rows a lot faster into the Table Insert portion in the dmv also they pick up a lot more rows quicker from the driver side parallelism portion. Thinking logically demand partitioning should be more advantageous since our SQL server is usually over 50-60% cpu, litespeed backups running, has 64 cores etc. I was able to balance rows processed on threads a lot better with round robin partitioning but also the data within partitions are so out of balance I noticed some threads in demand partitioning only processes 1 record from the driver whereas the average records from the driver is around 196. With the demand partitioning I order the rows within partitions descending vs in round robin I try to balance the rows better.
Should I always use round robin instead, why does round robin starts processing rows a lot faster than demand partitioning, can I do more optimization for demand partitioning?
Thank you in advance.

Round Robin Try 4 3577 Seconds
Demand Try 4 4813 Seconds

Round Robin Try 3 4367 Seconds
Demand Try 3 5228 Seconds

alt text

Both queries start at the same time and their drivers have the same rows in the same order

Round Robin Try 3 436 Seconds
Demand Try 3 5228 Seconds
Round Robin Try 4 3577 Seconds
Demand Try 4 4813 Seconds