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.
Setup
I’m working on one server with two instances
The server has 560 worker threads according to Erik’s script:
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.
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:
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.
But what happened to the threads?
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
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.
Reitse's blog 