« first day (4921 days earlier)      last day (23 days later) » 

2:34 AM
I see Julian Assange has been freed from prison and left the UK on an aeroplane
That all happened very quickly
 
2 hours later…
4:30 AM
Wordle 1,102 4/6*

🟨⬜⬜⬜🟨
🟨🟨🟨🟨⬜
⬜🟩🟩🟩🟩
🟩🟩🟩🟩🟩
5:13 AM
@PaulWhite only about 1,900 days
2
Wordle 1,102 4/6*

⬛⬛🟨🟨⬛
⬛🟩⬛🟩🟩
⬛🟩🟩🟩🟩
🟩🟩🟩🟩🟩
5:29 AM
@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
Wordle 1,102 5/6*

⬛🟨🟨🟨⬛
⬛🟩🟨🟩⬛
🟨🟩⬛🟩🟩
⬛🟩🟩🟩🟩
🟩🟩🟩🟩🟩
 
2 hours later…
7:54 AM
@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
Parallel merge join isn't that terrible as a row mode operator, but the exchange costs are ridiculous, especially when order-preserving.
Also, the internal exchange buffers far too small (in size and number) for modern use.
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
8:15 AM
It's quite fast even at SE-compatible DOP 2, and you have DOP-2 cores free for other work
8:27 AM
@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
8:49 AM
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.
 
1 hour later…
10:00 AM
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
10:19 AM
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
8
Let's not even joke about it
10:28 AM
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
10:44 AM
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
10:52 AM
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
11:03 AM
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
Adding inline functions can help but then you have another object to name
...
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
11:11 AM
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
11:21 AM
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
11:30 AM
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
11:33 AM
@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
11:35 AM
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
11:41 AM
at some point they'll tire of having to expose these things to people and hide it all away
well, batch mode is a gr8 example of that
spot the repartitioning and sync points
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
11:45 AM
@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
11:47 AM
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
11:59 AM
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
12:12 PM
color me underwhelmed
hail lepus
12:28 PM
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
12:46 PM
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
1:06 PM
@ErikDarling About resetting session wait stats, could you use :connect in SQLCMD mode?
probably, but life is hard enough
1:21 PM
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
you could go and visit the paddocks
I'm always amazed when people think I enjoy meeting people in person
1:33 PM
Yeah, he lives in New Zealand for a reason
born that way
saving up for an ice house in Antarctica
you want ice? i got a guy.
Superman?
1:35 PM
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
1:38 PM
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
1:47 PM
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
 
1 hour later…
3:06 PM
Aw man. I really liked that album.
3:17 PM
:CONNECT in SSMS sqlcmd mode works perfectly btw
 
2 hours later…
5:01 PM
> I have logged an incident with MS a few months ago, and and they told me to run chkdsk
5:19 PM
Top notch support.
5:54 PM
Obligatory
that xml question is a good one
6:09 PM
Is it?
who doesn't like a good documentation question?
@ErikDarling Excellent first question - if only they were all that good!
6:51 PM
"First question" is a bit misleading, the user has 8k reputation on SO
7:04 PM
Only saw that later - still a good question!
 
2 hours later…
9:10 PM
Might have a HNQ on our hands here
How long until @MikaelEriksson returns as our XML champion
9:34 PM
@Zikato Don't knock it till you try it.
10:32 PM
@Criggie I hear you can marry a citizen to expedite such things 😘😘😘
10:50 PM
heh - sorrily I am still spoken for at this time. Check back in a few months :-P
11:37 PM
@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
11:50 PM
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
11:57 PM
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

« first day (4921 days earlier)      last day (23 days later) »