« first day (4189 days earlier)      last day (53 days later) » 

6:16 AM
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.
 
 
2 hours later…
8:27 AM
morning all
 
9:26 AM
A chairde - Morning all!
 
 
1 hour later…
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
 
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.
 
only if you play your cards right
 
if i could do that i wouldn't need josh
 
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?‎
 
@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
 
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
 
> 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
 
@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
 
@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
 
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/…
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.
 
 
2 hours later…
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
 
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
user image
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
 
nothing about databases produces dates
2
get a nice watch if you wish to cavort or carouse
 

« first day (4189 days earlier)      last day (53 days later) »