I see Julian Assange has been freed from prison and left the UK on an aeroplane

That all happened very quickly

@PaulWhite only about 1,900 days

@ErikDarling Well, yes and quite a bit longer than that if you count being imprisoned in the embassy. I meant things developed quickly with his release

Escalated quickly

He seems in remarkably good physical condition

All things considered

He's not lived a dull life

@Vérace oopy, yes you're right

@Vérace Thanks

@ErikDarling Posts chooses row mode because converting the huge Body column to batch mode is expensive. Thread distribution is poor because the matching rows are all very close together in clustered index order. Fix that with a TABLOCK or NOLOCK hint to get an allocation order scan. The merge plan costs cheaper because the sort is smaller than in the hash plan. More rows from Votes, but only on the PostId column, so it's much smaller.

Context

SELECT
    P.Id,
    P.AcceptedAnswerId,
    P.AnswerCount,
    --P.Body,
    P.ClosedDate,
    P.CommentCount,
    P.CommunityOwnedDate,
    P.CreationDate,
    P.FavoriteCount,
    P.LastActivityDate,
    P.LastEditDate,
    P.LastEditorDisplayName,
    P.LastEditorUserId,
    P.OwnerUserId,
    P.ParentId,
    P.PostTypeId,
    P.Score,
    P.Tags,
    P.Title,
    P.ViewCount
FROM dbo.Posts AS P WITH (TABLOCK)
JOIN dbo.Votes AS V WITH (TABLOCK)
    ON V.PostId = P.Id
WHERE
    P.PostTypeId = 2

Everything in batch mode

Most of the execution time is SSMS display delay

It's quite fast even at SE-compatible DOP 2, and you have DOP-2 cores free for other work

@Erik Curiously, the real failure is not considering an extra sort for the parallel merge join:

The new sort on the lower branch allows the super-effective bitmap

Yep, that's a parallel merge join with sorts on both inputs

Good morning

SELECT
    P.*
FROM dbo.Posts AS P WITH (TABLOCK)
JOIN dbo.Votes AS V WITH (TABLOCK)
    ON V.PostId = P.Id + 0 - 0
WHERE
    P.PostTypeId = 2
    AND P.CreationDate > '20131225'
ORDER BY
    P.Id + 0 - 0
OPTION
(
    MERGE JOIN,
    RECOMPILE
);

Without the hints, you get the hash batch mode plan. Omit Body to get full batch mode.

With the hints, you get the nice double-sort-plus-bitmap plan.

Merge bitmaps are only possible in parallel plans with an explicit sort on an input

Another reason to love parallel merge join?

Jokes aside, the row mode hash join plan with a bitmap is arguably better

Just much less interesting

Ahem

A bit to respond to

I don't have a YouTube account 😀

I figured the problem with Posts was the Body column. It’s always the Body column. Thanks for confirming.

@PaulWhite I can gift you one as a valued member of the Creator community

Do not want

But ty 4 the kind thgt

@ErikDarling You could start an OnlyPlans channel

Let's not even joke about it

Hey if I had that grey sweatpants money I could buy you a vineyard

Then I could truly be a Data Darling

President of the fan club

@PaulWhite the rules for parallel merge bitmaps have always been confusing wrt inner and outer inputs and stuff like this

@ErikDarling Most of the time it revolves around which input is the one in the one-to-many, but more generally (as this example shows) a sort can appear on either input making a bitmap possible, which is then applied to the other input.

Someone once tried to tell me a bitmap wasn't possible on the lower input so I wrote a blog post about it

Might even have been Adam M, but I couldn't swear to it now

He has always been argumentative about parallel merge join bitmaps

Might've been him then

Everyone's wrong from time to time, but the frequency varies a gr8 deal

Strongly correlates with comma placement and tab usage

Anyway, hash is easy because only the build input is normally blocking so that's the only place a bitmap can go

You have to have seen all the records to build a complete bitmap and transfer it b4 the other input gets going

Merge isn't normally blocking, but a sort makes it so

Maybe someday I’ll get promoted to capitalized aliases

You'll have to get your joins the right way around 1st

Progress slow, lang haul etc

They should all be from a join b on a.a = b.a

yep, backwards

despite the evidence, it's not something I feel strongly about, but I do notice it

Is that a prompt setting?

I think it is

Yep

I think the only way I can stand where clauses written like that is when the aliases are aligned

But that’s a lot of white space

Some people start the WHERE with a 1 = 1

But I'm not a huge fan of that overall

I don’t care for that outside of dynamic sql

Trivial plan issues aside

Right. You don't always want the side effects

The impact of the default is somewhat reduced by the awful example

i will update newprompt dictionaries accordingly

It's a shame there's no fully satisfying way to format SQL

i'm much more satisfied than i used to be

Yes, I should focus on the positives

there are some tricky situations, mostly revolving around parentheses

Is a nightmare

the other day i had to format a somewhat complicated string_agg call and spent a solid 20 minutes arranging things

nothing felt right

🤣

Been there

The compromise between clarity and length is a tricky one

I don't like a query to expand beyond a screenful

Artistic factors aside

Where's my ENABLE_EXCLUSIVE_BATCH_MODE_PLANS_PREFERENCE hint

The optimizer has had it built in for ages now

Not the hint, the ability

sqllang!CSrchStratQuickPlanExclusiveBatch

Surely there's a spare intern hanging around the games room

idly playing foosball with himself

wondering what sort of cookies to buy to impress the boss

I would suggest cookies with embedded recreationals but the evidence suggests that's been done more than once already

What is wrong with my typing 2day

it's like you took typing lessons from me

And when will chat get paste images

It's stupid I have a tab with 'Ask a Question' open so I can paste images there and copy the link here

@PaulWhite Did you get a transfusion of Type-O?

Hard enough to maintain tab discipline at the best of times

@Zikato V good

I feel sorry for merge join so tend to stick up for it. For example, it only gets post-opt bitmaps, so the plan has to win the costing race before the often-crucial bitmap is added. Hash join gets bitmaps considered during cost-based optimization

Still, it only makes sense with low row counts, like most row mode plan operators

the forgotten join

Really is

Bugs me no end they never finished optimizing it

what did they leave out?

For example, say you see a suddenly high value on one input. The thing reads one row at a time from the other input (with an arbitrarily deep subtree) until it finds an equal or higher key. That could be pushed down.

It's just a waste of effort to run that subtree a row at a time with a key value you know won't join

We could talk about batch mode merge join too

And fixing exchanges so they buffer better

i suppose if batch mode merge join got the window aggregate treatment

They agonise over these tiny memory grants then let columnstore request 4TB

i'm not very interested in randomly sorting data at row counts where batch mode really does its job

Merge join should rarely come with a sort

But simply merge joining a batch at a time would have big perf consequences

Cache hits being what they are

Batch mode sort is fairly busted anyway

The sorting itself is nice ofc

batch hits would be the new cache hits

But people forget many benefits of newer things comes from writing newer code

You wouldn't write 1990s code today

@HannahVernon would

But Microsoft still ships it en masse in SQL Server

Yeah but special case eh

canadian case

And they're going to have to offer a new cost model at some stage

@PaulWhite Which also isn't governed by MSM

Batch mode is partly hobbled by costing hacks so it doesn't get chosen every time

@SeanGallardy I read that first as mainstream media before max server memory

i was just on an email thread with vp and js about that

sound of names being dropped from a gr8 height

Is the air thin up there

topical name dropping

rarified

nobody noticed

anyway i got politely dismissed so it's not as cool as it sounds

that said, mode transitions can be expensive so I h8 that they're hidden

Like in the Body example earlier today

But then we have plans where basic timing makes no sense so

at some point they'll tire of having to expose these things to people and hide it all away

yep

it's all hidden now so no longer an issue!

drinks all round

prefetch magic

@ErikDarling new cost model or batch mode costing in partic?

maybe they're still smarting from the new CE

and DOP feedback

@PaulWhite sort about what's in the video. batch mode tricks don't get the full BMOR capabilities and BMOR heuristics are crappy.

ah right

useless bloody hint

i was told that they get complaints about BMOR being too aggressive and too conservative

standard line to avoid changing anything

so like okay, you can't fix that, but you can relax restrictions about batch mode tricks getting more out of it

if someone is saying yes to the batch then why not

At some point it'll occur to them it'd be smart for people to actively request a mode and take responsibility for that choice

Rather than persisting with some invisible compromise that pisses every1 off at some stage

oh you mean generalized heuristics and cost models don't fit every scenario

the worst is having some workarounds available but it's unsupported

like?

yeah I mean they have this thing about not having too many user-visible knobs and settings...like lol, really

OVERRIDE_BATCH_MODE_HEURISTICS

@PaulWhite knobs are like rabbits

hm yeah well that'd be gr8 if it really was a full override

rather than the first page of a Choose Your Own Adventure book

I spend an inordinate amount of time trying to convince the engine to produce the plan I had in mind

are you ever disappointed when you get it

sometimes, but it always leads to something better

the optimizer almost never has good enough quality information to make the bold calls it does

it's the time spent getting the plan that irks me

completely unproductive

like talking with an AI

oh god they're going to AI the optimizer aren't they

SQL Server query plan co-pilot

improving your perf with the power of 1348946832 Azure AdevntureWorks installs

probably

let's just disallow a scan on the inner side of a NLJ and go from there

i'd vote for that

also no scan under a top

DISABLE_HOPELESSLY_OPTIMISTIC_GUESSWORK

how about converting long in clauses to index spools rather than filters

it wouldn't take 15 minutes to compile a plan with all those constant scans being merge interval-ed and sorted

oh i'm talking about two different shapes

but same idea for both

nvm I know what u mean

they did shoehorn in a small improvement for long IN lists in 2022 but its so small

one tiny improvement per major version isn't going to cut it

sqllang!CFeatureSwitchesLangSvc::FConvertLargeInListToJoinAsSubstEnabled

Still under TF 10083

color me underwhelmed

hail lepus

at least batch mode hash spills aren't comparative disasters

is there a way to execute a query on a different spid than what your current ssms tab is assigned

aside from loopbacks

SQLCLR

but no

crumb bums

people with poor code formatting might suggest Service Broker

but what do you need such a capability for?

locking demo or sthg?

oh i was just looking for a convenient short hand to grab isolated data from dm_exec_session_wait_stats without opening 49873498576938 new tabs

since query plan wait stats omit important things

for your own good

artisan hand-crafted heuristics

just imagine the sort of advanced tech that could reset session wait stats

ctrl + n

@ErikDarling good job given the way memory grants are distributed

i might have lied

oh yeah i lied

hoowee

hey these sleep task waits seem important

let's see how bad i screw this up

huh i didn't

good thing the relative batch cost is on point

if you apply it in the inverse

SELECT
    c.Text,
    c = COUNT_BIG(*)
FROM dbo.Comments AS c
GROUP BY
    c.Text
HAVING
    COUNT_BIG(*) >  2147483647
OPTION
(
    RECOMPILE,
    HASH GROUP,
    QUERYRULEOFF GenLGAgg,
    MAX_GRANT_PERCENT = 0.1,
    USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_160')
);

2m 19s!

it's batch mode sorts all over again

i'm not wearing enough deodorant for this level of spice

@ErikDarling About resetting session wait stats, could you use :connect in SQLCMD mode?

probably, but life is hard enough

thinking back to old cartoon characters, i always enjoyed gossamer, despite the wabbit.

there was always something rather understandable about an angry monster who was easily plied by haircuts and manicures

@PaulWhite has hugo asked you out to dinner yet

again?

> 306km away

he would walk 500 miles

ha

you could go and visit the paddocks

I'm always amazed when people think I enjoy meeting people in person

Yeah, he lives in New Zealand for a reason

right

born that way

saving up for an ice house in Antarctica

you want ice? i got a guy.

Superman?

I suppose there might be a nicely uninhabited island nearer

there's an island between alaska and russia that seems like it'd be nice to build a fort on

Will look into it

i know it's politically unpopular to suggest alaska is near russia, but there you go

Well, it makes sense it's close since they sold it

I wonder how long one could live off fish on a boat

Chocolate fish

Wouldn't that be wonderful

It would last a lifetime just not very long

You just need to measure time by number of choccy fish consumed

if you really wanted some solitude, default caledonia might be an easier move

@PaulWhite Do you know if the Cadbury's choccy fish is comparable to the NZ variant?

@Zikato They're both nice in different ways. Cadbury is smaller and darker choc

I say 'both', there are any number of imitations

I can't find anything like it nearby

> Shifty Shellshock, Crazy Town frontman and ‘Butterfly’ singer, dead at 49

i had done a good job of forgetting about that

Aw man. I really liked that album.

:CONNECT in SSMS sqlcmd mode works perfectly btw

> I have logged an incident with MS a few months ago, and and they told me to run chkdsk

Top notch support.

Obligatory

that xml question is a good one

Is it?

who doesn't like a good documentation question?

@ErikDarling Excellent first question - if only they were all that good!

"First question" is a bit misleading, the user has 8k reputation on SO

Only saw that later - still a good question!

Might have a HNQ on our hands here

How long until @MikaelEriksson returns as our XML champion

@Zikato Don't knock it till you try it.

@Criggie I hear you can marry a citizen to expedite such things 😘😘😘

heh - sorrily I am still spoken for at this time. Check back in a few months :-P

@Criggie I’ll be waiting in Bora Bora

I understand why people want things to be documented

It's unfortunate when the documentation is wrong

@ErikDarling on the floor-a in bora bora?

But at least you have something to blame

@PaulWhite yeah - ongoing disagreement about bad docs.... are they better or worse than no docs ?

The eternal conundrum

Guess it depends on how bad they are... 50% right verses 90% vs 99% etc .

On a practical note, it's somewhat easier to get an existing entry improved than to get a new one created

But really the answer is not to use xml inside the database

Upgrade to JSON

Don't mind storing it or using it strictly as an input or output format

Exceptions made for xml generated by SQL Server itself, where no alternative exists

Doesn't mean I'm happy about it

It seems to me like they’re receiving xml and trying to Do The Right Thing™️ with it

Yes

I have no problem with their question

Split on that thang

As it were

In modern parlance

Never give an interview while intoxicated

Anyway. If you want an actual guarantee of document order, you'll need to preprocess the xml outside the database

Or get the source to provide it as an explicit part of the xml