MaxDop Fun

Or more properly said, some interesting findings.

A few weeks ago, Erik Darling posted a video on Youtube about what happens when you set MaxDop. I won’t copy the content of that video, check it out for yourself.

What i did was try and replicate his findings. Because the scripts were easy enough for me to follow, understand and try out. And what happend was that Erik’s findings didn’t match my server or system. I just couldn’t get the results regarding the used threads for the query execution Erik showed.

Work intervened, as usual, but today i had a second try at trying to replicate Erik’s findings. This time i saved the scripts too :). The behaviour between SQL Server 2016 and 2017 is identical, so the mixed screenshots from both environments are only to show what happened. The version doesn’t seem to play a part in this.


I’m working on one server with two instances

Connected twice

The server has 560 worker threads according to Erik’s script:

Work that thread!

To records the run time of the query, i’m turning the statistics time on and the nocount can be turned off.

The Query

This Query is what it’s all about. I’ve used my own mark-up.

Ask me anything

SQL 2016

If i run this query on my 2016 machine, the results are this:

It’s nice and quick in getting together 18.037.912 rows. But that’s not the point. This is, go to the execution plan, click the select operator to get the properties and check out the thread stats:

Ok sure

So, on my 2016 machine, the query is maxed out on four threads and it’s using four threads. Cool. But not what Erik vlogged about. He had totally different numbers.

SQL 2017

On my 2017 machine, i had the same numbers, untill i changed one very vital variable, I removed the Clustered Columnstore index.

Bye bye runtime

But what happened to the threads?

Hello 5 times more

Not only did i manage to kill off performance by removing the Clustered Columnstore index, I also managed to increase my threadusage five times.

Execution plans

SQL 2016 with columnstore

SQL 2017 without columnstore

For me, this is another argument to use when people doubt the usage of columnstore indexes.

Thanks for reading!

Update 2019-03-29

Just after making this blog public i got the first reply from @sql_handle that my findings may be impacted by memory grants. I’ll be researching this and updating my post accordingly.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s