The Heap™ – Consultancy ©®

J.D.

12:31 AM

@ErikReasonableRatesDarling rhymes with Beavis...

Are you talking upstate NY or somewhere else?

John K. N.

9:06 AM

Wordle 1,187 3/6*

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

frog with eye patch

Charlieface

11:25 AM

Given that sys.syslanguages is listed under System Compatibility Views, implying it shouldn't be used outside of backwards compatibility, what is the modern alternative? There doesn't appear to be a sys.languages

Zikato

11:36 AM

I'm pretty sure it's using the .NET localization library

see the transcript for the French separator issue Erik and Paul were dealing with. The difference was in Windows 10 and 11

Hannah Vernon

12:01 PM

Wordle 1,187 5/6*

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

J.D.

@JohnK.N. Pirate Frog 🏴‍☠️

Erik Reasonable Rates Darling

12:21 PM

Nevis

Nevis ( NEE-viss) is an island in the Caribbean Sea that forms part of the inner arc of the Leeward Islands chain of the West Indies. Nevis and the neighbouring island of Saint Kitts constitute the Federation of Saint Kitts and Nevis, a singular nation state. Nevis is located near the northern end of the Lesser Antilles archipelago about 350 kilometres (220 mi) east-southeast of Puerto Rico and 80 kilometres (50 mi) west of Antigua. Its area is 93 square kilometres (36 sq mi) and the capital is Charlestown. Saint Kitts and Nevis are separated by The Narrows, a shallow 3-kilometre (2 mi) channel...

Zikato

12:44 PM

Does the R.R in J.R.R. Tolkien also stand for "reasonable rates"? There is no way for me to czech

Josh Darnell

1:05 PM

What are "learnings"? Do you perhaps mean findings or discoveries or lessons? — tchrist Sep 13 at 23:49

That made me chuckle.

Erik Reasonable Rates Darling

> which was also true before the recent signup initiatives.

does that mean creating accounts for everyone automatically

Zikato

This made me chuckle. An organizer misspelt the name of the city on a merch and instead of fixing it they edited wiki to reflect that typo.

https://www.reddit.com/r/DotA2/comments/1fjol12/ti_organizer_pgl_misspelled_city_name_on_their/

Erik Reasonable Rates Darling

i think it looks cooler with two ps anyway

Zikato

Cop pen hagen

Erik Reasonable Rates Darling

1:22 PM

COPPEN🇩🇰HAGEN

Paul White

2:04 PM

Wordle 1,187 3/6*

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

Sean Gallardy

> You've been blocked by network security

Today is starting off well

Also had a pipe burst last night, the fun never ends!

Erik Reasonable Rates Darling

r u 🔥d?

Paul White

Could be worse, you could be having chat markdown problems

Sean Gallardy

@ErikReasonableRatesDarling Is it wrong that I kinda hope so?

Erik Reasonable Rates Darling

@SeanGallardy i hope he was cute.

Zikato

2:06 PM

@SeanGallardy what kind of pipe

Sean Gallardy

You know me, I have high standards

@Zikato 3 inch water pipe

Paul White

They all say that

Zikato

Thematical. We have floods over here

Sean Gallardy

Gotta stay on topic and all

Zikato

I'm not sure why are we singled out

Sean Gallardy

2:10 PM

Maybe it's a sign

Zikato

no the signs are underwater

Paul White

Perhaps someone upstairs heard you like a drink

Erik Reasonable Rates Darling

god i hope russia stays out of hungary so i don't have to see a bunch of dimwits on twitter start talking about magyarorszag

Zikato

it stopped yesterday but it was 5 days in a row. Not quite biblical proportions, but close

Paul White

@ErikReasonableRatesDarling magyarorszag?

Sean Gallardy

2:11 PM

You'd never make it in Florida, it rains every day

Paul White

@Zikato Quite the story ark you have going there

ark!

Zikato

@PaulWhite u would Noe it

Sean Gallardy

would have been better if you put noah, but I assume local factors are at play

Paul White

23 hours ago, by Sean Gallardy

@Zikato Ano

Almost noA

Anyway, it's all fantastic news for the Czech hydro lakes

Erik Reasonable Rates Darling

@PaulWhite it's right below the cloud on the map

Paul White

2:14 PM

Oh, I didn't expand the map

Sean Gallardy

According to that post linked by josh, comments are down. I wonder if Paul's dashboard shows green now...

Paul White

For a moment there, I thought you meant comments were completely broken

I did a little dance

Sean Gallardy

Salsa?

Also, if anyone is having issues with the whole VSS backups thing, there should be a fix available for download soon.

Paul White

@SeanGallardy Who knows what it might have turned into if it had lasted

Sean Gallardy

🕺🪩

Paul White

2:21 PM

And do you really think anyone in this room deals with anything as mundane as backups

Much less VSS ones

Erik Reasonable Rates Darling

lol

Paul White

It's been a while since I used Visual Source Safe though

Sean Gallardy

Too many people rely on 3rd party backups these days, so yeah lots of VSS... whether or not people know it, good point they probably don't.

Paul White

I hear snapshots are instant and free

It's like magic really

Erik Reasonable Rates Darling

trillions of terabytes in 3ns

no data movement

Paul White

2:24 PM

🦄

Sean Gallardy

Just gotta spend 5 billion on storage

Also, the licenses are by GB

Erik Reasonable Rates Darling

does anyone need less than 1tb these days

Sean Gallardy

I also want to state, for the record, GIT is horrible for any medium to large sized project. I don't see why people use it for that stuff.

@ErikReasonableRatesDarling 640k

Erik Reasonable Rates Darling

i think a blank excel file is bigger than that

Sean Gallardy

0k in size, it lives in the cloud!

Paul White

2:27 PM

git is one of the modern things that everyone has to think is the bees knees regardless of all the evidence

didn't MS pay like $100B for it

Sean Gallardy

PRobably

Erik Reasonable Rates Darling

huh was oracle the only vendor to not implement mvcc like an idiot

Sean Gallardy

They like to overpay for crappy stuff

Nokia, Yammer, Dragon Naturally Speaking, GIT, etc.

Erik Reasonable Rates Darling

why postgres chose in-table versions and microsoft chose tempdb is pretty stupefying

pvs is basically undo segments

Paul White

Patent avoidance perhaps

Erik Reasonable Rates Darling

2:29 PM

🪣

Zikato

The one time I don't Czech the English spelling
https://cs.wikipedia.org/wiki/Noe

Sean Gallardy

@ErikReasonableRatesDarling ???

Erik Reasonable Rates Darling

what is your question

or do u have 3 questions

Paul White

@Zikato Oh noes

Erik Reasonable Rates Darling

that will be typed eventually

Sean Gallardy

2:29 PM

What do you mean they are basically undo segments?

Paul White

Hang on I need some snax

Erik Reasonable Rates Darling

the pvs used by adr in the local database is closer to undo segment behavior (local to the database)

Paul White

PVS is just a user database version store tho

I don't know enough about Oracle undo segments to say if they're a version or log store

Sean Gallardy

What Paul said about PVS

Paul White

ADR is a bit more advanced though due to the passage of time and the invention of in-row diffs

Erik Reasonable Rates Darling

2:32 PM

that's what i said about pvs

Sean Gallardy

when I think undo, I don't think version store

Paul White

This is all fascinating, but if you're going to rag on PVS as just being an Oracle rollback segment, you'll have to say the same about the tempdb version store

Which may be a fair comment, idk

Erik Reasonable Rates Darling

i'm not ragging

Paul White

It's early yet

Erik Reasonable Rates Darling

i like pvs being local better

i wish row versions had always been that way

Paul White

2:34 PM

Well yes, you get more of them for a start

Erik Reasonable Rates Darling

so people wouldn't hyperventilate about their poor downtrodden tempdb when discussing rcsi/si

Paul White

But it was heaps of work. A single tempdb version store wasn't so bad at the time

Erik Reasonable Rates Darling

you wouldn't know that from reading blog posts about isolation levels

Paul White

Besides, tempdb is transient. It doesn't get to have hurt feelings for long

@ErikReasonableRatesDarling Right, but most of that is just parroting what Ozar said once offhand

Erik Reasonable Rates Darling

yep

Paul White

2:37 PM

I rather suspect anyone having a crisis in tempdb due to RCSI/SI was rather close to the edge beforehand

Erik Reasonable Rates Darling

from a sample size of one consulting engagement in 2013

Paul White

yep yep

People asked for a tempdb per user database for years and years.
There really should have been more excitement about PVS and ADR from that POV.

Erik Reasonable Rates Darling

i tried to gin some up

Paul White

I liked ADR a lot when I first encountered it and I haven't changed my mind

Erik Reasonable Rates Darling

think about the cheers that would have arisen if it had fixed spools too

Paul White

2:39 PM

One could say recovery should always have worked that way, like RCSI should have been the default

Erik Reasonable Rates Darling

in memedb...

Paul White

It is encouraging to see some things still improve over time

Despite most of the rest of it turning to custard on a daily basis

Anyway, the Oracle stuff was cool. Time travel queries were neat

None of that bizarre temporal table business

"What would this query have returned 3pm last Tuesday"

Erik Reasonable Rates Darling

i think the cutest client cross section at the moment are people who want to migrate to postgres and are also afraid to use rcsi in sql server

o r u in 4 it

Paul White

SELECT ... FOR SHARE

Erik Reasonable Rates Darling

SELECT ... FOR SHAME

Paul White

2:43 PM

I wonder if they h8 heaps 2

Because guess wot

Erik Reasonable Rates Darling

teehee

Paul White

I suppose if you're the sort of person who enjoys fretting about fragmentation, you'll love vacuuming

Tangentially related, I was quite surprised they changed the rules for RCSI update locking with Optimized Locking

Well, Lock after Qualification really

Everyone's so optimistic these days

Anyway

Sean Gallardy

@ErikReasonableRatesDarling Now instead of tempdb being out of space, people complain their user database is too big

Erik Reasonable Rates Darling

@PaulWhite is that documented anywhere or did you find it messing with the feature?

@SeanGallardy temporarily

Paul White

@ErikReasonableRatesDarling It's well documented on the Optimized Locking page

Sean Gallardy

2:48 PM

OL should be in vNext IIRC

Erik Reasonable Rates Darling

how can it not be

it's the only decent thing they've come up with since 2019

Paul White

Assuming they ironed out the bugz

Sean Gallardy

Azure workloads being what they are

low throughput

Paul White

It's also spookily similar to how Postgres does locking, in a way

Sean Gallardy

git pull
<wait 15 minutes>
found 1048238 objects
<wait 20 minutes>
resolving deltas ...
<wait 30 minutes>
complete!

Erik Reasonable Rates Darling

2:50 PM

@PaulWhite ty

Paul White

Aside from the good reasons to use OL in and of itself, it will encourage more people to turn ADR on

Sean Gallardy

Hopefully the people that turn on ADR aren't doing many rollbacks and aren't doing many non-adr operations

If the cloud has enough throughput to break it....

Paul White

Well, rollbacks have always been quite rare...until that one time

Not to mention startup recovery

Erik Reasonable Rates Darling

> In Azure SQL Database, idle transactions (transactions that haven't written to the transaction log for six hours) are automatically terminated, to free up resources.

Paul White

Intern wrote the usual KILL script

As you did fairly recently, I believe

Sean Gallardy

2:53 PM

@PaulWhite If you only knew... you'd be even more disappointed.

Paul White

Just inject a failure into the task, easy

Failure comes easy for some

Sean Gallardy

Seems like 10% of my job is just killing the sql server executable

Paul White

Hannah spends 50%

Sean Gallardy

Gov't and all

something about efficiency

Paul White

The good times will roll when the questions start coming in about weird blocking and deadlocking on TXN locks

Erik Reasonable Rates Darling

2:57 PM

i'm gonna have to do a lot of work on my blocking and deadlocking analysis scripts huh

Paul White

Looks like it

In about a decade, when usage becomes widespread

It's amazing that a company that produces Optimized Locking also came up with Ledger

Every point on the curve, I guess

Erik Reasonable Rates Darling

3:12 PM

ledger adoption must really be popping off

it's all you ever hear about

ledger and linux

woo

J.D.

Ah, that Nevis. It's interesting, there's so many random Nevi in North America. I've never heard of it before though. Thanks!

Erik Reasonable Rates Darling

there's only one worth going to

J.D.

Haha yea, the upstate one threw me off. I'll check it out. We like beach vibes. Anything in particular you favor there, food or attraction, or otherwise?

Erik Reasonable Rates Darling

being where no one can find me

Sean Gallardy

3:52 PM

Cabin in the Great Smokeys is good for that, off grid of course. I believe that national park has the most mysterious disappearances - aka people going missing in ordinary places with no signs and are never found.

I would say that Appalachian trail has more, but I'm pretty sure those people end up being found, years later, not alive.

Erik Reasonable Rates Darling

i should have said "no one except room service"

Sean Gallardy

:/

Catskills are near you, that's a real my side of the mountain opportunity

Erik Reasonable Rates Darling

you know how i feel about the outdoors

Sean Gallardy

4:09 PM

Fresh air is bad for your health, I believe it is known to the state of California to give you cancer.

Peter Vandivier

that's why Eric smokes so many cigarettes

known health influencer that he is

Erik Reasonable Rates Darling

link in bio

Sean Gallardy

5:16 PM

Build succeeded.
    0 Warning(s)
    0 Error(s)

Finally, some good news today

Paul White

Ship it

Hannah Vernon

5:32 PM

What is the consensus on adding a dummy table with a columnstore index to coerce batch mode? Is it au-fait to do that, or will my future self hate me for doing it?

@PaulWhite I mean I do try

Paul White

Serious people use a temporary table

Hannah Vernon

something like this:

    DROP TABLE IF EXISTS [#dummy];

    CREATE TABLE [#dummy] ([x] int NULL);

    CREATE COLUMNSTORE INDEX cs ON [#dummy]([x]);

Paul White

That or an empty nonclustered columnstore index are both valid

Hannah Vernon

excuse the double-spacing

Paul White

You can create the CS index inline, but yes

Has to be table-level though

Hannah Vernon

5:36 PM

SQL Server complained when I tried to do it inline, but that was probably a mistake I made somewhere.

In the FROM clause, I added:

Yano_of_Queenscastle

@PaulWhite Would using regular table be considered funny?

Hannah Vernon

INNER JOIN [#dummy] [d] ON 1 = 1

Paul White

CREATE TABLE [#dummy]
(
    [x] int NULL,
    INDEX cx CLUSTERED COLUMNSTORE
);

You also don't provide a column list with that type of index

Hannah Vernon

ahhh that was probably my mistake

Paul White

The comma is important there

@HannahVernon No, you tried to create a column index. It has to be table level

Hannah Vernon

5:38 PM

oic

Paul White

That statement will fail without the comma

Hannah Vernon

the INNER JOIN I have is not a Bad Thing, though, right?

I mean obviously aside from the delimiters

:-)

Paul White

Most people use an outer join on false

Otherwise, the dummy table appears in the plan

Hannah Vernon

yeah I noticed it is in the plan. I kind of like it being in the plan for visibility.

Paul White

That's...unusual

Hannah Vernon

5:41 PM

I suppose performance optimization would say you want it not in the plan.

so you're saying LEFT JOIN [#dummy] [d] ON 1 = 0 would be more typically what you'd see.

Paul White

Much better. The join you wrote is a cross join, which can only be implemented by nested loops

That's not very batch mode friendly

Hannah Vernon

ahhh, I'm so glad I asked - thank you!

Paul White

Also, unless you add exactly one row to the dummy table your results will be wrong

No rows, or some multiple of the rows from prior tables

Hannah Vernon

Do you mean if I do the INNER JOIN it would be wrong? Or I should always add a single row to the dummy table?

Paul White

Are you asking how a cross join works

Hannah Vernon

5:45 PM

ouch no

Paul White

Use the left join 🙂

Hannah Vernon

it's curious though because there is no row in the dummy table and the results with or without the dummy table are the same.

@PaulWhite will do

Paul White

@HannahVernon Were there zero rows before

Because joining to an empty table is rarely productive

Hannah Vernon

I see the error of my ways now

Paul White

Commas last then, good

Hannah Vernon

5:47 PM

I'm not going to go that far.

Paul White

Not yet, perhaps

Hannah Vernon

I love that you left room for growth

Paul White

Erik's optimism classes continue to pay dividends

Hannah Vernon

You speak the truth, as always.

Erik Reasonable Rates Darling

understand your explain. — mike 2 hours ago

Hannah Vernon

5:52 PM

oh, I also added another temporary table to decrease the complexity of the original query, which combined with batch mode has dropped execution time down from ~2.5 seconds to ~350 ms

Paul White

I can get zero rows even faster

Hannah Vernon

so I think that's a win. Especially considering SolarWinds Database Performance Advisor will shut the hell up now.

Paul White

@ErikReasonableRatesDarling Why are comments allowed again?

Hannah Vernon

@PaulWhite it returns 3 rows precisely the same from each version of the proc, I'll have you know.

Paul White

I am sorry I ever doubted u

Hannah Vernon

5:53 PM

I mean, I can hardly blame you

I do use Service Broker for example

Paul White

2.5 seconds powered by a watch battery
350ms consuming the entire generation capacity of a medium-sized country

Hannah Vernon

hahah maybe Azure has a heart attack when I run the new version

Paul White

Egads, you're in the cloud

Hannah Vernon

to be fair, this schema is a terrible terrible thing

Paul White

How many free vCore seconds do you have remaining

Hannah Vernon

5:55 PM

@PaulWhite yeah, we just moved this year. Having said that, we're running SQL Server in a VM with Top Tier disks lol.

Paul White

Oh, that's good

Erik Reasonable Rates Darling

Needing batch mode for a 2.5 second query is interesting

Paul White

Proper SQL Server just hosted off-site. Perfectly normal

Hannah Vernon

right

Paul White

@ErikReasonableRatesDarling Time is money

What if they'd cured cancer in those extra 2.15s

But someone please spare a thought for tempdb in this scenario

Josh Darnell

5:57 PM

@PaulWhite I wrote a query with a CTE yesterday that I had to cancel after 5 minutes. I rewrote it using a temp table and it finished in 1 second.

Paul White

More pressure on tempdb

Josh Darnell

Yeah sorry.

Paul White

I wrote some JavaScript code today. Am I web dev

Hannah Vernon

you are if it returned NaN the first time you ran it

if it worked perfectly without any errors the first time you ran it, you're deserving of the god moniker

Paul White

Well, I broke all the syntax highlighting on my website, does that count

Hannah Vernon

6:00 PM

yes, I would say.

Paul White

NaN it is then

Josh Darnell

@PaulWhite Congrats!

Paul White

@JoshDarnell Thanks, man

Hannah Vernon

@ErikReasonableRatesDarling when I first wrote the queries in this stored proc, I didn't care about performance optimization all that much because the results are being consumed by a robot. However, SolarWinds doesn't care, and is ringing the bell about it being the highest consumer of waits, so in the ever-present attempt at bettering everything, we have to fix stuff that is "slow".

Paul White

4

@JoshDarnell Have a copy of the new edition on me

Hannah Vernon

6:03 PM

"Darneli" I love it.

Erik Reasonable Rates Darling

@JoshDarnell don’t tell the data engineers of LinkedIn about this

@HannahVernon SolarWinds is really stupid about that stuff. I don’t know why anyone bothers with it.

Hannah Vernon

that is something I completely concur with.

also, it pages me when a step fails in a job, even if the job is designed to succeed when that happens.

Paul White

Be careful with pagers

Hannah Vernon

and it thinks our databases are nearly out of space even though they have literally gigabytes of space between the used space and the maximum database size.

@PaulWhite yeah, that was a little bit of a surprise I imagine

Paul White

I hear everything's blowing up now

Toasters and whatnot

Erik Reasonable Rates Darling

6:08 PM

It’s almost like it’s a bad monitoring tool that should be disregarded

SQL Sentry would never do this to you

Paul White

Aren't the alternatives all worse?

Hannah Vernon

I tried very hard to not have it inspect the SQL Servers I manage, but TPTB had other designs.

Erik Reasonable Rates Darling

I do believe we’re talking about DPA

Hannah Vernon

^^^ yes

Erik Reasonable Rates Darling

Utter trash

You’re better off not knowing

Paul White

6:09 PM

DPA?

Ok, nvm

Hannah Vernon

a superb feature is how it rewrites all the SQL to an even stupider standard than I do

Erik Reasonable Rates Darling

😒

Josh Darnell

@ErikReasonableRatesDarling I'll leave that to the Team Leads.

@PaulWhite OMG 🤣

Paul White

😁

Hannah Vernon

this is particularly helpful from DPA

> DPA supports traditional availability groups (in a single WSFC). However, this instance participates in a distributed availability group, so DPA's Availability Group feature is not available. All other DPA monitoring features will function as usual.

lol

yeah, like crap.

Josh Darnell

6:18 PM

@PaulWhite The bass guitar is a nice touch.

Erik Reasonable Rates Darling

@JoshDarnell have you visited the official site yet

Josh Darnell

@ErikReasonableRatesDarling Haha yes, I looked at it when you posted the other day.

Clearly I didn't look close enough.

Did you make the site?

Hannah Vernon

Made by AI, under Erik's tender guidance no doubt

Yano_of_Queenscastle

I was tasked investigating, whether we could use Dynamic Data Masking to mask data on our dev instance, so that our devs can develop on real data, but not see anything sensitive. It turned out the DDM is not a very good solution for this.
However, I can't came up with scenario, for which it IS good for, since the masking is easily bypassed, and doesn't return anything meaningful either.
Have you ever seen it used productively?

Paul White

@JoshDarnell Best bit

Erik Reasonable Rates Darling

6:48 PM

@JoshDarnell yes, can't you tell by how terrible the design is?

Aaron Bertrand

6:59 PM

hai

@Yano_of_Queenscastle I came to a similar conclusion: mssqltips.com/sqlservertip/4002/…

@PaulWhite Do you have a handy list of optimizations that are skipped in tempdb? I know you've alluded to it a few times but, other than fk join elimination, I haven't found any other concrete examples. (Context: I have been working on a post about why to not do any real testing in tempdb.)

@ErikReasonableRatesDarling Neither. Just had some unexpected waitfor this week.

Yano_of_Queenscastle

I think I have read that blog during my investigation :D (Thank you for it - it's one of the few I've read that actually addressed this.)

It's just that it seem like incredibly limited scenario - If you can control the user's access in application, then... you might as well control their access in that very application, no?

Aaron Bertrand

Well, I think part of the intention is to not force people to repeat that control in 14 different applications, along with SSMS, ADS, etc.

Similar to "don't use stored procedures, just put the query in the app, then it's easier to update the query in the app" unless you run the same query from 14 different apps and sometimes want to run it in SSMS - then a stored procedure makes more sense.

One of the comments on the post suggested that some of the holes have been fixed in or before SQL Server 2017. I only saw that comment today, so I haven't gone back to investigate. I've basically ignored the feature since I wrote the articles.

Zikato

7:18 PM

@SeanGallardy I thought MS had scaling monorepos infoworld.com/article/2337202/…

Erik Reasonable Rates Darling

7:30 PM

@AaronBertrand oh did you retire unexpectedly

Hannah Vernon

@ErikReasonableRatesDarling this made me rethink things a bit. Thanks for that. It turns out I tried batch mode at first, then added a temporary table that was pretty obviously a good optimization without confirming that batch mode was actually useful for this. Turns out it wasn't needed at all. Results down from 2600 ms to 30 ms is more than good enough lol.

Aaron Bertrand

@ErikReasonableRatesDarling lol, I wish

Sean Gallardy

10:02 PM

@Zikato We do, you'd assume that everyone uses it though and you'd have to convert your repo for it IIRC. Also there's this, which is typical MS...

> Note: for new deployments, we strongly recommend you consider Scalar instead of VFS for Git.

Erik Reasonable Rates Darling

10:30 PM

I’m happy to report I don’t know what that means

Sean Gallardy

11:04 PM

Neither does anyone else, you're in questionable company

Transcript for

The Heap™ – Consultancy ©®