The Heap™ – Consultancy ©®

Zikato

06:16

Morning

@PaulWhite can you recommend the XML book? I've watched Michael Rys's presentation on effectively shredding the XML in SQL. But I don't do that often enough so the knowledge doesn't stick.

ypercubeᵀᴹ

08:27

morning all

Vérace - Слава Україні

09:26

A chairde - Morning all!

Paul White

10:50

@ErikDarling Was asleep. Appreciated and starred now.

@Zikato It's this one 🔼

Erik Darling

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

Paul White

11:05

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

Erik Darling

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.

Zikato

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

Paul White

Yes Michael Rys is the go to for everything xml

Erik Darling

practically invented it

Paul White

@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

Zikato

11:14

We use XML with Service Brokers a lot

Erik Darling

oh the deadlock xml

yeesh

Paul White

@Zikato makes sense

Zikato

blocked_process_report xml as well

Paul White

You DBAs. So crazy.

Erik Darling

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

Paul White

11:15

Living your best life

Zikato

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

Paul White

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

😀

Erik Darling

datadabbles

2

Paul White

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

Erik Darling

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

Paul White

11:18

right

Erik Darling

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

Paul White

though that does sound a bit like regex

slippery slope

Erik Darling

only regex i know

i think i even goofed that up

Zikato

.*

Paul White

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

a dark path

Zikato

11:20

You can always do that manually

Paul White

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

Zikato

with or without the glyph?

Erik Darling

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

Paul White

@Zikato Oh with for sure

Erik Darling

what views do you join perfmon counters to???

have i been missing something all these years?

Paul White

11:22

controversial ones, apparently

Zikato

maybe they are saving snapshots

My favourite counter is Deprecated Features

Paul White

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

Erik Darling

install bdc x f

Paul White

4u

Erik Darling

i can't wait until ags are deprecated

Paul White

11:32

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

Erik Darling

i have no soul apparently

Paul White

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

Erik Darling

setting up ola jobs

Zikato

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

2

Paul White

ha prob true

Erik Darling

11:42

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

Paul White

200k if it's a DAG

Zikato

You are getting paid?

Erik Darling

paul charges by the thought

Josh Darnell

@PaulWhite hey

Paul White

oh hai J D

Erik Darling

11:53

uh oh

are my twitter cards broken

Josh Darnell

They're about to be.

Erik Darling

:O

Zikato

only if you play your cards right

Erik Darling

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?

Erik Darling

11:58

sir this is arby's test kitchen

Paul White

> ‎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?‎

Zikato

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

Paul White

@willianpereira You might be looking for that site ^

Erik Darling

so what's everyone doing this weekend

alright cool

fun bunch

John K. N.

12:17

morning

yes

Paul White

@ErikDarling reading a book about xml

Erik Darling

are you trying to forget xml or the weekend

Paul White

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

Might have a Picnic

🍷

Erik Darling

such a lush

Josh Darnell

@ErikDarling RUNNING / nothing.

You?

Erik Darling

12:28

not running

Zikato

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

Erik Darling

well we know what you're having for breakfast

Zikato

not sure I follow?

Paul White

always a risk in this chat

Erik Darling

12:45

same

Paul White

huh wrong chat room lol

→ 1 message moved to Cricket Chat

Erik Darling

crikey

Paul White

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

Certainly not me I have a busy weekend ahead

Michael Green

'Morning

John K. N.

@Zikato You've got to ask Erik to JEAGL

Zikato

12:52

@ErikDarling JEAGL pretty please

John K. N.

> 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.

Michael Green

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

John K. N.

SAT: Standby due to RFC / SUN: Swimming

Erik Darling

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

"oatmeal"

3

Michael Green

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

Erik Darling

12:57

@MichaelGreen is it complicated?

Michael Green

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

Zikato

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

Erik Darling

it's a common salve for anything that itches

Michael Green

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

Zikato

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

Josh Darnell

13:01

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

Michael Green

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

Zikato

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

Peter Vandivier

@MichaelGreen u/

Paul White

@ErikDarling ew

@MichaelGreen quite right too

@Zikato needs to be on its own line

Zikato

ohh

One day I'll master the chat

Paul White

13:08

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"

Erik Darling

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

that's just disturbing

Paul White

It's not entirely out of character

Zikato

cultural difference

Erik Darling

perhaps for you island folk

going right to cannibalism

Paul White

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

Obviously

Erik Darling

13:09

this keeps getting worse

Zikato

I think I'll skip breakfast

Paul White

Else there'd be nothing left for dinner

Erik Darling

just the memories

Paul White

Still less cruel than foie gras

Michael Green

@PaulWhite like a ripe Brie

Paul White

13:11

Just like that yes

Erik Darling

those ducks had it coming

Michael Green

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

Paul White

My Siri voice is currently South African female

Not because it sounds especially South African

It's just the least annoying

Michael Green

How eclectic!

Paul White

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

Erik Darling

13:15

does it sound like yolandi

Paul White

@ErikDarling Is that a brand of oatmeal

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

I find it hilarious

Erik Darling

currently downloading new siri voices

Paul White

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.

Erik Darling

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

Paul White

Chocolate Ass Cream

►

@ErikDarling aren't you eeyore tho

Erik Darling

13:25

only when i'm not drinking

Michael Green

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

Erik Darling

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

Paul White

@ErikDarling Please sir to refer to transcript

11 mins ago, by Paul White

Not because it sounds especially South African

Erik Darling

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

Michael Green

@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.

Erik Darling

13:34

my blogs are apparently not a good form of salesmanship

dunno if that's what you want

Paul White

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

mustaccio

@PaulWhite But views must be breathtaking

Michael Green

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

Paul White

I think it'll make some things trickier to debug

Other things will no longer need debugging

Michael Green

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

Paul White

13:38

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

Michael Green

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

Paul White

Or 15

but yeah

Erik Darling

i think it's 16

one run to cache the plan

15 for it to figure out there's a problem

Paul White

> five more times

grumpy robot donkey

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

Michael Green

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

Erik Darling

13:44

if they really cared they'd just make everything adaptive

Paul White

INNER ADAPTIVE JOIN hint anyone?

Erik Darling

option(adaptive join)

just a nice way of telling merge joins to beat it

Michael Green

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

Erik Darling

if you can find it in query store

John K. N.

ALTER DATABASE SCOPED CONFIGURATION....

Michael Green

13:50

Eventually queries on query store will be self-optimizing

Paul White

@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.

Michael Green

@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?

Paul White

right

Josh Darnell

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

Paul White

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

Josh Darnell

13:56

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

Erik Darling

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

Paul White

nested loops should be retired

apply is legit

Erik Darling

eh

Paul White

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)

Erik Darling

yeah

Paul White

13:59

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

Erik Darling

i hear it's not sargable

Paul White

batch mode spools

Erik Darling

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

Paul White

@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

Erik Darling

or a setting that allows you to adjust the threshold

er... and/or

Paul White

14:12

They really don't like adding knobs like that

Erik Darling

no one likes a knob

Paul White

People do dumb things with them

Erik Darling

"insufficient"

Paul White

Yeah

I remember the Votes example

Erik Darling

@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

Paul White

14:18

Yeah I don't know seems like an advanced option

What the hell is skewness anyway

Math man

Perhaps SCOTUS should decide

Erik Darling

i feel quite disenfranchised

Zikato

lol

Paul White

I think I might close the Tweeter for the day

Erik Darling

one step ahead of you

Paul White

not going to be a good time

Josh Darnell

14:24

Oh what happened now.

Erik Darling

i mostly just stay navigated to the mentions page

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

Paul White

Yeah anything important tends to turn up in mentions

Zikato

I use Twitter to send errata to Erik

Erik Darling

90% of my dms

Paul White

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

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

Zikato

14:26

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

Paul White

yeah on the verge of unfollowing tbh

Zikato

cold, but fair

Paul White

14:53

dishes washed

Erik Darling

ah, fresh wine glasses

Paul White

yep

Erik Darling

ooh, new beavis and butthead movie

"waitForAllOwnersToOpen"
"waitForAllConsumersToBeReady"

yeesh

Paul White

15:11

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)

Josh Darnell

15:27

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

Paul White

time flies

Erik Darling

15:43

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

Paul White

> boinks

root cause of many problems

Erik Darling

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

Paul White

one can but dream of such a world

Erik Darling

16:03

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.

Erik Darling

16:23

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

Peter Vandivier

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

Erik Darling

you should export the whole site

Peter Vandivier

honestly not a bad idea

Erik Darling

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

see transcript for additional details

Peter Vandivier

:-p

prob just dump to a GH repo

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

Erik Darling

16:35

sounds like you're building a good first party solution

Peter Vandivier

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

Erik Darling

[email protected]

bit pricey for a prank

Peter Vandivier

[email protected]

Erik Darling

[email protected]

excuse you

Peter Vandivier

😂

Erik Darling

16:46

well that was a fun way to test for nulls

WHERE ISNULL(p.ParentId, 1/0)

3

Josh Darnell

16:58

That is a proper level of disdain NULL.

Paul White

18:54

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

Paul White

19:12

Also you have a syntax error

How does SQL with a syntax error get three stars

2

Zikato

It took me way too long to detect it

Erik Darling

19:29

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

@PaulWhite copy and paste error

more than anything

Paul White

@ErikDarling SET ANSI_WARNINGS OFF;

Erik Darling

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

Paul White

Amateur detected

Erik Darling

yeah seriously

Paul White

OMG just hit CTRL-K like everyone else

Erik Darling

19:30

used to more beginner-friendly chat platforms

Paul White

heh heh heh

Erik Darling

that looks terrible

why did you make me do that

bad father

Paul White

I fixed it 4u

your backticks will not help you here

I mean they should, but who desires consistency really

Erik Darling

as long as they don't give me lyme disease

Paul White

ew

would that make you a lymey tho

Erik Darling

19:33

blymey lymey

Paul White

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

Erik Darling

it is yeah

Paul White

k

Erik Darling

at least it used to be

ex-europeans now i suppose

Paul White

hm yeah I guess

Erik Darling

19:39

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

Zikato

no u

Erik Darling

go to bed

Paul White

@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

Erik Darling

i'm a one man ceip

why can't i get a cookie

Paul White

that's just the way the cookie crumbles

Zikato

19:44

8

Erik Darling

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

Paul White

that's crazy talk like expecting generate_series to produce dates

Erik Darling

nothing about databases produces dates

2

get a nice watch if you wish to cavort or carouse

Transcript for

The Heap™ – Consultancy ©®