HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SQL Server generates a query plan with parallelism and TOP statement

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
generatestopstatementsqlwithqueryandplanserverparallelism

Problem

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

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.

Context

StackExchange Database Administrators Q#324527, answer score: 4

Revisions (0)

No revisions yet.