The Heap™ – Consultancy ©®

1:01 AM

@ErikDarling ah good timing. Looked apocalyptic here for a bit lol

Q: admın ما جربته بالفعل بالتهييجـ علاج السحر✔️|☎️00966578385770☎️✔️ سوار روحاني ✔️معالج روحاني

2:11 AM

-2

admın ما جربته بالفعل بالتهييجـ علاج السحر✔️|☎️00966578385770☎️✔️ سوار روحاني ✔️معالج روحاني

mysql

2:35 AM

@J.D. yeah looks like real shit.

2:48 AM

My backyard at like 4pm today

3:32 AM

Wow

Blame Canada

2

4:04 AM

Yea no bueno. I'm surprised how strongly it spread all the way down here instead of dispersing over such a large area on its way.

I haven't paid much attention to the news on the fires themselves though. Hope all's well your way.

4:44 AM

4:58 AM

@JoshDarnell they're wrong more often than Jack Dolphin

About the voting buttons. It's quite hard to tell I have already voted on a post. It sort of changes colour a little bit. But if I cancel my vote, it lights up much more. What sort of nonsense is this

6:47 AM

Morning

Looks like SO is trying to fight its way out of the discussion, without talking to those affected by the bad/wrong/one-sided decisions that have been made.

Look, we've got the data and we don't trust moderators and/or the community to detect ChatGPT answers, and because of the fluctuation of users [clue: due to bad decisions made on our side] we are going to allow AI generated content

1032 have signed the letter

Zikato

Morning

Wordle 719 4/6*

🟩🟩⬛⬛⬛
🟩🟩⬛⬛⬛
🟩🟩⬛⬛⬛
🟩🟩🟩🟩🟩

9:23 AM

> I am just learning from ChatGPT. I was doing alright in MS Access more user friendly-ish. I just felt I needed a more professional database since it could hold more data and most employers want Microsoft SQL Server and not Access. Id rather spend my time learning SQLServer than keep learning Access since employers rarely want Access experience.
And yes I have only used this program for about 2 hours so far I am very new.

Comment:

> I'm going to echo what /u/SirGreybush said. You need to take a basic, structured, preferably well-edited/checked (for accuracy) SQL Server course. You should don't be using ChatGPT for this.
Using ChatGPT at this stage, you don't know the right questions to ask it (or the right way to ask the questions you have), **nor do you know when it's giving you an accurate/correct answer.**

11:21 AM

@J.D. I didn't know yards existed in NYC!

Independent of whether you care about RavenDB or not, this looks like it will be an interesting series of posts: Integer compression: delta encoding + variable size integers

For those in the room that are interested in storage internals / compression.

Reminds me of vardecimal for SQL

11:39 AM

Apr 1 at 22:42, by Erik Darling

I had no idea

Never saw one when I stayed there for 6 months

That's because you don't read the transcript

It's served me well for years, why change now

@SeanGallardy I'm further into Long Island, in the suburbs. Probably an hour-ish east of Eric. More yardage out this way.

Ahhh ok, was never out that way.

That's nice you have some space :)

11:54 AM

For sure. 🙂 It's not the biggest of properties (real estate is still a little crowded out here) but it's just right for what we wanted. Enough room for the dog to runaround, etc.

Meanwhile, I'm over here thinking of getting starlink and buying a 979 acre lot in Wyoming

Heh sounds nice. Bet the stars are amazing at night.

@SeanGallardy Highly recommend the east end of Long Island in the Spring / Summer, if you get the chance. North Shore > South Shore unless you're looking for ocean waves.

1000000% no light pollution

@J.D. The ocean and I don't get along well (says the guy who lives on the coast in Florida)

lol uhoh

Then yea definitely North Shore > South Shore LI if you ever come to visit.

If I come up to NY again I'll have to do a tour, see Erik again and stop by and annoy you

11:59 AM

Hah. I'd be down.

@SeanGallardy I found a spot, surprisingly in NY, that has the greatest star sights I've ever seen. Better than Iceland (though I saw the Northern Lights in Iceland, so that's a whole different awesome celestial beast).

You could look up in any direction and see a shooting start every half a minute, and the sky was so abundant with star light.

It was upstate about 7 hours though, near Lake Placid (where they have Olympic Ski jumping). Middle of the woods, barely any phone service.

That makes sense, I spent a bunch of time around Salamanca and Cuba. Also a great state park on the PA side right below Corning that has 0 light pollution.

It really is something I wish more people would do/have access to

@JohnK.N. it does look like that, but they have provided a messenger for the mods to converse with.

@SeanGallardy for sure. I love star gazing. Is Corning just south of Allentown or it's somewhere else in PA?

Ah no, you must mean Corning, NY but south of the border to PA?

It's in the middle of nowhere

yeah corning, ny

Tioga is the closest thing to it

which... isn't saying much

Yah, I gotcha now. Cool. I'll keep that in mind if I'm in that area.

A few hours from Lancaster, but I have family in Harrisburg, so a little closer.

12:06 PM

almost a straight shot down to Harrisburg

Wordle 719 2/6*

🟩🟩⬛🟩⬛
🟩🟩🟩🟩🟩

Got lucky on the rando opening there

Yea not too bad. Probably hit up Lancaster this August.

@HannahVernon oOo very nice. 👍

Going to get some Amish made items :D

@SeanGallardy whoopie pies for dayz

@J.D. cheers!

12:09 PM

@J.D. 😂😂😂😂😂

Cheese and sweet bologna too. 🙂

It's always the food that gets me (wherever I go) lol

☝️☝️☝️☝️☝️☝️☝️☝️

@PaulWhite looks awesome!

Minus the really cool purply celestialness on the right side of that picture, and that's roughly what the sky looks like on a clear night up in Lake Placid.

There really are heaps of stars

12:11 PM

Looks quite stacked.

How far is that from you? I would imagine it's a bit more rural there?

Ha, most of NZ is rural

Stonework on that church? looks great

@PaulWhite fair enough, never been. Hope to one day.

> Recently, 4,300 square kilometres of New Zealand’s South Island was recognised as an International Dark Sky Reserve, the largest reserve of this type worldwide.

oOo. Many shooting stars to be seen there, I'm sure.

12:27 PM

We just got a chicken coop and chicks yesterday.

@JoshDarnell Reds?

@JoshDarnell Nice

@SeanGallardy Yep, Rhode Island Reds! There were supposed to all be red, but closer inspection since they've been home some of them might be Ameraucana.

Weird, normally the Ameraucana are way darker, black

If you get some blue eggs, you'll know!

@SeanGallardy Haha well, the Ameraucana chicks are lighter with a dark stripe. I guess they get more uniformly dark as they get older. Or they really are all Reds and we're just crazy 😅

Let me see if I can post a picture.

1:04 PM

@JoshDarnell neat, thanks

Just A Weird Bug I Found In Some Client Code

By the way, @Erik, this just blew my mind:

►

Q: How to merge staging data in a specific order when the target table has foreign keys

@J.D. where on LI are you again?

@JoshDarnell it had me really messed up for a bit. I had no idea what was happening.

0

I am trying to modularize the merging of staging data for our branches. Currently, I get an export file (zip) from our system supplier once per day per branch (50+ branches). Via a series of Azure functions, I extract the zip, which gives a series of tsv files, I then upload each tsv file to a st...

sql-server

@J.D. why not one stored procedure that calls related stored procedures in the correct order? That was the task could still remain somewhat parallel.

@JoshDarnell The 365 really got me :D :D :D

@JoshDarnell Yeah some of those are a bit darl

Lamak

Wordle 719 3/6

⬜⬜⬜🟨⬜
⬜🟩🟩🟩⬜
🟩🟩🟩🟩🟩

1:21 PM

Wordle 719 6/6*

⬛🟨⬛🟨⬛
🟩⬛🟩⬛⬛
🟩⬛🟩⬛⬛
🟩🟩🟩⬛⬛
🟩🟩🟩🟩⬛
🟩🟩🟩🟩🟩

@ErikDarling Hang on wait a minute that would update the whole table @JoshDarnell

UPDATE t SET t.id = 1 FROM (different table) just says update every row in the table called t if there are any rows in the (different table). Hence the semi join (exists).

UPDATE t SET t.id = 1 wouldn't puzzle anyone

It's just a variety of accidental cross join so possible when using UPDATE ... FROM

I suppose one lesson is not to have tables named the same as aliases you often use?

@ErikDarling Old Bethpage

She was our matchmaker

@PaulWhite it was even weirder in the client code, but yeah.

Oh I'm sure. Simplified repro and all

@ErikDarling sure, you could still refactor the Header and Lines code into their own procedures, so it's cleaner and modular still, agreed. I wouldn't say 1 procedure that calls all the other tables procedures too though. Just 1 per set of related work.

1:36 PM

@PaulWhite they were using full table names for aliases and had two very similarly named tables, etc etc.

@J.D. historic Old Bethpage or regular Old Bethpage?

@ErikDarling @Josh dbfiddle.uk/wHpJqKbt

artisan historic old beth page, we called her

@ErikDarling By historic, you might mean the Old Bethpage Village Restoration? That's more of a fairgrounds / point of interest as opposed to a town. But it's right up the road from me.

@PaulWhite Oof, that's rough. I'm just so surprised the parser allows that.

@JoshDarnell It's the quite famous (I thought!) problem. Some asked for UPDATE FROM syntax to be deprecated.

@PaulWhite Well I've clearly been missing out haha!

1:41 PM

Yes, but you have chooks, so you're still ahead

Like, dbo.T is not defined in that query specification in a FROM or JOIN clause, a CTE, a derived table of some kind - nothing "updateable."

@PaulWhite I wasn't familiar with that NZ slang before now 😁

You can write UPDATE dbo.T without a FROM clause though.

It might be nice if the server warned you with syntax like that, but hey heaps of things would be nice

I mean, you might also legitimately want to update every row of T if TT contained any rows idk

At some stage, one has to take a bit of responsibility for the SQLs one writes

3

@PaulWhite Good point. In my mind, when you use the UPDATE...FROM syntax, I start thinking more in terms of a SELECT query. Which I guess is (part of) the problem with that syntax.

It also only "works" because "dbo" is the default schema for the user

One advantage of MERGE is it forces you to write an ON clause to relate the source and target

(and it enforces deterministic changes)

@J.D. I thought there was a section of town that was preserved older houses or something

@PaulWhite gets weirder with a primary key on dbo.t as well

1:55 PM

@ErikDarling dbfiddle.uk/F0Cw2NtG?

@ErikDarling yea I'm pretty sure you're thinking of the Old Bethpage Village Restoration. (I thought it was an actual town too before we moved here 3ish years ago.) It is a bunch of old farm houses / buildings, but no one actually lives there. It's more of a museum that emulates what Old Bethpage was like back in the 1800s. They have a blacksmith, etc.

They do events there, like Rise of the Jacko-lanterns , etc. Otherwise normally they're just putting on a show of old time lifestyle.

@ErikDarling Also dbfiddle.uk/vgNBqE5y

Being a blacksmith must have been rewarding work, I think

Sounds like a p cool job, given the times.

Hot n sweaty more likely

Unless you had a more modern forge with A/C

@PaulWhite Favorable for those Alaskan blacksmiths.

@JoshDarnell FWIW, I think this came up in the transcript a few weeks ago. Charles Face had all the insight on it.

2:28 PM

On blacksmithing?

heh sure

Out of curiosity, who here actually actively uses the Query Store to troubleshoot issues?

Forrest

@J.D. o/

I do

In what ways do you guys find it most useful? I've had it on for a while now but have found only using it once in a blue moon. Forced a query plan once, which worked out pretty well. I mostly just looked at Regress Queries with it. But I don't find it being a usual go-to tool for me so far.

Though interestingly today in Regressed Queries, I found a query that had like 100 different query plans apparently.

@J.D. Err, once. Didn't work.

2:47 PM

@J.D. I use my stored procedure to look at it mostly.

@ErikDarling oOo, sp_QuickieStore?

Yeah

If you want to use the GUI, this is an example of what I do: erikdarlingdata.com/…

Also with some details on procedure usage

@ErikDarling oOo nice, thanks! I'll check it out. I haven't used sp_QuickieStore much yet, but maybe I'll find more use with it over the raw Query Store.

I’ve been doing some more work on it again recently

Cool cool.

2:59 PM

@ErikDarling btw it's not that I think it's an obvious issue to miss. Using table names as aliases would certainly make it harder to spot.

The really funny thing about it was a whole part of an application had been working wrong for years and no one realized it

Not an entirely unfamiliar story

I hope the tables weren't actually called T and TT though 👀 lol.

The general consensus was “this explains a lot of things”

I wonder if they'll build you a statue

Should write that into contracts

3:14 PM

Indemnification, statue erection, all standard boilerplate

Neat

4:02 PM

@ErikDarling so apparently sorting the Grid view doesn't actually happen on the grid itself (locally on the client side). Seems like it's part of whatever query Query Store uses under the hood.

So if you're looking at Top Resource Consumers in Grid format, and you want to sort the query sql text column of the Grid, it doesn't sort the data in front of you, it just returns the top 25 queries it has tracked alphabetically.

That's not cool.

Just came across this in some code that's used to re-skin (theme) a hierarchy of controls in an app based on a user's preferences.

''' <summary>
''' This eerily named function skins all of the children of the specified Control.
''' </summary>
''' <remarks></remarks>
Public Sub SkinChildren(Parent As Control)

5

lol

Noice

From the days when real discipline existed

4

I got a good chuckle out of it. And it is some nice, clear, properly commented code 🤣

4:20 PM

Someone just pinged me on Teams ranting about lakhs

I thought you all would appreciate that

Lakhs context

Closed, By Design

As it has been 1 crore times before

(1 crore = 100 lakh)

Sean uses the dozen, gross, and great-gross units

As I hear is common in the US

Hectare, really

But some of my hectares have stones so hard to say which to use

Mar 14 at 17:16, by Paul White

In the US, they probably prefer fahrenseconds

Long hundred

The long hundred, also known as the great hundred or twelfty, is the number 120 (in base-10 Hindu-Arabic numerals) that was referred to as hund, hund-teontig, hundrað, hundrath, or hundred in Germanic languages prior to the 15th century, and is now known as one hundred twenty, or six score. The number was translated into Latin in Germanic-speaking countries as centum (Roman numeral C), but the qualifier long is now added because English now uses hundred exclusively to refer to the number of five score (100) instead. The long hundred was 120, but the long thousand was reckoned decimally as 10 long...

> twelfty!

4:31 PM

@PaulWhite Do you measure your speed in barleycorns per beard-second?

@SeanGallardy I try not to move, so it's never really an issue

But we use km/h here

Or kph

blasphemy

Well, no one has figured out decimal time yet

The maximum road speed limit is generally 100kph = 27.7777777777777... m/s

A few roads have 110kph

No one wants all those recurring decimals on road signs though

So we're stuck with hours

Could raise it to 180 kph maybe, for 50 m/s

Or lower it to 90 kph for 25 m/s

180 kph is pretty fast

4 u

4:39 PM

I'll do 140-150 over some of the bridges here

but not 180

@PaulWhite a bushel and a peck

Also, I'm enjoying the no Mod stuff, front page looks hilarious right now

I'm trying not to look

I only look to see how wrong Jockey Dolphin is

2

@PaulWhite Best thing

4:55 PM

Lol, any of y'all watch that program where they dig and dig in that hole on that island and never find anything?

The Curse of Oak Island

The Curse of Oak Island is a multi-season reality television series that chronicles an eclectic team of treasure hunters and their search for legendary treasure on Oak Island, off the shore of Nova Scotia, Canada. It is an American television production that premiered on the History network on January 6, 2014. The show features what is known as the Oak Island mystery, showing efforts to search for historical artifacts and treasure.The tenth season premiered on November 15, 2022. == Premise == The Curse of Oak Island follows brothers Marty and Rick Lagina, originally from Kingsford, Michig...

@CadeRoux Yup

What's up

They've been digging in there for 10 seasons just on TV and found nothing

They had one episode where some "expert" comes on and they take some measurements and he's got all these numbers up on the whiteboard. Like 12, 60, 360, 720. And then he goes: "this is sacred geometry".

lol yep

At that point, I just had to laugh at my brother for watching it.

Digging is also being generous

4:58 PM

Dude, 720 is just twice 360, so that's not sacred.

I'm not digging expert, only have a few years of experience as a child laborer

but one would think you could dig down as they do with quarrying

Bob's your uncle

Yes, 5, 12, 60, those are linked to time measurement since maybe before the Mayans. So they are good numbers, but this isn't anything sacred or mysterious.

So now any time someone throws out a number and they are acting like that number is important, my brain goes "sacred geometry", and then sometimes I even say it out loud.

@ErikDarling I get one right every once in awhile at least. 🙃

Me: How wide is the new fridge going to be
Wife: 36"
Me: Sacred geometry

2

TWSS

I like watching it (background noise) for some of the historical items they bring up

learning via osmosis

5:09 PM

cs.uwaterloo.ca/~csk/spectre

I'm kind of surprised this still has a positive score. Currently +80 / -51.

5:22 PM

Probably hasn't hit a wide enough audience yet?

@CadeRoux The premise of this, even if it was a good show, doesn't sound 10 seasons worthy lol.

@J.D. the aggregate in the order by is a neat trick

Thanks! Saves a step. :)

What will they think of next

Parallelized INSERT INTOs without using a TABLOCK hint?

I jest because I was reading this one earlier.

5:36 PM

Ah yeah, back when there were things to talk about

Unrelated, but I think once you told me Parallel Operators in the execution plan don't always have the correct time stats showing. Am I remembering that correctly?

@J.D. It's going to have infinite number of seasons. They can keep making new holes.

So definitely worthy from a "content" producers' point of view

@CadeRoux infinite seasons is some sacred math...

@J.D. well, confusing timing. Accounting for operator times is a bit messy but parallel exchanges are DOPly so.

@ErikDarling heh, fair enough.

I'm wondering if other operators are not very revealing too. E.g. I have an execution plan that is literally a SELECT <- Remote Scan. The Remote Scan operator shows 2s but the overall execution time is 4s.

I'm assuming that extra 2s is the work actually happening on the remote side, and the execution plan on the local side doesn't see those time statistics. That's my guess right now at least.

6:26 PM

@CadeRoux I don't think so, they have maybe 1 or 2 more left in them.

6:38 PM

@J.D. Paul has an article that goes into a lot of the quirks and details about per operator times: Understanding Execution Plan Operator Timings

+82 / -52 now

@JoshDarnell oOo nice, thank you! 🙂

@SeanGallardy *bald eagle noises*

:D

@J.D. maybe check the plan wait stats? Might not pick up anything interesting though.

7:01 PM

@ErikDarling yea, on the local side the only wait stat is OleDB which makes sense for a remote server call, but the wait time is only 1s or so, so I don't think it adds up still. If I run the remote procedure directly, it's runtime matches the remote calls runtime, so the procedure does truly take 4s.

I'm thinking it's just a lack information the execution plan is providing, which is fine. Was just a curious if that's normal with remote operations in plans.

is "spam" VTC no longer a thing? just "Doesn't belong here" now? 🤔

ah damn, it's flag, not VTC

well at lease the vote buttons are bigger so i didn't forget to vote

Look at this scab.

Talking about flagging and voting.

😜

oh right

forgot we're not supposed to be doing that

:-|

homer disappear dot gif

¯\_(ツ)_/¯

i'll flag my flag to remove my engagement and make someone else the scab

genius or madness? who's to say

7:11 PM

It's devious.

or maybe i'll fit myself for a blackhat and start picking up the review queues like a nerdy pinkerton

reject conformity, embrace chaos

@J.D. can you get the plan on the remote side?

@PeterVandivier go for it

@ErikDarling I can. I can post both plans if you're interested. But it's a non-issue, more just a curiosity, so don't waste any time unless it's interesting.

@ErikDarling Boring local plan and not so boring remote plan.

7:32 PM

https://support.microsoft.com/en-us/topic/kb4541096-fix-access-violation-occurs-when-you-run-query-on-computed-columns-in-sql-server-e470d071-8ee1-7880-29c9-42f1cc8283c2
^^^ still happening on SQL Server 2019 CU20, even though it was "fixed" in CU4

I would first check the session wait stats to see how much async network io is involved

@PeterVandivier You do you, boo 😘 I was just messing around, to be clear.

@ErikDarling Best way to do that?...sp_WhoIsActive?

FWIW, I'm discarding the results in SSMS.

No: learn.microsoft.com/en-us/sql/relational-databases/…

Gotcha 👍

7:36 PM

Also have you considered batch mode for the remote side

Seems like it would be useful

In what way?...columnstore indexing?

erikdarlingdata.com/…

No doubt. I've read before, but will re-visit.

Do you suggest that because of the current high DOP?

No because it seems to be joining large tables with no real predicates aside from joins

True. I don't even remember what this query does. Probably haven't looked at in 2+ years.

Good thoughts though, thanks.

FWIW, ASYNC_NETWORK_IO seems relatively low. waiting_tasks_count = 20 and wait_time_ms = 270.

CXPACKET and CXCONSUMER are quite large in comparison.

7:52 PM

Naturally

> <Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="2661" WaitCount="824"

> <QueryTimeStats CpuTime="3668" ElapsedTime="3097" />

Interesting...

Is that remote or local

Local

> Timesheet.TimesheetEmployeesExtended

8 mins ago, by Erik Darling

Naturally

That view is a real piece of crap

Quite possible. :)

hmm, looking at the code of it now. It's not the prettiest thing I've seen, but nothing terrible jumps out at me, other than it has a bunch of LEFT JOINs.

Nvm I take it back. Found a poopy predicate.

8:06 PM

@HannahVernon The same callstack?

I believe so - it's the same problem as I had before, which never did get completely resolved. I can turn of 176 and not have the dump occur.

I don't have time to try to repro it right now unfortunately, just way too much going on

it seems to be only a problem in one of our environments right now, and I don't even have time to figure out why.

It's weird because all environments should theoretically be practically 100% identical

100% identical from a T-SQL code perspective, database schema perspective, and they are all running the same CU and patch levels.

the data is different

Two poopy predicates actually. I guess that's why they call it a deuce.

if I remove the persisted computed column from the query it runs just fine.

totally unrelated; seen in an sqlpackage-generated T-SQL script:

> WAITFOR DELAY '00:00.010';

before adding a constraint

Quite polite of it to wait...

9:45 PM

@J.D. Naturally

9:56 PM

@HannahVernon Sounds like an identical environment problem

I suppose you tried disabling adaptive joins

@ErikDarling In any case, that view runs in under a second already. Not gonna spend any brain power righting the wrongs in it lol.

11:29 PM

@J.D. thanks for bringing it up 😃