5

I have two simple queries:

SELECT TOP(20) * FROM Clients ORDER BY City
SELECT TOP(20) Id, Name, City FROM Clients ORDER BY City

In the first case I get a result like this

Id Name City many more columns
6 Person 6 NULL ...
2 Person 2 NULL ...
3 Person 3 NULL ...

In the second case I get a result like this In the first case I get a result like this

Id Name City
2 Person 2 NULL
3 Person 3 NULL
6 Person 6 NULL

Notice that the order is different. Of course I understand that beacuse City is NULL for these three persons a unique order is not necessarily garantueed.

However, upon inspecting the query plans I noticed that the first query used parallelism enter image description here

The second query did not:

enter image description here

After reading the documentation about parallel query processing, it specifically mentions that certain constructs inhibit parallelism, such as the TOP operator.

The only notable difference, apart form parralelism, is that the Top N Sort node of the parallelised plan has an estimated I/O cost of 16, while the Top N Sort node of the non-parallelised query plan has only an estimated I/O cost of 0.01

So my question is: why would it still use parallelism here when I use the TOP operator, even though microsoft stating it should inhibit the mechanism?

0

1 Answer 1

4

bottoms up

The documentation in that section is a total disaster.

The TOP operator only introduces a serial zone in the plan where it is consumes data. That's exactly what your plan shows, with the Gather Streams operator prior to the TOP operator.

Note that a TOP N Sort operator is not restricted in the same way as (presentation) TOP is in forcing a parallel zone.

NUTS

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.