The Heap™ – Consultancy ©®

Paul White

12:40 AM

try cast as lord is not null

is that a cast down

Criggie

No idea - reminds me of
`$ su - jesus.christ`
su: user jesus.christ does not exist

Paul White

Wordle 1,115 2/6*

🟩🟩🟩⬜🟨
🟩🟩🟩🟩🟩
💅🏼

Criggie

How do you do monosopaced font in chat ?

this mono font

does it not work for a whole line ?

Paul White

No markdown works with line breaks

Criggie

test
this `mono` font
third line

huh - interesting, thank you

mustaccio

12:44 AM

strikes again innit

Paul White

It's quite a famous thing in here

Criggie

I blame Jira

Paul White

> Chat markdown strikes again

I would have quoted one of the 6348784356 previous messages but on phone

mustaccio

do you have that number on speed dial

Criggie

I keep 867-5309 on speed dial

mustaccio

12:49 AM

If that's a joke, EAGL might be in order

then I'll explan 2-12-85-06

Criggie

Its a song youtube.com/watch?v=axLRUszuu9I

867-5309/Jenny

"867-5309/Jenny" is a song written by Alex Call and Jim Keller and performed by Tommy Tutone that was released on the album Tommy Tutone 2 (1981) through Columbia Records. It peaked at number four on the Billboard Hot 100 and number one on the Rock Top Tracks chart in April 1982. The song led to a fad of people prank calling unsuspecting victims by dialing 867-5309 and asking for "Jenny". == Creation == Lead guitarist Jim Keller, interviewed by People in 1982, said: "Jenny is a regular girl, not a hooker. Friends of mine wrote her name and number on a men's room wall at a bar. I called her on a...

Its on wikipedia - must be true.

I still blame Jira.

Erik Darling

1:17 AM

Having a nice wedding time

Thank you for coming

Criggie

6:24 AM

Zikato

9:35 AM

@PaulWhite Very nice. Had to google the emoji and its meaning though

Zikato

9:52 AM

@ErikDarling The 914,4 meters stare

Zikato

10:21 AM

@ErikDarling is branching out - www-edarling-cz.translate.goog/…

Paul White

11:02 AM

he is fond of dates

Josh Darnell

11:38 AM

Two interesting posts from Oren about dealing with snapshots / MVCC: Efficient snapshotable state followed up by Cloned Dictionary vs. Immutable Dictionary vs. Frozen Dictionary in high traffic systems

Erik Darling

11:52 AM

@Zikato tequila sodas were working overtime last night

@PaulWhite invoice due dates maybe

is @JoshDarnell a ravendb stan account

Josh Darnell

1:44 PM

I am.

Erik Darling

Boeing agrees to plead guilty to conspiracy to defraud the US government

Peter Vandivier

2:46 PM

🤯

Hannah Vernon

4:23 PM

@Criggie our local POTS phone system has that number playing "Jenny" when you call it. Its a quaint, antiquated system.

@ErikDarling what is the justice department asking companies for their opinion now? I mean "agrees to plead guilty" makes it sound like that.

Also I didn't read the article, pretty clearly

Paul White

5:02 PM

The last time I read about that particular Woeing incident, they did have a genuine choice to make. The DoJ charges were questionable, but going through a trial and being found guilty comes with potentially much more severe consequences. Being a company with a criminal record is apparently quite inconvenient, especially if you're in the government contracts business.

Peter Vandivier

the big shocker for me in that article is the absence of the phrase "the company will admit to no wrongdoing"

low bar to jump for sure, but generally speaking i assume corporate megaliths get to pay out the settlement and have no legal finding of fact against them

maybe that'll still happen, but the phrasing "plead guilty" implies not i reckon

Paul White

Haven't read the latest yet, but I also seem to recall they were a few days away from end of a probationary period when the incident occurred.
They don't have much luck

Peter Vandivier

v inconsiderate of the planes to not wait a few more days before falling out of the sky

Paul White

Right!

> If approved, the new deal will be Boeing's first new felony in decades.

Well, that's not so bad then

Peter Vandivier

reset the big number on the day counter

it has been 0 days since we did crime

shame that pride month is over, they could've yassified the news cycle with a whole "be gay, do crimes" marketing blitz

Paul White

5:11 PM

Tricky period for them. I hope the CEO is well compensated for his troubles

Peter Vandivier

won't someone think of the shareholders?!?

Paul White

@Zikato The deadlock you sent me is reasonably well explained already, though it's a little handwavy in places. The main question I think, is why the resources are acquired in the opposite order. One takes COMPILE then Sch-S on stats, the other has Sch-S on stats, then looks to acquire COMPILE. The only role of the UPDATE STATS is to do the partitioning thing on Sch-M so the normally compatible Sch-S requests end up as part of the cycle.

We understand now how a e.g. a filtered index can cause a COMPILE lock to be requested on a table (more details below).

When queries compile, they can load indexes and stats at different times, on demand, depending on when the optimizer needs the information for various purposes (not just cardinality estimation). It's perfectly possible for one query to acquire Sch-S earlier than the other (they are different queries, after all). These locks aren't released until the compilation is complete, so you just see increasing refcounts if requests are repeated.

One query happens not to load that stat early. After it acquires COMPILE, it finds it needs the stats during the binding process for the index filtering predicate. It can't get it due being on a scheduler with a Sch-M partitioned lock. The other query acquires Sch-S successfully due to being on a higher numbered scheduler. Later, it finds it needs to rebind the filtering predicate and can't get the required COMPILE lock, and we have a deadlock.

I suppose the other thing to be clear on is this is the result of run-of-the-mill statement-level recompilation for the two queries (not the stats update job). COMPILE locks for dependencies like filtered index predicates are acquired on recompile. COMPILE locks for procedures are only acquired on a fresh, complete compile.

If it helps, the thing the COMPILE lock is protecting in the filtered index case is inserting the PROCHDR cache entry for the bound tree corresponding to the filter predicate.

--- ends ---

Zikato

5:31 PM

I've read the whitepaper and couldn't find what are the rules for the fresh stored proc compilation vs the statement recompilation

Paul White

Statement-level recompilation is very much the norm. Whole-procedure recompilation used to be the only possibility of course. I think it's safe to say the only time you get a whole proc compile now is when the proc isn't in cache.

Zikato

But that's what I'm after right? It only compiles again if the proc / bound tree is not in the cache. So I'm trying to find out why was it removed from the cache

Paul White

@Zikato No, there are two distinct issues. Whole procedure compile is not relevant for the deadlock case. Plans are cached in OBJCP (or SQLCP). Bound trees are cached in PROCHDR. They're quite separate.

So, you need a COMPILE lock on the procedure if you're planning on inserting a plan for it into the OBJCP cache. You need a COMPILE lock on the closest lockable thing if you're going to be caching a bound tree (aka algebrized tree, normalized tree).

For a SQL expression on a filtered index, the table gets the COMPILE lock.

As mentioned, proc plans are really an array of plans, each which can be recompiled without affecting the proc plan or needing a proc COMPILE lock.

Zikato

Hmmm, I can't check for the deadlock anymore but I can see if I still have plans for the blocking issue from last week.

So does that mean that each of those blocked procs was trying to access a filtered index?

Paul White

Or one of the other many causes of non-proc COMPILE locks

Zikato

5:43 PM

right

so it could be just the ordinary statement-level recompile after all (for example due to stats update)

Paul White

I've forgotten the details already, but I think you said you saw COMPILE locks on tables for the blocking scenario as well

Zikato

yes

Paul White

Computed columns (with or without scalar functions) and filtered indexes are the main things to look for

It took me a while to repro the deadlock because I was deliberately triggering stale stats refresh, which loaded the stats too early

Zikato

triggering them early manually or via auto_stats?

Paul White

Auto

It just happens to cause stats meta to be loaded before the index filter is checked for rebinding

Rebuilding an index is a trigger for COMPILE as we established already, but pretty much any change that bumps the metadata version will suffice.

I've been using ALTER INDEX name ON schema.table SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON)

Zikato

5:50 PM

So for the blocking scenario, the real blocked resource was obfuscated by the COMPILE locks? I still don't understand what other things were at play that it caused blocking this time but statement recompiles happen all the time

Paul White

Because it's quick and easy. You don't even need to change the current setting.

Zikato

I'll have to try repro eventually - just too much stuff piled on currently

Paul White

@Zikato The deadlock scenario is top of my stack right now. I don't recall the blocking scenario in sufficient detail to say. Perhaps I'll re-read it.

Zikato

That's fair

Paul White

Plus, there's all this Boeing stuff

Paul White

6:06 PM

Jul 4 at 9:20, by Zikato

There is still stuff I'm missing related to stats. I've monitored auto-stats update and even though ASYNC is enabled I see many more instances of sync (Load and Update stats)

You probably need to work on understanding that. If sync stats are happening somehow, that could be the missing link

Erik Darling

6:21 PM

This is all very interesting

Paul White

6:39 PM

Hopefully, young Z will come through with some more firm details to narrow the search

auto stats not running async when asked to would certainly be interesting

> Statistics on local temporary tables are always updated synchronously regardless of AUTO_UPDATE_STATISTICS_ASYNC option

Peter Vandivier

Jul 5 at 15:53, by Peter Vandivier

🌠tmyk

Paul White

7:11 PM

> Because the table "dbo.ohyeah" contains a filtered index or filtered statistics, changes to the table schema require a refresh of all table data.

From the Erik archives

@PeterVandivier I suppose it makes sense, because a background thread wouldn't be able to see the private temp table

Erik Darling

huh i wrote that

forgot about that one

back when joe sack was happy to help fix things

Erik Darling

7:32 PM

Hm it would be interesting if it were stats being created and not updated

Paul White

yeah, it's more of the general weirdness of filtered indexes/statistics

the 'refresh' being on the bound tree associated with the filtering expression

that's the indirect relevance

Erik Darling

right

Paul White

8:06 PM

How does anyone work like that

Erik Darling

they use sp_HumanEventsBlockViewer instead

Paul White

8:21 PM

as they should

I did see that suggestion b4 btw

Zikato

8:48 PM

At least it's not broken by a French thousand separator

Sean Gallardy

Does anyone here have to look at Kusto data constantly? I have some questions around how you use it to be efficient at repetitive tasks that might have slightly varying inputs.

Erik Darling

@Zikato that was quickie store, tyvm

Zikato

@SeanGallardy sure

Ask away or private chat or email

@PaulWhite I don't feel young anymore

Paul White

relatively young, then

Erik Darling

11:12 PM

Can anyone spare a cigarette

Sean Gallardy

@Zikato Thanks! I'm finding myself using similar (but not the exact same) queries to investigate issues. I don't want to manually do a bunch of stuff but I also don't want to make some crazy 500 join monstrosity with all the possible items and options and data from 10-25 different kusto tables.

I'm trying to figure out how other people are automating this without writing a front end to do it - I'm using kusto explorer right now.

For example, some queries may have anywhere from 3 to 15 inputs (I am currently using kusto explorers variables assignment and placeholders but open to other options) whereas others are based on the output of the previous query or a subset of those. In SQL I'd just make some stored procedures and whatnot, Kusto has Functions but I haven't touched those yet and unsure if I'd have the ability to create any in these clusters.

Transcript for

The Heap™ – Consultancy ©®