The Heap™ – Consultancy ©®

John K. N.

06:55

@bbaird Linux dd command

Boot into a Linux from USB Stick and use the dd command to create a copy of the existing disk to the new disk or even to an image file.

dd if=/dev/sda of=/dev/sdb

Zikato

07:16

Morning

John K. N.

Morning

Zikato

11:47

Is anybody here actively using Azure Portal?

John K. N.

12:52

What?

Zikato

14:01

The Azure Portal - portal.azure.com

Josh Darnell

@Zikato I haven't been, but I'm in it now. What's up?

Zikato

I'm trying to favourite some resources. When I copy the URL from address bar, it's not reusable. When I paste it, it redirects for 20 sec and then lands on homepage

And when I've pinned to Dashboard (create new) it works, but then the Dashboard disappears after a while - cannot be found in the Dashboard resources, even the notification link doesn't work

Sean Gallardy

14:44

Spicey comments for @Paul this morning

1

Q: Computed column marked as PERSISTED still calculates every time

I have the following table: CREATE TABLE dbo.Cases ( CaseCaption nvarchar(255), NickNameComputed AS (dbo.fn_ParseNickName(CaseCaption)) NOT NULL, NickNamePersisted AS (dbo.fn_ParseNickName(CaseCaption)) PERSISTED NOT NULL ) As you can see, I have 2 computed columns getting their valu...

Paul White

hm

Sean Gallardy

🍿🥤

John K. N.

15:05

Why don't people just answer the question?

Paul White

Fucked if I know

Anyway, it's closed as a duplicate now

I have closed this question as a duplicate as I believe it is the same issue in play (the persisted column needs to come first). As the author of the article you reference, I don't have much to add otherwise than is already stated there. If you believe your question requires a specific answer, edit it to include all the details needed to reproduce and I'll be happy to consider reopening it. Conor's commentary is not applicable for the reasons given in the duplicate Q & A. — Paul White ♦ 17 mins ago

Sean Gallardy

I saw that

I made popcorn for nothing

Paul White

You probably made the popcorn for the comment I wrote before deleting it and posting that one instead

Sean Gallardy

per-haps

Josh Darnell

CAKE - Perhaps, Perhaps, Perhaps (Official Audio)

►

Sean Gallardy

15:19

I hear they go the distance

Paul White

Erik will be along in a moment to mention the lakh of a NOEXPAND hint for computed columns

Conor's heart is in the right place anyway

Hannah Vernon

15:37

@PaulWhite I love your writing.

> People are not always very interested in the internal interactions and cost model deficiencies that led to the undesirable outcome.

Paul White

Thanks

Hannah Vernon

You'd think TF176 would be the default behavior, even if only under 4199

Paul White

Too much risk of people complaining about computed column matching no longer working as it used to, I suppose

Having two CCs the same (but only one persisted) in a table seems like an odd usage

...

Erik Darling

sounds like a world star problem

7

Q: Why does adding a computed column prevent predicate pushdown?

I have a weird situation that I don't quite understand. I have a table like this kinda: CREATE TABLE dbo.cc_demo ( id INT IDENTITY PRIMARY KEY, up_action INT, down_action INT, last_action_date DATETIME ); INSERT dbo.cc_demo ( up_action, down_action...

sql-server index sql-server-2017 computed-column

Paul White

That's related, but not the same issue

This one is specific to having two computed columns on the same expression

And the persisted one being second in table definition textual order

Demo for anyone interested: dbfiddle.uk/RmytHSWS

Erik Darling

15:51

Oh I know it’s different, that was more about me complaining about the lack of a hint

Paul White

Oh, right

Sounds like a sock puppet issue

I see they've been buggering about with inbox notifications again

Seems impossible to clear chat notifications from the main site without actually clicking to follow the link

Oh, I see now

You have to click the tiny closed envelope icon

42

Q: Inbox improvements are live

TL;DR: The inbox improvements we previously announced are now live (as of November 29th, 2022 13:30 UTC). Thank you to our beta testers! A huge thank you to moderators who helped us beta-test these inbox changes! Your feedback was invaluable, and we could not be here without you. New features Th...

discussion featured top-bar global-inbox announcements

Erik Darling

Nothing like some improvements

Hannah Vernon

@PaulWhite it certainly does

Paul White

@ErikDarling Can't argue with improvements

bbaird

Comparative negligence for the coffee spill onto my laptop + wireless keyboard: Cat: 60% (getting in my way) Me: 25% (knocking over coffee cup) Coffee Cup manufacturer 10% (top heavy, spill prone design) Coffee shop: 5% (using said coffee cups)

And like a true cat, he just went and barfed on the carpet

Hannah Vernon

15:56

lol

Erik Darling

You: 100% for getting a cat

3

Hannah Vernon

probably didn't like the coffee

so glad that AWS is renewing their sponsorship. When do we get paid again?

bbaird

@ErikDarling 75% my mom (for rescuing the cat years ago) 15% my wife (for wanting the cat) 10% me (for relenting to pressure)

Paul White

Also had an 'accident' earlier. I went into the bathroom to speak to my wife, who was cleaning her teeth. I had a glass quite full of cordial in my hand. She didn't hear me over the noise of the electric toothbrush and only noticed I was right behind her when she looked up into the mirror.

Hannah Vernon

trying to kill her with a heart attack?

Paul White

16:00

This startled her somewhat. So, she attacked me with the hand towel, loosening my grip on the glass and sending it flying.

Hannah Vernon

somewhat lol

Paul White

Was not impressed

Hannah Vernon

sounds completely reasonable

Erik Darling

doesn't sound very cordial to me at all

Paul White

Right

Josh Darnell

16:01

🥁

Paul White

I am hilarious, just quietly

Luckily, we have plenty of spare glassware

Erik Darling

she's probably still shaken up over the bathroom spider incident

Josh Darnell

@PaulWhite I got a good chuckle out of the story 😀

Paul White

Massive win really, since she also insisted on cleaning up the broken glass etc.

Zikato

You did that on purpose to get rid of that one ugly glass

bbaird

16:08

And cordial that wasn't up to your standards

Erik Darling

but did she get you a new drink

Paul White

Regrettably, no.

She was busy with the mop

@Zikato It wasn't a bad glass though. We just have a lot of them

They were given away free several years ago

Erik Darling

i would still like to know what the cordial in question was

Paul White

I think you had to buy 2L of Anchor milk or something

@ErikDarling I believe it was lemon, lime, and bitters with sodastream'd water

I'm not about to go lick the bathroom floor to check

And I'm sure she did a stellar cleaning job anyway

The story would've been perfect if the cat were also involved. Alas.

bbaird

@PaulWhite You can always embellish

Paul White

16:22

Might do that on my next retelling

Erik Darling

@PaulWhite you won't know until you tell it

Paul White

Is no one going to make the purr-fect pun at all

Erik Darling

👉👃

Paul White

You pretend to have such high standards

Erik Darling

no no, i have very high standards

for other people

Paul White

16:27

ah well that works

Erik Darling

16:53

i'm at the worst part in having written some code

where you're looking at it rather admiringly

and you know there's something horrible lurking in it

Hannah Vernon

lol, story of my life. Except I'm not writing code, I'm just fixing it. The prior DBA (and I use that term with reticence) had some very convoluted stored procs with cross-database references that are rather unseemly, and undoubtedly have something surprising lurking in there.

@PaulWhite Anchor milk? Sounds very strange.

Paul White

@HannahVernon anchordairy.com/nz/en.html

Hannah Vernon

also, why oh why are there so many nested cross applies in this code.

Paul White

Anchor (brand)

Anchor is a brand of dairy products that was founded in New Zealand in 1886, and is one of the key brands owned by the New Zealand based international exporter Fonterra Co-operative Group. In Malaysia, Singapore and Taiwan, Fonterra uses the Fernleaf brand instead of Anchor. == In New Zealand == Historically, the Anchor brand of milk products in New Zealand was owned by the New Zealand Dairy Group, which merged with Kiwi Co Operative in 2001 to form Fonterra. As the merger would leave New Zealand with virtually no competition in the domestic dairy sector, government legislation was required for...

Hannah Vernon

oh it's a brand. I see. And now I know that there is a Great NZ Bake Off, will have to watch that.

Erik Darling

16:58

@HannahVernon at some point various sql server luminaries were pushing the concept of "proxy cross apply"

where you nest cross apply in various ways to encourage better index usage

Paul White

Sounds Farley unlikely

Hannah Vernon

@ErikDarling it gives me the heebee jeebies just like DISTINCT does.

Erik Darling

@PaulWhite the idea has ben gan a long time

Paul White

SQL is verbose. It is what it is.

Hannah Vernon

loop joins a plenty is all I see

Erik Darling

17:00

more typing = better performance

Hannah Vernon

LOL

anyone reading this chat would have to be thinking "what the hell"

Paul White

@ErikDarling Isn't that the USP of all the sp_Blitz* modules?

Erik Darling

i wish i had the time to fix all the problems in those

Paul White

They'll never be finished. Best you can hope for is useful

Erik Darling

i think blitzlock is pretty close now

unless deadlock xml changes significantly

it would be nice to start them over without all the backwards compatibility requirements

blitzcache i would re-word all the cost nonsense

blitz i would yank all the security checks and much of the outdated stuff

blitzindex i would write a mode to actually script out clean up changes

Paul White

17:23

Doesn't 2022 come with some extra blocking process info or something?

Erik Darling

this is what a bpr looks like in 2022: gist.github.com/erikdarlingdata/…

Paul White

oh, are the stack frames new?

I saw something new could be disabled with TF 1241

Erik Darling

@PaulWhite that appears to be it, but i'm flummoxed as to what i could do with them

Paul White

they could be resolved to give clues as to what something was doing at the time?

Erik Darling

quite frustrating that they add all this new nonsense but haven't fixed the wrong object id showing up

@PaulWhite how would one resolve them?

Paul White

17:30

idk

the usual way?

Erik Darling

-_-

Hannah Vernon

while we're fixing everything, can we make it so no one ever uses BETWEEN and IF always has a BEGIN...END

Paul White

have you checked the eXtensive documentation

I wonder what the guid and rva are for

@SeanGallardy might know

Erik Darling

call stacks for a blocking problem seems excessive

Paul White

not for a skilled escalation engineer

Erik Darling

17:40

do you know any of those?

Sean Gallardy

@PaulWhite rva so you don't need the loaded modules

Paul White

oh, relative virtual address

Sean Gallardy

yeah that way if you know the module then you know the function and code without needing the loaded modules, since modules can be loaded at any address

explanation for the channel

Erik Darling

how does this help with query blocking

Sean Gallardy

you know the callstack

Erik Darling

17:47

...

Sean Gallardy

It's helpful in debugging the issue

without dumps

Erik Darling

...

why do you need dumps for queries blocking one another

Sean Gallardy

Someone has a copyright on ellipsis

depends on the blocking

For example, in HADR, database startup might be blocked

or it might be blocking

Erik Darling

but that's not query on query blocking

that's ha/dr nonsense that no one understands anyway

Sean Gallardy

no, but blocking isn't just on queries

locks are used all over the place

Erik Darling

17:51

all the blocking i'll deal with is :D

Sean Gallardy

not to mention spinlocks

Paul White

can SQL Server resolve those with the usual XE trace flags n stuff?

Sean Gallardy

I don't see why not

I haven't tried though

Paul White

me either

Sean Gallardy

If I don't have a dump I use the callstack resolver because I am lazy and already have public orprivate symbols

Paul White

17:53

yep

and add an extra zero to the invoice

Hannah Vernon

IF @error_number = @deadlock_error --Deadlock encountered seems like a worthy comment, don't you think? /sarcasm ofc

Erik Darling

isn't that self-documenting

Sean Gallardy

needs more UDFs

Hannah Vernon

@ErikDarling one would think

Paul White

@ErikDarling Can't tell without seeing the value @deadlock_error contains

Hannah Vernon

17:57

or, for that matter, what value @error_number contains

Paul White

same as all comments then

Hannah Vernon

pretty much

lol, fuck why

    -- If you get here, skip the error part and go to finished
    GOTO FINISHED

this is even better: SELECT @mail_recipients = @mail_recipients;

how could anyone ever type that

Sean Gallardy

Sounds like someone getting paid per character

hence why people use spaces

Hannah Vernon

lol it does

Sean Gallardy

because we all know tabs are better

Hannah Vernon

18:06

@SeanGallardy tabs are only better in a bar

Sean Gallardy

LIES!

that's where you don't want tabs

Hannah Vernon

clearly we've diverged

Paul White

00 4D2C0D6165614261A81A98ADD091C0F01+0xA90E4
01 A6938CA537B6444591837D01C597342C1+0x9971A
00 SqlDK!ThreadScheduler::SwitchWorker+293
01 SqlDK!SOS_Scheduler::Switch+107
02 SqlDK!SOS_Scheduler::SuspendNonPreemptive+257
03 SqlDK!WaitableBase::Wait+271
04 sqlmin!LockOwner::Sleep+1176
05 sqlmin!lck_lockInternal+7411
06 sqlmin!GetDataLock+930
07 sqlmin!BTreeRow::AcquireLock+672
08 sqlmin!IndexDataSetSession::GetRowByKeyValue+3910
09 sqlmin!IndexDataSetSession::FetchRowByKeyValueInternal+724
0a sqlmin!RowsetNewSS::FetchRowByKeyValueInternal+1089

@ErikDarling That's your example ^

Resolved using SQLCallStackResolver

Hannah Vernon

kind of makes my comment comment look silly

Sean Gallardy

The top of that stack is eXceptional

Paul White

18:13

I didn't bother with the kernal stuff

It's just wait for single object or whatever

Sean Gallardy

yeah

another common one is SignalAndWait

I liked Erik's video today, although him without a beard was different

Paul White

wat

Ginger

If anyone here would be interested in supporting a proposed SE site for programming language design and implementation, follow this proposal, and check out the chat room! We need more votes on questions

3

fear not; I have authorization

Paul White

confirmed authorised

107

Programming Language Design

Proposed Q&A site for designers and implementers of computer programming languages

Currently in definition.

Erik Darling

18:30

@Ginger the real ginger would propose such a thing

Ginger

I got ninja'd

Paul White

@ErikDarling We don't listen to people without beards

Ginger

bold of you to assume I don't have a beard d:

@PaulWhite can you 11 that post to mention that we need more votes on questions? I forgot to add that in :p

Erik Darling

@PaulWhite that video is three years old

beard is in place

Paul White

explains why I can hear you then

@SeanGallardy with the 3yo info

Sean Gallardy

18:43

I said today's video, I didn't say it wasn't recorded 3 years ago

Paul White

@Ginger Sounds like someone wants to buy the Extended Authorship Package after all

@SeanGallardy You also said tabs were better than spaces, so at least your disinfo is consistent

Ginger

@PaulWhite I've got more Gingercoin™

Sean Gallardy

I see Mods who spilled their cordial aren't very cordial today

:P

Paul White

Consistency is key

Especially for the slow learners

Ginger

wrong room q:

mousetail

18:59

I noticed, I couldn't find the message I was trying to reply to. That should have cued me in

J.D.

@HannahVernon Worst I've seen in production code (C# though) was a method whose only line of code was if (1 == 1) return true;. The method was actively being called. To be fair, it was in a class with a literal 40,000 line comment. Actually, that was probably the worst I ever saw. Good times...

Ginger

@J.D. ಠ_ಠ

Hannah Vernon

@J.D. sounds like it was less painful to remove the content of the function than to change where ever it was called.

@J.D. seems like they could have just return true; though

J.D.

There was no truth anywhere in that wall of code. I'm just glad I don't have to support it anymore.

Sean Gallardy

One would think that the function would just be optimized away though

Hannah Vernon

19:05

one of those "just in source for fun" things

Erik Darling

yes, the way sql server deals gracefully with 1 = 1

Sean Gallardy

I don't know how C# optimizes though

Erik Darling

@HannahVernon i just pushed all the changes from the block viewing code to main on github, if you'd like to see the final product

Hannah Vernon

@ErikDarling awesome - sorry I've been soooo busy lately that I haven't had a chance to turn around, let alone look at github.

been doing like 5 merges a day for a couple of weeks on our internal code base.

Erik Darling

oh no worries, just thought i'd let you know since you contributed stuff to it

i wonder why i wrote code that did datediff(s, d1, d2) / 1000 instead of just using ms

signs and wonders

Paul White

19:15

I hear more code is faster code

Erik Darling

obvs

i ran into a very funny thing with udf performance

Paul White

Why were you dividing by a thousand though

Math man

Ginger

@PaulWhite ~~well golflangs are often slow so I guess that's right~~

bbaird

Ginger

my eyes, they bleed

bbaird

19:22

IEAGL: youtube.com/watch?v=bgaCCxwUml8

Erik Darling

@PaulWhite i have no idea, this was code i wrote drunk on a plane six years ago

anyway, the udf thing was basically this rewrite: gist.github.com/erikdarlingdata/…

Cade Roux

if (three > 70) {
three = three + 1900;
}
else {
three = three + 2000;
}

Where "three" was the integer converted from a 2-character string input field representing the year of a date (one and two were the ints converted from the month and year input fields).

Still haunts me to this day. The guys who wrote it were Clipper programmers and I got them fired about a month or two into the job. "I never really got the hang of that C programming" was the line on the way out the door.

How about you just name your variables? Dealing with C is enough work - making your brain work extra hard is not worth it. I guess if there weren't horrible bugs in the code I desperately needed to fix so we could ship the produ\ct, I wouldn't have minded so much.

Erik Darling

i think a programmer support group se site would be a hit

codetherapy or something

Ginger

"Tell us about your self."

2

Cade Roux

I've forgotten many bad programmers over the years but I will always remember Carl and Dave.

Zikato

19:28

@PaulWhite Looks like the cat got their tongue

Paul White

Mmm

@ErikDarling on my phone but was it inlined?

Erik Darling

@PaulWhite no, neither version was marked as inlineable

Paul White

19:46

Right but it was on 2019+ and you checked the execution plan

Without inline I can quite believe there was a significant difference but 10x seems a little high

Zikato

The gist says 100x

Paul White

With inline, maybe I would be less surprised

@Zikato Yes but Erik thinks you divide seconds by 1000 to get milliseconds

Zikato

But you do. Am I missing something?

Paul White

Anyway I expect he'll blog about it with a full reeeeepro

@Zikato Please refer to the transcript

If something takes 5 seconds, it didn't take 0.005 milliseconds

Could drive a man to drink

Zikato

yeah, I found it now

Paul White

19:55

Well I hope you learnt a valuable lesson

Zikato

It's a performance optimization. DBAs hate this one simple trick

3

Paul White

😀

Zikato

Ginger

20:08

@ErikDarling so do you still want these or

Paul White

20:18

Ha ha ha

Erik Darling

20:29

@Ginger my price has quadrupled

@PaulWhite maybe. maybe not. they didn’t have before and after query plans unfortunately. I sort of assumed that functions marked as not inlineable wouldn’t be inlined.

Of course I’m not very smart / 1000

e cores and all that such

Sean Gallardy

20:49

You two make such a cute sorority team

Paul White

@ErikDarling learn.microsoft.com/en-us/sql/relational-databases/…

HtH

Erik Darling

@PaulWhite I’m mobile at the gymnasium. What am I looking for there?

Paul White

21:08

I'm mobile too. Inline is automatic at CL150

You have to turn it off via database scoped config or INLINE = OFF

Or do you mean it can't be inlined?

Like not eligible at all

IIRC multiple RETURN statements disallow it

2 hours ago, by Erik Darling

anyway, the udf thing was basically this rewrite: https://gist.github.com/erikdarlingdata/a74996099ff9bbef9c5171c2df78f9b3

So I don't have to go searching for it again

If the second one can be inlined, a bad plan could explain the huge performance difference

Erik Darling

21:26

I see what you mean, yeah

They patched SQL Server 2019 from RTM to CU18 but no other settings changed

Then a few days later the function started misbehaving

Anyway I thought it was amusing

Sean Gallardy

Someone is going to make a voodoo doll and call it Patches

Apparently patches break everything

In b4, "I am having horrible performance issues":
https://dba.stackexchange.com/questions/320351/running-a-sequential-trigger

J.D.

lol I'm surprised no one's said anything in that one yet.

Erik Darling

@SeanGallardy have you tried not breaking everything with patches?

Sean Gallardy

@ErikDarling I have not, should I try it? Will I dream?

Erik Darling

21:41

@SeanGallardy hard to know, I’ve never broken anything with a patch 🫠🫠🫠

Sean Gallardy

technically neither have I

Haha, well... got pinged already about a possible bug in 2022

Where's my magic 8 ball

Erik Darling

It’s definitely a bug

Sean Gallardy

I shook the virtual magic-8 ball and it said, "Yes definitely"

I guess don't try to enable filestream in 2022 express edition

Erik Darling

Ew

@SeanGallardy you may be interested in this thread: twitter.com/sql_handle/status/…

Not sure I’ve seen that wait type go very high before

Sean Gallardy

Hmm

That's a pretty high number to start with, ~800 million every ? interval

Erik Darling

21:56

I thought it was interesting too

But I’m not sure what causes that wait to begin with

J.D.

In the vein of silly and redundant code, I caught EF Core generating the following code after an INSERT today: SELECT MyIdentityColumn FROM MyTable WHERE @@ROWCOUNT = 1 AND MyIdentityColumn = SCOPE_IDENTITY(); instead of just SELECT SCOPE_IDENTITY(); lol

Erik Darling

At least they didn’t use merge for an insert?

J.D.

True, that's minimally appreciated.

Erik Darling

22:25

@AngryHacker in my example there are two Compute Scalars for the first example and only one for the second. Only the first example computes the expression (scalar function in this case). The second one simply projects the value. When I'm next at a computer, I will move this to chat because I get the feeling I will need to draw pictures. — Paul White ♦ 1 hour ago

🥸🥸🥸

Paul White

23:03

I'm enjoying learning to read execution plans

It can wait until later

Erik Darling

23:18

i can suggest some new avatars for this chat session

something artful

Sean Gallardy

23:57

😂😂😂 that zinger by Paul

Transcript for

The Heap™ – Consultancy ©®