patternsqlMinor
SQL Server generates a query plan with parallelism and TOP statement
Viewed 0 times
generatestopstatementsqlwithqueryandplanserverparallelism
Problem
I have two simple queries:
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
The second query did not:
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?
SELECT TOP(20) * FROM Clients ORDER BY CitySELECT TOP(20) Id, Name, City FROM Clients ORDER BY CityIn 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
The second query did not:
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?
Solution
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.
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.
Context
StackExchange Database Administrators Q#324527, answer score: 4
Revisions (0)
No revisions yet.