The Heap™ – Consultancy ©® - 2022-11-29

« first day (4347 days earlier) ← previous day next day → last day (527 days later) »

6:55 AM

@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

7:16 AM

Morning

Morning

4 hours later…

11:47 AM

Is anybody here actively using Azure Portal?

1 hour later…

12:52 PM

What?

1 hour later…

2:01 PM

The Azure Portal - portal.azure.com

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

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

2:44 PM

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

sql-server sql-server-2017

hm

🍿🥤

3:05 PM

Why don't people just answer the question?

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

I saw that

I made popcorn for nothing

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

per-haps

CAKE - Perhaps, Perhaps, Perhaps (Official Audio)

3:19 PM

I hear they go the distance

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

3:37 PM

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

Thanks

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

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

...

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

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

3:51 PM

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

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

Nothing like some improvements

@PaulWhite it certainly does

@ErikDarling Can't argue with improvements

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

3:56 PM

lol

You: 100% for getting a cat

3

probably didn't like the coffee

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

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

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.

trying to kill her with a heart attack?

4:00 PM

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

somewhat lol

Was not impressed

sounds completely reasonable

doesn't sound very cordial to me at all

Right

4:01 PM

🥁

I am hilarious, just quietly

Luckily, we have plenty of spare glassware

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

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

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

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

4:08 PM

And cordial that wasn't up to your standards

but did she get you a new drink

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

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

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.

@PaulWhite You can always embellish

4:22 PM

Might do that on my next retelling

@PaulWhite you won't know until you tell it

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

👉👃

You pretend to have such high standards

no no, i have very high standards

for other people

4:27 PM

ah well that works

4:53 PM

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

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.

@HannahVernon anchordairy.com/nz/en.html

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

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

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

4:58 PM

@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

Sounds Farley unlikely

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

@PaulWhite the idea has ben gan a long time

SQL is verbose. It is what it is.

loop joins a plenty is all I see

5:00 PM

more typing = better performance

LOL

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

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

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

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

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

5:23 PM

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

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

oh, are the stack frames new?

I saw something new could be disabled with TF 1241

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

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

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?

5:30 PM

idk

the usual way?

-_-

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

have you checked the eXtensive documentation

I wonder what the guid and rva are for

@SeanGallardy might know

call stacks for a blocking problem seems excessive

not for a skilled escalation engineer

5:40 PM

do you know any of those?

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

oh, relative virtual address

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

how does this help with query blocking

you know the callstack

5:47 PM

...

It's helpful in debugging the issue

without dumps

...

why do you need dumps for queries blocking one another

Someone has a copyright on ellipsis

depends on the blocking

For example, in HADR, database startup might be blocked

or it might be blocking

but that's not query on query blocking

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

no, but blocking isn't just on queries

locks are used all over the place

5:51 PM

all the blocking i'll deal with is :D

not to mention spinlocks

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

I don't see why not

I haven't tried though

me either

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

5:53 PM

yep

and add an extra zero to the invoice

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

isn't that self-documenting

needs more UDFs

@ErikDarling one would think

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

5:57 PM

or, for that matter, what value @error_number contains

same as all comments then

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

Sounds like someone getting paid per character

hence why people use spaces

lol it does

because we all know tabs are better

6:06 PM

@SeanGallardy tabs are only better in a bar

LIES!

that's where you don't want tabs

clearly we've diverged

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

kind of makes my comment comment look silly

The top of that stack is eXceptional

6:13 PM

I didn't bother with the kernal stuff

It's just wait for single object or whatever

yeah

another common one is SignalAndWait

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

wat

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

confirmed authorised

107

Programming Language Design

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

Currently in definition.

6:30 PM

@Ginger the real ginger would propose such a thing

I got ninja'd

@ErikDarling We don't listen to people without beards

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

@PaulWhite that video is three years old

beard is in place

explains why I can hear you then

@SeanGallardy with the 3yo info

6:43 PM

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

@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

@PaulWhite I've got more Gingercoin™

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

:P

Consistency is key

Especially for the slow learners

wrong room q:

6:59 PM

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

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

@J.D. ಠ_ಠ

@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

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

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

7:05 PM

one of those "just in source for fun" things

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

I don't know how C# optimizes though

@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

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

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

7:15 PM

I hear more code is faster code

obvs

i ran into a very funny thing with udf performance

Why were you dividing by a thousand though

Math man

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

user image

my eyes, they bleed

7:22 PM

IEAGL: youtube.com/watch?v=bgaCCxwUml8

@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/…

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.

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

codetherapy or something

"Tell us about your self."

2

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

7:28 PM

@PaulWhite Looks like the cat got their tongue

Mmm

@ErikDarling on my phone but was it inlined?

@PaulWhite no, neither version was marked as inlineable

7:46 PM

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

The gist says 100x

With inline, maybe I would be less surprised

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

But you do. Am I missing something?

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

yeah, I found it now

7:55 PM

Well I hope you learnt a valuable lesson

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

3

😀

user image

8:08 PM

@ErikDarling so do you still want these or

8:18 PM

Ha ha ha

8:29 PM

@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

8:49 PM

You two make such a cute sorority team

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

HtH

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

9:08 PM

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

9:26 PM

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

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

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

@SeanGallardy have you tried not breaking everything with patches?

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

9:41 PM

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

technically neither have I

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

Where's my magic 8 ball

It’s definitely a bug

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

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

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

Hmm

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

9:56 PM

I thought it was interesting too

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

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

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

True, that's minimally appreciated.

10:25 PM

@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

🥸🥸🥸

11:03 PM

I'm enjoying learning to read execution plans

It can wait until later

11:18 PM

i can suggest some new avatars for this chat session

something artful

user image

user image

11:57 PM

😂😂😂 that zinger by Paul

« first day (4347 days earlier) ← previous day next day → last day (527 days later) »

Transcript for

Nov '2229

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile