@ErikDarling Eeny, meeny, miny, moe

A chairde - Morning all!

Good Morrow good people

good evening you creeps

How was your weekend?

my wife shared her sinus infection with me

Sharing is caring

would you like a booger

@ErikDarling were you expecting much benefit from parallelism with that small row count?

none at all

point taken about parallel scan/seek row packet distribution though

reminds me of Forrest for some reason

i was hoping that there would be some row distribution so it would be easier to explain why the parallel plan may show more rows entering the sort operator than the serial plan

alas it was not to be

i could have sworn you had a post about that but i couldn't find it quickly

the parallel top thing?

yeah

this one perhaps sql.kiwi/2012/05/parallel-row-goals-gone-rogue.html

yeah looks close enough

still weird that one thread gets extras though, heh

@ErikDarling Oh, perhaps I'm missing your point then. Which discrepancy are you referring to exactly? Which row count at which operator?

In your picture, thread 5 processes 1854 rows. The Sort, NLJ, and inner Seek show 1,012 rows. The Gather Streams sends out 1,000 after receiving 1,012 rows (not shown in execution plans).

In your answer, both serial and parallel sorts receive 1,854 rows

oh i meant that in the serial plan the sort seems to send out 1000 rows only where the parallel plan seems to send out 1012 rows

Yeah, well the Gather Streams also receives 1,012 rows - you just can't see it

So, it's buffering at the exchange

i suppose it's just odd that it's more visible in the parallel plan

Well, there's no buffering at the Gather Streams exchange in the serial plan because it doesn't exist

yeah but in other places

Meaning the other parallel operators?

Let me try to describe it a different way

Imagine, for example, that 253 rows fit into each packet at the exchange. After three such packets have been filled by the parallel side of the plan, 3 full packets x 253 = 759 rows have been made available to the consumer side of the exchange, where the Top gets its rows from. The Top needs 1,000 rows so it can't finish yet. The parallel side of the plan needs to supply another full packet of 253 rows before the Top can see 1,000 rows. A total of four full packets of 253 rows = 1,012 rows.

That explains why the parallel NLJ, inner seek, and Sort output show 1,012 rows. They need to provide that many to the producer side of the Gather Streams exchange to fill four packets of 253 rows. Only full packets get moved across the exchange (until the end of the stream anyway).

The other seek produces 1,854 rows because the Sort input is blocking. This seek has to provide all the rows it has before the Sort will transition to producing output rows.

that makes a lot more sense now, thanks

it reminds me slightly of the parameterized top thing i ran into a while back

The plan would make easier reading if that situation applied here

As I remember, that was a special case where a single row was enough to push a packet across the exchnage

erikdarling.com/a-parameterization-puzzle-with-top-part-1

that thing

yep

Top with a constant < 101 that was it yeah

But you had to choose 1,000 for your helpful Q & A demo

And a world of potatoes ensued

🥔🌎

That post of yours will never get the appreciation it deserves

@PaulWhite sounds like an SEO problem

More sense rather than complete sense then

I've forgotten all the important details already, it seems

gets in the way of important things

Oh, a lower DOP would help too

i ran into a lower dop problem yesterday

same execution time from maxdop 4-16

maxdop 3 was very fast

tiresome.jpg

sorts that spill are faster

nothing makes sense

the thing is, everything is ridiculously complicated when you dig right into it

so, it's not really that surprising that odd things happen sometimes

I once wrote a five part series of long posts on how a single fairly simple parallel plan starts up

Not the whole lifetime of its execution, mind you

And not every possible detail either

The only correct response to someone saying a strange thing happened is

Yes, yes it did

i ran across those looking for the other post yesterday

great post(s)

my usual response is "i believe you"

> sorry to hear that

> happens more often than you'd think

tiresome.jpg probably covers it

oh yes, sorts are faster if they spill unless they're batch mode

all very intuitive

you end up doubting everything you think you know

it's quite chaotic

that's one of wife's nicknames

she has a series of quite unique filing systems

on the upside, she's never given me a sinus infection

well, my wife doesn't have a filing system, so it all seems to even out

unless throwing away important papers is a filing system

obviously a common female trait

I just rediscovered that the single-row-packet thing only applies to Top (<=100) and not WITH TIES

TOP (1) WITH TIES runs for a hilariously long time

Pretty sure we noticed that at the time

it rings a bell

Always nice to meet an old friend

perhaps it would run faster if it weren't wearing formal attire

We should refer to that as the tuxedo option henceforth

Though that did give Jackie Chan amazing running speed

and a singing voice

yes

doesn't seem to slow jardani jovonovich down either

Had to google that one

me too

heh

The first one was ok and it went downhill since

are you having a wrong contest with jacuzzi dolphin that i'm unaware of

oh also, are you submitting to bits this year?

I haven't seen any of the John Wick films

I'm saving them up

you could kill a whole day with them now

@PaulWhite 1 was the best, 2 was good, 3 was meh, 4 was arguably better than 3

@ErikDarling no, I'm not even going

well fine then