The Heap™ – Consultancy ©®

10:50 AM

@ErikDarling Was asleep. Appreciated and starred now.

@Zikato It's this one 🔼

i also have that book and have not found it particularly helpful when actually writing xpath queries

and it was bizarrely expensive

bought it in 2016 and it was ~60 on amazon

11:05 AM

Yeah it might not be the best one

I don't even remember how I came to buy it

Might've been part of a bundle of something

I don't usually rate Apress

Then again, how would you write a good book about XML

2

I do think it explains things quite well from the bottom up, which is a style I usually like, but for some reason the concepts just don't stick in my brain very well.

Probably because I write XML so infrequently

the reason is obvious

every example of xml is about how a business would use it

we use it to work with query plans, xe data, etc.

This is the Xpath presentation I usually refer to. I have the PASS video stored locally.

https://www.slideshare.net/MichaelRys/sqlpass-ad501m-xquery-mrys

Yes Michael Rys is the go to for everything xml

practically invented it

@ErikDarling I don't think I've used xml with query plans more than a handful of times

I certainly don't look forward to doing it

11:14 AM

We use XML with Service Brokers a lot

oh the deadlock xml

yeesh

@Zikato makes sense

blocked_process_report xml as well

You DBAs. So crazy.

hey i've been troubleshooting parallel deadlocks from orm calls this week

11:15 AM

Living your best life

I'm a DB developer, I just dabble in other stuff. Usually I want to monitor or debug what I created

There really ought to be a distinction between developers who dabble in databases, and real database developers

😀

datadabbles

2

I suppose I'm alone in mostly eyeballing xml because anything else is too hard

Even deadlock graphs in PE don't do it for me

I wonder what Jason would do

no i often just read the xml after doing a quick f/r on > to /n>

11:18 AM

right

but that isn't practical for the people who use scripts i write

though that does sound a bit like regex

slippery slope

only regex i know

i think i even goofed that up

.*

yep I mean you have to enable regex s/r in SSMS for that

a dark path

11:20 AM

You can always do that manually

I suppose I should state I do rate developers (except web devs obv) and wish I were better at actual programming

I also have auto line breaks enabled in SSMS for xml

Line wrapping, whatever

with or without the glyph?

Hi David It can cause bad plans if we join this DMV with others. It does seem to correct itself after a reboot, its more a peculiarity and if anyone else had seen it — Stockburn 10 hours ago

@Zikato Oh with for sure

what views do you join perfmon counters to???

have i been missing something all these years?

11:22 AM

controversial ones, apparently

maybe they are saving snapshots

My favourite counter is Deprecated Features

a good game is to try to get that to wrap around

install bdc x f

4u

i can't wait until ags are deprecated

11:32 AM

I hear they're a mirror for one's soul

i have no soul apparently

Sometimes think AGs are the only reason the DBA role still exists

setting up ola jobs

Sometimes I have a feeling that AGs caused more outages than they prevented.

2

ha prob true

11:42 AM

basically paying some knob 150k a year to call microsoft every time the ag goes down

200k if it's a DAG

You are getting paid?

paul charges by the thought

@PaulWhite hey

oh hai J D

11:53 AM

uh oh

are my twitter cards broken

They're about to be.

:O

only if you play your cards right

if i could do that i wouldn't need josh

willian pereira

Bom dia, galera! eu transferi um site em Wordpress pro meu localhost e me gerou um 404. alterei as url no banco de dados, e nada mudou alguem poderia me ajudar?

11:58 AM

sir this is arby's test kitchen

> ‎Good morning, guys! I downloaded a site on Wordpress to my localhost and generated me a 404. i changed the url in the database, and nothing changed someone could help me?‎

reddit.com/r/AskReddit/comments/cq1q2/…

@willianpereira You might be looking for that site ^

so what's everyone doing this weekend

alright cool

fun bunch

12:17 PM

morning

yes

@ErikDarling reading a book about xml

are you trying to forget xml or the weekend

Honestly I wasn't aware it was Saturday until you said that

Might have a Picnic

🍷

such a lush

@ErikDarling RUNNING / nothing.

You?

12:28 PM

not running

youngest one is still healing from chickenpox, so that's my plan for the weekend

well we know what you're having for breakfast

not sure I follow?

always a risk in this chat

12:45 PM

same

huh wrong chat room lol

→ 1 message moved to Cricket Chat

crikey

I should have multiple chat tabs really but who has time for that

Certainly not me I have a busy weekend ahead

'Morning

@Zikato You've got to ask Erik to JEAGL

12:52 PM

@ErikDarling JEAGL pretty please

> General on- and off-site discussion for dba.stackexchange.com. Jokes explained at great length (JEAGL) please. We are using SQL. It's always morning in the Heap™ Best gymnastics group anyone could ask for.

@ErikDarling school holidays started today, so mostly kid stuff. I predict a lot of Monopoly & Risk in the coming days.

SAT: Standby due to RFC / SUN: Swimming

is it a jeagl if it's a one-word answer?

"oatmeal"

3

@ErikDarling recently SSMS crashes when I do a regex replace.

12:57 PM

@MichaelGreen is it complicated?

In my head I can hear Paul say "quite right too!"

is oatmeal traditional when you're sick? Might be a different culture thing

it's a common salve for anything that itches

@ErikDarling no, almost exactly as you've shown - inserting new line to break up long text

let's see how chat handles gifs
https://c.tenor.com/ubgtCAokOz0AAAAd/nightcrawler-jake.gif

1:01 PM

@Zikato healthline.com/health/oatmeal-bath-for-chickenpox#oatmeal-bath

Except mine uses "\n" instead of "/n". Maybe because I'm in Australia? :-)

Thanks Josh and Erik, today I learned. We just use over-the-counter medicine.

@MichaelGreen u/

@ErikDarling ew

@MichaelGreen quite right too

@Zikato needs to be on its own line

ohh

One day I'll master the chat

1:08 PM

I must admit I thought the "oatmeal" was a reference to the way infected skin looked when covered with e.g. calamine lotion

Hence the "ew"

why would i suggest that anyone eat their pox-inflicted children

that's just disturbing

It's not entirely out of character

cultural difference

perhaps for you island folk

going right to cannibalism

Besides you'd just take a scraping, not eat the whole thing

Obviously

1:09 PM

this keeps getting worse

I think I'll skip breakfast

Else there'd be nothing left for dinner

just the memories

Still less cruel than foie gras

@PaulWhite like a ripe Brie

1:11 PM

Just like that yes

those ducks had it coming

@PaulWhite now I just need one of those voice synthesising AIs and I'm sorted.

My Siri voice is currently South African female

Not because it sounds especially South African

It's just the least annoying

How eclectic!

I tried to get it to say "ice cream" but no joy so far

1:15 PM

does it sound like yolandi

@ErikDarling Is that a brand of oatmeal

JE: "ice cream" is indistinguishable from "ass cream" in an SA accent

I find it hilarious

currently downloading new siri voices

smh.com.au/entertainment/comedy/…

> 2. Have you had any misunderstandings because of your accent?

Well yes, I have this bit in my show where I talk about arse cream and what to do with it. I noticed people often looked confused and worried - turned out, they thought I was talking about ice-cream and they couldn't understand why I would want people to rub that on their bums.

lack of imagination

while writing about new sql server features i get pretty excited until i remember no one who needs them will see them for years

i'm starting to feel like that hitch hiker robot

Chocolate Ass Cream

►

@ErikDarling aren't you eeyore tho

1:25 PM

only when i'm not drinking

@PaulWhite something something glass houses ... :-D

anyway, i asked SA siri where to get ice cream

she said a place near me with "ice cream" in the name

it did not sound like ass cream

i want my money back

that cost me like two blog posts

@ErikDarling Please sir to refer to transcript

11 mins ago, by Paul White

Not because it sounds especially South African

i don't mind when a goose chase ends in foie

@ErikDarling we're getting new hardware. I quietly mentioned that SQL Server 22 will drop soon. Blank stares & tumbleweed. No hum. If it ever happens I'll be sure to point them toward your blogs.

1:34 PM

my blogs are apparently not a good form of salesmanship

dunno if that's what you want

I hear tables are expensive at the restaurant at the end of the universe

mustaccio

@PaulWhite But views must be breathtaking

Serious question - do you think performance tuning will be hard with the new responsive / adaptive execution features?

I think it'll make some things trickier to debug

Other things will no longer need debugging

If the plans could change spontaneously after a few executions, that'll be frustrating, I'd think.

1:38 PM

There could be a new class of transient problems that will be v difficult to pin down

And again, repros may become harder for remote analysis

@PaulWhite Exactly. Rather than "turn it off an on again" it will be "run it five more times & call me in the morning."

Or 15

but yeah

i think it's 16

one run to cache the plan

15 for it to figure out there's a problem

> five more times

grumpy robot donkey

I wonder how much of the investment is justified by Azure cost reductions

@ErikDarling ... and in the darkness bind them. My precciosss.

1:44 PM

if they really cared they'd just make everything adaptive

INNER ADAPTIVE JOIN hint anyone?

option(adaptive join)

just a nice way of telling merge joins to beat it

I guess query store will have the history of changing plans so we can track when the adaptive stuff kicks in?

if you can find it in query store

ALTER DATABASE SCOPED CONFIGURATION....

1:50 PM

Eventually queries on query store will be self-optimizing

@ErikDarling can't beat a nice parallel merge join

@MichaelGreen A large fraction of common queries could be successfully auto-tuned.

But never underestimate the capacity of people to write dumb queries.

Against dumb schemas.

@PaulWhite it must count for a lot. If you could shave 1% off all SQL execution Azure-wide how many racks of servers (plus electricity, cooling, space) would that add up to?

right

I read something recently about the scale of one of the O365 service and it boggled my mind.

I do wonder how useful row mode parallelism would be these days if all operators supported batch mode

Erik would miss the repartition streams I know

1:56 PM

> Our production service runs over 100 different processes and app pools on a mixture of 200,000+ machines - Exchange Online Journey to .NET Core

@PaulWhite as soon as batch mode nested loops is a thing i can probably retire

nested loops should be retired

apply is legit

eh

sql.kiwi/2019/06/apply-versus-nested-loops-join.html

so if we're going to batch mode anything, it should be apply (correlated join)

yeah

1:59 PM

nested loops with the join condition on the operator itself is almost always bad

or at least v risky if outer side cardinality estimates are wrong

i hear it's not sargable

batch mode spools

imagine if spools even kept up with modern temp table technology

be a different world

i have serious gripes with what PSP considers a sufficient skewness threshold

@ErikDarling lkr

@ErikDarling They could tweak it a bit but ultimately I think we need a query hint or something to say, hey, this is PSP

or a setting that allows you to adjust the threshold

er... and/or

2:12 PM

They really don't like adding knobs like that

no one likes a knob

People do dumb things with them

"insufficient"

Yeah

I remember the Votes example

@PaulWhite maxdop, cost threshold, fill factor, min server memory, create ag

anyway sys.configurations has some upper and lower bounds that could be used to prevent forest fires

but sure a hint like min/max grant percent would be useful for setting it per query

2:18 PM

Yeah I don't know seems like an advanced option

What the hell is skewness anyway

Math man

Perhaps SCOTUS should decide

i feel quite disenfranchised

lol

I think I might close the Tweeter for the day

one step ahead of you

not going to be a good time

2:24 PM

Oh what happened now.

i mostly just stay navigated to the mentions page

only look at feed when i'm in the porcelain suite

Yeah anything important tends to turn up in mentions

I use Twitter to send errata to Erik

90% of my dms

I use it to send one of the three gif memes I know

Anyway this washing up isn't going to do itself bbiab

2:26 PM

those are rookie numbers. Reminds me, I haven't made meme in over a month

yeah on the verge of unfollowing tbh

cold, but fair

2:53 PM

dishes washed

ah, fresh wine glasses

yep

ooh, new beavis and butthead movie

"waitForAllOwnersToOpen"
"waitForAllConsumersToBeReady"

yeesh

3:11 PM

were they not already there?

maybe not exposed in extended events before, if that's where you're looking?

or sys.dm_os_waiting_tasks

@ErikDarling they are documented e.g. in support.microsoft.com/en-us/topic/…

also mentioned in joshthecoder.com/2019/05/06/cxconsumer-slow-join.html

I explain what they mean at great length in sqlperformance.com/2021/05/sql-performance/… (five parts)

3:27 PM

@PaulWhite I can't believe that was three years ago.

time flies

3:43 PM

no i'm just remarking that it's a particularly nasty parallel deadlock situation over here

though this is the first time that i've noticed "<threadpool id=..." in the parallel deadlock xml

maybe i just ignored it before because there was nothing of value to parse out

one damn parallel merge join in the whole plan and it boinks everything

> boinks

root cause of many problems

if only twitter were a safe space to talk about sql server

one can but dream of such a world

4:03 PM

anyway, this is the first time i've actually forced a plan in query store and had it work

there were two plan variants. one serial, one parallel with only loop and hash joins.

i forced the serial plan 🌌

the PSP optimization reminds me of the first time i drank johnnie walker blue. i was all riled up on the hype around a $200 bottle of scotch. then it tasted like dessert syrup.

4:23 PM

suppose i'm gonna have to get real good and used to clearing out query store and the plan cache now eh

not spending any more time on it today, but in case it's helpful to anyone else in the transcript: think i've got the start of a snippet for extracting the useful text out of the wayback machine for old feedback.azure.com/forums/908035-sql-server pages

$archiveUrl = ""
$archive = Invoke-WebRequest $archiveUrl
$HTML = New-Object -Com "HTMLFile"
[string]$htmlBody = $archive.InputFields[0].outerHTML
$HTML.write([ref]$htmlBody)
$HTML.getElementsByTagName('article')[0].innertext

2

powershell, for the avoidance of doubt

you should export the whole site

honestly not a bad idea

just don't use local wordpress, you'll get a 404

see transcript for additional details

:-p

prob just dump to a GH repo

only 959 individual issues captured but i imagine it's the highest-traffic ones mainly search

4:35 PM

sounds like you're building a good first party solution

i think first party would be knowing who to contact at archive.org to fix the rendering bug :(

or who to contact at microsoft to get them to stop switching forums every 3 years

help@archive.org

help@microsoft.com

bit pricey for a prank

admin@erikdarlingdata.com

sa@erikdarlingdata.com

excuse you

😂

4:46 PM

well that was a fun way to test for nulls

WHERE ISNULL(p.ParentId, 1/0)

3

4:58 PM

That is a proper level of disdain NULL.

6:54 PM

@ErikDarling Amusingly, that expression might also return NULL instead of the expected division-by-zero error

7:12 PM

Also you have a syntax error

How does SQL with a syntax error get three stars

2

It took me way too long to detect it

7:29 PM

@PaulWhite with weird ansi setting or generally because of expression blah blah

@PaulWhite copy and paste error

more than anything

@ErikDarling SET ANSI_WARNINGS OFF;

SELECT
    c = COUNT_BIG(*)
FROM dbo.Posts AS p
WHERE ISNULL(p.ParentId, 1/0) = @ParentId

Amateur detected

yeah seriously

OMG just hit CTRL-K like everyone else

7:30 PM

used to more beginner-friendly chat platforms

heh heh heh

that looks terrible

why did you make me do that

bad father

I fixed it 4u

your backticks will not help you here

I mean they should, but who desires consistency really

as long as they don't give me lyme disease

ew

would that make you a lymey tho

7:33 PM

blymey lymey

is that what you call brits or am I confusing that with something else?

it is yeah

k

at least it used to be

ex-europeans now i suppose

hm yeah I guess

7:39 PM

after seeing where psp refuses to kick in, it's quite odd to see where it does kick in

i realize v1 is rarely the sterling product but did anyone kick this thing around first

no u

go to bed

@ErikDarling they do, of course, but they don't have many customer systems to play with

If you've turned CEIP off

and all the other secret backdoors

i'm a one man ceip

why can't i get a cookie

that's just the way the cookie crumbles

7:44 PM

8

i would have thought that if you had two parameter sensitive predicates that you could lead the optimizer to the important one with index key column order or index hints

but no

what an odd bird

that's crazy talk like expecting generate_series to produce dates