The Heap™ – Consultancy ©®

Vérace

02:32

A chairde - Morning all!

Wordle 1,321 4/6*

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

Yano_of_Queenscastle

02:48

Morning.

I know timezones are thing, but don't you have like 3 AM in Ireland?

Though I am not one to judge, it's 4AM here.

Paul White

03:28

Best time to be awake. Fewer annoying humans around

Yano_of_Queenscastle

Sure. I like that time, too.

But it kinda fucks up the rest of my schedule. I am sure afternoon-me is going to be annoyed at 4AM-me for not sleeping.

Vérace

07:08

@Yano_of_Queenscastle My sleeping isn't so good at the moment - I had snoozed for a while but awoke at 02:30 (2:30 AM in freedom units) - where is it 4AM when the UTC time is 02:48? Assuming that the Heap uses UTC in it's display? It appears to do so here.

@Yano_of_Queenscastle Me too - the grumbling's already started!

John K. N.

07:53

Wordle 1,321 4/6*

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

Zikato

Wordle 1,321 4/6*

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

Paul White

08:17

Wordle 1,321 4/6*

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

Yano_of_Queenscastle

09:30

@Vérace I rounded up a bit, but Czechia is at UTC+1 (at times, at least). I think The Heap™ actually uses some local time. At least for me it always shows right time.

Paul White

Yes, chat shows local time.

Zikato

10:06

Is Erik ok?

Vérace

11:12

@Yano_of_Queenscastle From here, only some obscure archipelagoes use that time zone!

Charlieface

11:50

@Vérace Greenland is neither obscure (according to Trump) nor an archipelago. Although I got the impression it was +1 not -2 being discussed.

Yano_of_Queenscastle

13:40

@Vérace We use the boring Central European Timem that is UTC+1 or UTC+2, depending on seasons. We might be small country, but no obscure archipelagos here!

Erik Reasonable Rates Darling

15:16

@Zikato much better now, thank you for your kind attention

Vérace

@Charlieface Its population is less that of Lisburn - that's *obscure!! :-)

@Yano_of_Queenscastle Oh - when I search for "Queenscastle" I get some obscure place in Missouri (or Wyoming... or somewhere else I may have flown over... ). :-) Oops, just checked - it's even worse than I thought - it's in Indiana - saw it once (Gary) from the viewing gallery of the John Hancock building in Chicago!

Charlieface

I very much doubt Trump has heard of Lisburn though...

@Vérace Might be referring to this one...

Windsor Castle

Windsor Castle is a royal residence at Windsor in the English county of Berkshire, about 25 miles (40 km) west of central London. It is strongly associated with the English and succeeding British royal family, and embodies almost a millennium of architectural history. The original castle was built in the 11th century, after the Norman invasion of England by William the Conqueror. Since the time of Henry I (who reigned 1100–1135), it has been used by the reigning monarch and is the longest-occupied palace in Europe. The castle's lavish early 19th-century state apartments were described by the art...

Sean Gallardy

15:36

git pull
Waits 20 minutes
Found 0 objects to send
Waits 30 minutes
Received 83000 objects
Waits 10 minutes
Resolving 53000 deltas
<scrolls a billion lines>
Waits 5 minutes
Updating files
Waits 5 minutes
More scrolling

Do a git pull, go eat lunch

Josh Darnell

16:03

That's a big git repo.

Sean Gallardy

4u

John K. N.

@SeanGallardy SQL Documentation?

Sean Gallardy

Nah, they don't have that much documentation

Vérace

The manual for emacs?

Sean Gallardy

or vi, take your pick

Erik Reasonable Rates Darling

16:19

sean downloaded an optimizer

Josh Darnell

I'm sure it's a huge, old codebase, but I wonder if it's ALSO contaminated by large (binary) objects. That can make cloning brutal.

Erik Reasonable Rates Darling

16:48

powerbi dashboards and whatnot

Yano_of_Queenscastle

@Charlieface It's more or less literal translation of the my city's name :-) It was one of cities reigning kings gave to queen to support her.

Erik Reasonable Rates Darling

does this mean chat has ✅✅

Charlieface

Interesting, so even though it was often known as Krajova in Hungarian and German, it actually comes from králové in Czech which means queen, rather than the unrelated Slavonic root krajina (or krai) which means border.

Yano_of_Queenscastle

Seems like it, but better to double czech.

Sean Gallardy

@JoshDarnell That it does, and yes it does

Charlieface

17:05

Czech has often interested me: we have a guy from Moravia (Morava) who told me his dialect of Czech is actually closer to Slovak than to Praguer Czech

Yano_of_Queenscastle

I always assumed they speak like that mostly to confuse tourists from Prague. The better the connection between the capital and the regions, the more homogeneous the language becomes.

Zikato

17:21

@Charlieface I call bullshit

Erik Reasonable Rates Darling

kecy

Zikato

Kecy v kleci

Yano_of_Queenscastle

Dunno, I would find it pretty believable - 70 year ago.

Zikato

@Yano_of_Queenscastle we're neighbours

Yano_of_Queenscastle

Basically :D I know you mentioned you're from Pardubice.

Charlieface

17:48

@Zikato Dunno, that's just what he told me, I think he said he's from Ostrava or some such.

But I can czech again

Erik Reasonable Rates Darling

okay, stored procedure compile time parameters aren't in the execution plan. there's no optimize for unknown hint, there are no local variables in use, and parameter sniffing is not turned off as a database scoped configuration. is there anything else that would mess that up?

Charlieface

@ErikReasonableRatesDarling Sorry what situation are you talking about? Also what are "compile time parameters"?

Erik Reasonable Rates Darling

in query plans

compile time parameters are stored with their values

but not in these!

Forrest

I feel like I've seen this before, but I'm trying to remember context

Charlieface

Oh I see you mean the parameters used when the plan was cached.

WITH RECOMPILE maybe?

Erik Reasonable Rates Darling

17:52

nope

Forrest

psp?

Erik Reasonable Rates Darling

with recompile you can get the literal values out from the xml

Charlieface

Given the parameter names it looks like a Linq2SQL ad-hoc batch, not a procedure?

Erik Reasonable Rates Darling

it takes more work but they're in there

@Forrest i've only ever seen it in the contexts i listed

@Charlieface the example i'm showing you is when they're there, it's more difficult to show you an example of when they're not there 😃

Charlieface

Always Encrypted?

Erik Reasonable Rates Darling

17:56

i don't see anything in sys.column_encryption_keys

Forrest

I don't remember if psp messes with them

Erik Reasonable Rates Darling

@Forrest no psp, compat 130

Forrest

gotta ask, TFs?

Charlieface

forced parameterization?

Erik Reasonable Rates Darling

@Forrest none i can see, but azure sql db so no dbcc trace status. nothing listed in the query plan either.

@Charlieface no, but that wouldn't mess with things that are already parameterized

Forrest

18:04

ooooh, Azure SQL DB, that's where I think I saw it

but no other ideas, sorry

I assume you can repro from an ssms call?

Erik Reasonable Rates Darling

this is where the plot thickens

some parameterized statements do have them (like the one above). the one i need to repro does not have them.

🥴

there must be something silly in the way the application calls this

the statement looks like any other parameterized execution

(@list of parameters) select...

oh son of a bitch

i think it's because they get passed to an itvf

wait no they're used elsewhere too

some are passed to the itvf, others are used in a where clause

i wonder if the itvf pokes eyes for all of them

let's see

okay no that's not it

Charlieface

18:21

"Baggins is stuck..."

Erik Reasonable Rates Darling

you can bet your pipe weed on it

Zikato

Maybe this?
https://learn.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing-details?view=sql-server-ver16#table-variable-deferred-compilation

Erik Reasonable Rates Darling

it's not a table variable

there is no table variable in use

Zikato

oh, it's not table variables in your example

Erik Reasonable Rates Darling

oui mon ami tchèque

table variable contents are never available

Zikato

18:38

Yeah, I was thinking about interleaved execution. Couldn't recall the name

Sean Gallardy

18:49

sounds like you've been to MARS

Josh Darnell

Maybe sp_prepare is being used? brentozar.com/archive/2018/03/…

I don't actually know if that obscures params in the plan. It just came to mind.

Erik Reasonable Rates Darling

19:04

i'm waiting to get more details from the developer(s)

i don't think sp_prepare or mars is in use

.net sql provider and all that

0

Q: stored procedure gets max startDate

I have this stored procedure that I want the max startDate base on AgentId or without the AgentId. The way I am doing this is using a if else and wanted to see if there was a better way to do this? IF (@AgentId = 0) BEGIN SELECT top (1)max(cgv.StartDate) as AgentLatestPublishedD...

sql-server

comment answers abound

Sean Gallardy

lots of them today

Erik Reasonable Rates Darling

there oughtta be a law

Sean Gallardy

we don't need more laws

maybe there should be a law about laws

Zikato

20:34

What is it? Some kind of Law Law Land?

Erik Reasonable Rates Darling

HyperLawLaw

Paul White

20:51

@Erik How exactly are you obtaining the execution plan

FORCE_SHOWPLAN_RUNTIME_PARAMETER_COLLECTION help?

Erik Reasonable Rates Darling

21:21

@PaulWhite grabbing it from query store

@PaulWhite Dunno, can’t go messing with production at the moment

Zikato

@ErikReasonableRatesDarling Sorry, I've just noticed

Paul White

@ErikReasonableRatesDarling Ok, well it's a mystery then. If it were me, I'd try collecting plans in different ways to see if it was specific to QS or not. Then again, you might be OPping and there's some crucial detail missing.

Zikato

Down Periscope - Fart In A Sub

►

Erik Reasonable Rates Darling

@PaulWhite i will update when i have a response from the developers. i was hoping there was something obvious that i neglected to check.

Paul White

Did you try not using Azure SQL Database

That has Sean's sticky fingers all over it

A wonder anything works

Zikato

21:27

AI will fix it

Sean Gallardy

@PaulWhite My fingers are very clean, I wash my hands half as much as an obsessive compulsive person

Erik Reasonable Rates Darling

perhaps one of you can spare a cigarette

waiting is quite trying

i suppose i could descale my espresso machine

maybe take a shower

Zikato

21:45

don't forget to invoice for your wait stats

Erik Reasonable Rates Darling

i will also invoice on all of your behalves

Paul White

I'm surprised you're not busy in their test/staging/qa environments

Erik Reasonable Rates Darling

the query doesn't run naturally there

and without the half a dozen input parameters...

it seems like it's worth solving the parameter mystery before i go making up parameter values

who knows what oddities are at play

huh people still call themselves vmware experts

Paul White

22:37

@ErikReasonableRatesDarling Do you have a plan available for the curious

Erik Reasonable Rates Darling

@PaulWhite yes, when I’m back at my computer I’ll email it 2u

AI haters build tarpits to trap and trick AI scrapers that ignore robots.txt

Paul White

Seems like trying to push the tide out

Erik Reasonable Rates Darling

Typical moderator, quashing resistance

Paul White

22:56

2

A: Stored procedure to get max startDate with optional AgentId

Current solution Your version is certainly not the worst. Main problem is you have the same code twice, which has some maintenance overhead (i. e. all changes must be applied to both code blocks). But T-SQL is not Java or C# - it doesn't give you as many tools for code deduplication (and many of ...

Answer renders comments obsolete

Quashed

If I were a picky sort of person, I would say a flag would have been helpful

But I am not, so

Yano_of_Queenscastle

Hopefully next time people who answer questions will be more considerate.

Paul White

Hope springs eternal

Erik Reasonable Rates Darling

23:16

The Sun also rises

Paul White

And the tide comes in

@ErikReasonableRatesDarling ty

Erik Reasonable Rates Darling

least i can do

i'm a sporting chap

don't tell the ducks

Paul White

23:41

@ErikReasonableRatesDarling QueryCompilationReplay = 1 is interesting

Paul White

23:57

As is ParentObjectId = 0. I thought this was a stored procedure?

Transcript for

The Heap™ – Consultancy ©®