The Heap™ – Consultancy ©®

mustaccio

00:32

> The amount of beaurocracy we have to go through

I'm trying to imagine what beaurocracy looks like.

Must be beautiful

Paul White

01:44

I remember when this room used to be about databases

Paul White

02:12

But talking of lambdas bread twitter.com/jack2loneill/status/1645847228777635840

Erik Darling

03:32

@PaulWhite what is it about now?

Hannah Vernon

04:24

Speaking of Azure: securityaffairs.com/144682/hacking/…

Paul White

05:10

@ErikDarling seems mostly hardware, Oses, and client-side dev

and now bread products

John K. N.

Morning

Paul White

0

Q: Does sys.dm_exec_query_stats give information for "Compiled Plan Stub"?

I am investigating on the Sql Server behaviour with the “Optimize for Ad hoc Workloads” option set to “True”; in particular, I am trying to understand which information we have in cache after the first execution of an Ad Hoc query and consequently the Stub is put in cache. I noticed that as soon ...

That seems like a good question to me, well above average in fact. I wonder why someone would choose to downvote it. Perhaps the interactions between cache DMVs and parameterization are super-obvious.

Zikato

06:18

Morning

Wordle 662 4/6*

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

John K. N.

06:47

@ConcernedOfTunbridgeWells I tried a cherry-pick in my preferred tool once. No chance at all. Had to revert to Git Bash to get it done.

Git is a hog. And if you haven’t been using it since the beginning, then there is a steep learning curve IMO.

Wordle 662 4/6

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

Paul White

08:05

Wordle 662 3/6*

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

That's my grid from 661 which everyone should have finished by now. I was pleased with the Easter theme.

Assuming no one here is on Midway island or Honolulu, where it's still Tuesday evening

Zikato

08:30

My holiday in Honolulu is now ruined!

It's the qualm before the storm

Erik Darling

10:19

@PaulWhite if it’s so good why is it closed as a duplicate 🫣

Michael Green

@ConcernedOfTunbridgeWells I've been learning Rust of late. I like it.

Paul White

@ErikDarling Because you could only find the duplicate if you already knew the answer. It's a duplicate answer rather than an exactly duplicate question. Some thinking is also required to understand how the linked answer resolves the question. Had the author responded by saying they didn't understand, I would have explained in a full answer with a link to the 'dupe'. As it is, they knew enough to work it out for themselves.

Michael Green

It is very fond of nested generics, though.

Paul White

All problems can be solved with just one more layer of abstraction/indirection

2

Paul White

10:41

@ErikDarling On a first read, I even thought they might be saying they'd seen 'query stats' DMV rows were being removed because the parent cached plan did not contain a query plan (only a stub). That would have been an interesting (though unlikely) bug.

Erik Darling

11:02

@PaulWhite ah yeah, makes sense

Erik Darling

11:46

i am sad that this question got asked shortly before my blog post about it publishes.

0

Q: Trace database query

I need an advice. I would need to trace one particular procedure in SQL Server. It happens to me that one procedure ends up with a timeout and I need to find out which select, insert or update this happens. I want to ask, I don't know much about SQL profiler, so my question is, is it really the b...

sql-server-2014 ssms

Hannah Vernon

Wordle 662 5/6*

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

Should have been done in three, but lines 3 and 4 were stupid mistakes

Erik Darling

12:03

kinda has a campfire vibe

Paul White

0

Q: How to reclaim space on Sql Server Azure

I have a Sql Server database which currently has 270 gigs of space allocated to it. However, the actual space used is 27% (81 gigs). I would like to reclaim that space, as on Azure, they charge you by the amount of allocated space. The large amount of unused space is due to dropping a varbinary ...

sql-server azure

Slow I/O and log in the cloud? Is it possible?

Just scale it up eh

Erik Darling

when will people tire of paying for this

Paul White

idk

but look, your performance and life may be miserable but the cloud handles patching, backups, and HA/DR 4 u

all in one handy (but completely indecipherable) invoice

Erik Darling

i thought i was having a really insane vm problem but it turns out i am just a networking god

crisis averted

networking consultancy started

Paul White

what a lovely story

Erik Darling

12:12

i'm pleased that you're pleased

Paul White

v uplifting

The Azure team could probably use a talent like that

2

Erik Darling

i'm just gonna start my own cloud

who wants in

everyone gets their own laptop

Paul White

ha ha ha

Erik Darling

12:37

hrm.

Zikato

I thought that was a bit weird

Every article about the Halloween problem starts with the explanation how it got its name.

Erik Darling

yeah

well brent often laments that people don't read blog posts, or even blog titles

@Erik, I cancelled them, so they never finished. — Greg Gum 9 mins ago

Paul White

2 days ago, by Paul White

Reading comprehension being what it is these days

Erik Darling

seems that expectations are misaligned here

those dbcc commands are all-or-nothing, right? do they roll back work done or just abandon progress?

Zikato

12:54

Today I learned that "Chekhov's gun" is named after the Russian playwright and short-story writer Anton Chekhov

Erik Darling

nice "culture"

Zikato

Erik Darling

too fat and weak to get it

Zikato

Paul has trained you well

ConcernedOfTunbridgeWells

@Zikato I thought it was about the guy from Star Trek who kept leaving his phaser lying around.

Erik Darling

13:02

dammit jim i'm a doctor not a gun rack

Paul White

> DBCC SHRINKDATABASE operations can be stopped at any point in the process, and any completed work is kept.

> DBCC CLEANTABLE runs as one or more transactions. If a batch size isn't specified, the command processes the whole table in one transaction, and the table is exclusively locked during the operation. For some large tables, the length of the single transaction and the log space required may be too much. If a batch size is specified, the command runs in a series of transactions, each including the specified number of rows.

I feel like Josh

Erik Darling

i'm traumatized by poor documentation

pls forgive me

Paul White

Chekhov's gun

Chekhov's gun (Chekhov's rifle; Russian: Чеховское ружьё) is a narrative principle that states that every element in a story must be necessary, and irrelevant elements should be removed. For example, if a writer features a gun in a story, there must be a reason for it, such as it being fired sometime later in the plot. All elements must eventually come into play at some point in the story. Some authors, such as Hemingway, do not agree with this principle. The principle is recorded in letters by Anton Chekhov several times, with some variation; it was advice for young playwrights. == Criticism... ==

Erik Darling

thank you for the needfuls

Paul White

documentation as a service

Josh Darnell

13:08

@PaulWhite 🤣

Erik Darling

putting the p in gpt

Paul White

Just Open Source Heuristics - JOSH for short

3

Chekhov's gun: Jettison Or Shoot Him

Jokes Overly Simplistic Here

Josh Only Seems Happy

Hannah Vernon

That last one is self-referencing; a recursive josh, if you will

I hear those are the hardest joshs

2

Paul White

Jumps Over Some Hierarchies

Is there a problem with the outcome of SELECT c = CONVERT(decimal(9,6), 48.123456891123456); for you? — Erik Darling 36 mins ago

@Erik learns networking, forgets everything else

Erik Darling

13:28

it doesn't throw an error, i'm curious what they're unhappy with

you can sue me if you'd like

dbfiddle.uk/eKoQqzgI

Paul White

@ErikDarling Maybe this will assist your reading comprehension dbfiddle.uk/AHr0FlSM

And where to post answers

Erik Darling

asking a clarification question is not posting an answer in a comment!

sheesh

someone woke up on the wrong side of the jandal

Paul White

You sound like Jay Dee

Erik Darling

it ended in a question mark

it's a question

so i can provide a great answer©

Paul White

I see

Well ok then

Erik Darling

13:34

your invalid object name error demo didn't help, bub

was having a lovely morning reading old issues of smash hit

Paul White

Allow me to assist you further with a retro-edit

Erik Darling

now i'm all wound up from mod abuse

Paul White

But realistically, there are only so many ways to round a number

Erik Darling

from floor to ceiling

Paul White

Wait for the next question asking how to reclaim the space from the dropped variable-length column

The numeric conversion seems a funny thing for the question to focus on

You'd think it would be about performing the change online

Erik Darling

13:40

i wonder why a computed column isn't suitable, too

Lamak

Wordle 662 5/6

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

Erik Darling

flowers blooming

Paul White

@ErikDarling I suppose the issue with your comment answer is that it rounds up rather than truncating

Erik Darling

fine i deleted it

you win

perhaps they'll find some magickal way to get a subset of a string

and convert the result to a decimal(9,6)

SELECT
    s =
        CONVERT
        (
            decimal(9,6),
            SUBSTRING
            (
                '48.123456891123456',
                0,
                CHARINDEX('.', '48.123456891123456') + 7
            )
        );

thank mod the orange button appeared

Paul White

13:56

Yikes

SELECT
    CONVERT
    (
        decimal(9,6),
        ROUND
        (
            CONVERT
            (
                decimal(10, 7),
                S.latitude
            ),
            6, 1
        )
    )
FROM dbo.Stringy AS S;

Erik Darling

i'm sure op will benefit from all this

Paul White

The moustache already voted to close too localised

Coincidentally just after your comment answer

Erik Darling

if i posted that as an answer it would have been downvoted

on account of rounding v. truncating

Paul White

Rightly so

That can happen to wrong answers

Still aint a comment

(it arguably is but I'm pursuing it anyway)

Erik Darling

how would you have phrased that to make it a comment

Paul White

14:03

I would have written a gd answer

Erik Darling

that's because you got the p cores

assume you're just some lowly e cores

Paul White

Maybe ask for an expected result and compare with your query?

Erik Darling

isn't asking why the output of the query isn't suitable doing that?

Paul White

I misread the question at least twice so I'm not really going to go all-in on this

@ErikDarling That's why I said it reminded me of Jay Dee. He went through a bit of a phase (like many others) of commenting, "Would <answer> work for you?"

Erik Darling

hah

Paul White

14:06

Which is essentially the same thing as asking why <answer> wouldn't work

From a certain point of view

Erik Darling

from a different hemisphere, perhaps

topsy turvy world

Paul White

It's not a brilliantly-worded question, but it is a decent one, once understood

OP could have helped with expected results and mentioning why ALTER COLUMN wasn't an option

I wonder how many non-numeric values there are in that string column

how much space in bytes would DECIMAL(9,6) use compared with DECIMAL(8,6) - I have 60 billion rows so want to estimate the space saving between these 2 (and other types) — Quade Mar 14 at 14:58

Seems to be a long project

Erik Darling

14:21

six decimals long

Paul White

Friendo with the Azure space issue indeed has a very large LOB column

Good luck with that

J.D.

@PaulWhite eh, I still think if you're not actually putting the answer in the comments, and just asking if a certain type of answer would solve their problem, should be acceptable. But yes, I'm out of that phase and onto my next one, so I digress.

Paul White

@J.D. Yeah there are shades of gray

You're just more fun to pick on than most and, well, here

Sean Gallardy

Without comments, what would Paul do? He secretly enjoys them.

J.D.

@PaulWhite lol no doubt

Random thought. I find Postman's UI too robust and disorganized. There's at least 5 main menus by default.

Paul White

14:32

@SeanGallardy Are you testing me, Microsoft man

Sean Gallardy

@PaulWhite You're always a passing grade in my book

J.D.

15:07

I see questions like these and just can't comprehend how people enjoy living in that world. Viva la standard database solutions and on-prem.

Josh Darnell

Wow. Just...wow.

Paul White

Sounds like hell tbh

Josh Darnell

Imagine this scenario. You have a multi-tenant application, and all the tenants share a database. There's a dbo.Tenant table, and basically all other tables in the DB have a TenantId column with a FK back to that Tenant table. Question for the room: would you consider that relationship to be a hierarchy (Tenant is a "parent" to all of the other tables), or is Tenant simply an attribute of the other tables?

Erik Darling

sounds like an invoice relationship

Josh Darnell

15:24

I'm sure that design choice will be the source of many invoices once there are more and larger tenants, yes 😁

Hopefully invoices from my good friend Erik, once I get stumped.

Sean Gallardy

@PaulWhite I was told by reliable sources that this is indeed the future and the best way to go.

Paul White

@JoshDarnell That's one of those questions

Erik Darling

@JoshDarnell ❤️

Zikato

@JoshDarnell does the classification matter? I'd consider it to be attributed

Paul White

28

Q: Handling growing number of Tenants in Multi-tenant Database Architecture

Handling a modest number of customers (tenants) in a common server with separate databases for each tenant's instance of the application is relatively straightforward and is normally the correct way to do this. Currently I am looking at the architecture for an application where each tenant has t...

sql-server database-design

15

Q: Should a multi tenant system with SQL Server 2016, Shard or have Tenant isolation via separate database per tenant?

Given the use case: Tenant data should not cross talk, one tenant does not need another tenant's data. Each tenant could potentially have large historical data volume. SQL Server is hosted in AWS EC2 instance. Each tenant is geographically distant. There is an intention to use third party vis...

sql-server database-design sharding scalability multi-tenant

Sean Gallardy

15:35

That's an interesting story, if off-topic. Do you have a question or something? — mustaccio 3 mins ago

I almost spit out my tea

Kudos

Paul White

sigh

Erik Darling

Mustaccio rn

Josh Darnell

I wonder if that comment is appropriate in their "culture."

Paul White

2

A: 10000 tenants and Muti-Tenant Data Architecture performance?

Design a shared-everything system. This can be deployed in a shared-nothing way i.e. put each tenant in their own database, should that prove desirable. The extra development effort to type the additional predicate is very small and easy to do up front. The reverse - where you re-factor a shar...

@JoshDarnell It wasn't

It got flagged

Josh Darnell

Culture is difficult.

Paul White

15:42

Seems

Josh Darnell

Thanks for those multi-tenant arch links.

Paul White

10 mins ago, by Zikato

@JoshDarnell does the classification matter? I'd consider it to be attributed

I have the same question, I suppose

Josh Darnell

One of my coworkers keeps talking about the concept hierarchically, and it seems to affect the way he models other things in the system (in a negative way in my opinion).

I'm trying to remember the example that prompted me to ask the question...

Paul White

Does the local law allow you to shoot a coworker?

Thinking laterally

Josh Darnell

See, this kind of outside-the-box thinking is why I raised the question.

Needed to step back and look at the big picture.

Paul White

15:50

There's always more than one solution to a problem

Erik Darling

Does the Tenant table get joined to or does it just get used to route queries somewhere

Josh Darnell

It's not joined to, at least not often.

Paul White

Just somewhere to store the tenant's attributes like their name I expect

And to ensure tenant ids stored elsewhere are valid via the FK

Josh Darnell

Yep, exactly.

I forgot to ping @Zikato when I was replying back there.

Paul White

@Zikato does enjoy a ping

Josh Darnell

15:55

It was @Zikato's question initially anyway.

Paul White

I was concerned @Zikato's question might get lost in the flurry of transcript

Josh Darnell

I'm sure @Zikato will be grateful that you made sure I followed up.

Paul White

Yep, and if I know @Zikato, he'll be sure to thank you personally

Josh Darnell

I think the hierarchy view mostly results in weird application-side modeling. Like, you have a Tenant object, with a List<Employee> and a List<RetailLocation> etc. Everything has to pass through the Tenant object.

But because we're using EF, bad application modeling usually reflects back into janky database tables.

You end up needing to load some huge object graph just to get one thing.

Paul White

Is that a limitation of the tool though? I would imagine a code thread/session would be dealing with a single tenant for its lifetime. It wouldn't change once determined at the start?

I may not have expressed that very well

Zikato

16:04

@PaulWhite How about a local culture?

Paul White

Perhaps the ins-and-outs are too much to describe to someone who doesn't work with EF

Sean Gallardy

What would your mom say to that, though?

Zikato

My mom would say: "Co wolno wojewodzie, to nie tobie, smrodzie"

Paul White

How rude

> What the voivode is allowed to do, it's not you, the stench

(inline translate)

Zikato

poor effort by the inline translate this time

Paul White

16:08

I kinda guessed

Zikato

wsjp.pl/haslo/podglad/1726/….

Lamak

"What is allowed to the governor, is not allowed to you, stinker" - deepl

Paul White

> What the voivode is allowed to do, it is not you stink
What is allowed voivode
Definition
Some people, especially the elderly, the more important, and higher-up, have special privileges and can afford behavior that others consider reprehensible

Zikato

chatgpt is even better: A possible translation of the Polish proverb "Co wolno wojewodzie, to nie tobie, smrodzie" to English would be: "What is allowed for the noble is not allowed for you, peasant."

Paul White

That's gooder

> Rank has its privileges

mustaccio

16:12

Co wolno Jovi, nie wolno bovi

Sean Gallardy

@Zikato yeah google translate from polish wasn't v.good

Paul White

@SeanGallardy Polish?

Sean Gallardy

That's what it "auto detected"

Paul White

Ah

Sean Gallardy

and gave the same translation as inline

Zikato

16:14

yup, my mom is Polish

Paul White

well that makes sense then

I did wonder why you said a Polish proverb

Lamak

@Zikato chatgpt seems to give me the deepl translation, not this one

Paul White

sounds like it could use some polish

RHIP

== English == === Phrase === RHIP (military, Internet) Initialism of "rank has its privilege" or "rank hath its privileges". Initialism of rest here in peace. ==== Related terms ==== RIP === Anagrams === IRHP

Zikato

I'll czech it out

Sean Gallardy

Gonna have to put my chest waders on soon for this room

Zikato

16:16

@PaulWhite one letter short of showing up in Wordle. I'm still mad at "snafu"

Paul White

Yeah SNAFU was a bit mean

Sean Gallardy

I thought that was an acronym

Paul White

I nearly said at the time, the situation was normal...but no clues

@SeanGallardy It was/is but people use it as a word now

source: Wordle

Sean Gallardy

🤦‍♂️

NYT never gets anything wrong, apparently

Paul White

couple of snafus there yeah

let's see

snafu

Noun: snafu (plural snafus)

Alternative letter-case form of SNAFU

Verb: snafu (third-person singular simple present snafus, present participle snafuing, simple past and past participle snafued)

(military, slang) To screw up or foul up.

yep

Lamak

16:18

didn't know it was an acronym actually

assumed it was something like "waifu"

Paul White

situation normal all fucked/fouled up

Sean Gallardy

that's what I know it as

Paul White

depending on your sensitivities

Lamak

@PaulWhite yeah, had to google it

Paul White

I don't think I've heard it used as a verb

Josh Darnell

16:21

I wonder when we'll see FUBAR in Wordle.

Maybe it's already been done.

Sean Gallardy

Don't give them ideas

Josh Darnell

I associate those two words in my head (SNAFU / FUBAR).

Erik Darling

I look forward to diacritics

Paul White

I imagine you have more than two critics

ho ho

Sean Gallardy

Paul White, SQL Clause

Lamak

16:22

SNAFUBAR

5

Josh Darnell

I kind of agree though, that it's become a normal word with inherent meaning rather than an acronym. I've met multiple people that know the word but aren't familiar with the acronym origin.

Sean Gallardy

@Lamak Sounds like a "health" snack

Josh Darnell

@Lamak Amazing

Zikato

@JoshDarnell weird. That's like not knowing what Halloween problem was named after

Josh Darnell

16:43

@Zikato Nice callback.

Erik Darling

Our Chilean friend is the better at English than the rest of the room

Josh Darnell

16:57

@PaulWhite You're right that, for a given thread / session, the tenant won't change. In this case we're talking about web apps, and a thread is associated with a single HTTP request.

We might be pulling a list of employees that work at a particular store to display in a grid, via a HTTP GET request to a route like /api/{ORG_ID}/{STORE_ID}/employees.

In the code for that route, I don't really want to get the org, then get the store, then get the employees. I just want to say something like return _context.StoreEmployees.Where(e => e.Store.Id == STORE_ID && e.Organization.Id == ORG_ID);

J.D.

@SeanGallardy Break me off a piece of that...SnaFuBar

Sean Gallardy

17:52

You'd die of gluttony if I did that

J.D.

18:26

Better than dysentery.

Sean Gallardy

> You have died of dysentery.

Hannah Vernon

18:51

> Table spool may only be able to handle 2,147,483,648 input rows before exhausting the available values for uniqueifier.

lol

that's a big spool

from here for those interested

Erik Darling

19:31

sounds like a tempdb problem

Josh Darnell

19:42

@HannahVernon 4 u

mustaccio

20:55

-1

Q: PSQL select matview

WITH web_users AS ( SELECT tracks.anonymous_id, tracks.user_id, lower(COALESCE(tracks.context_campaign_source, tracks.context_campaign_m_source, tracks.context_campaign_tm_source, tracks.context_campaign_ntutm_source, tracks.context_campaign_utm_source)) AS source, lower(COALESCE(tracks.context_c...

psql

Not sure what would be a welcoming thing to do here, except VTC

Erik Darling

well i formatted the code

mustaccio

Now you're obligated to help the OP.

He didn't ask you to format the code, he needs help.

Although, perhaps, formatting was exactly what he needed help with... hard to tell

Josh Darnell

I hate that query so much it hurts.

mustaccio

21:12

I added some helpful tags and clarified the title.

I hope now it can remain open.

Erik Darling

does postgres have as onerous restrictions on creating materialized views as sql server?

Additional requirements

mustaccio

I'm pretty sure they are quite onerous, but differently onerous.

Erik Darling

Well the important thing is to keep practical skills from being transferable

mustaccio

22:26

Practical skill being RTFM, I assume

That's almost FUBAR, as some might say

Josh Darnell

22:41

I can't help but read DBCC CLENATABLE in the voice of Mario.

Wordle 662 3/6*

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

Paul White

23:31

@ErikDarling no but pg mat views don't stay in sync with the data. They're snapshots

@JoshDarnell I see. Thanks for the example. I can't help but think back to days when we'd implement that as a stored procedure with parameters for the organisation and store keys

Which I know is an obvious point and doesn't help with the EF and design issue you're talking about.

Erik Darling

23:59

@PaulWhite sounds familiar

@JoshDarnell did you also have to go see that movie with your family?

Transcript for

The Heap™ – Consultancy ©®