The Heap™ – Consultancy ©®

Zikato

06:22

Morning

John K. N.

07:29

morning

Charlieface

08:46

Morning.

Please somebody back me up on this one. SQL Server does not have "row IDs" as far as I am aware, it just uses a uniquifier on non-unique indexes to identify the row. Therefore a uniquifier is necessary on a non-unique index.

1

A: Why do root and intermediate levels of NON-unique NON-clustered indexes additionally store row ID?

The storage engine needs a way of uniquely identifying a row. It does that through the index key columns, both for clustered and non-clustered indexes. (Non-clustered indexes simply store the clustered index's key as an INCLUDE). If the index key is non-unique, and there are duplicates on the pag...

John K. N.

But what happens if you have a non-unique non-clustered index on a heap? Regarding your statement that ...Non-clustered indexes simply store the clustered index's key as an INCLUDE...

Charlieface

Heaps don't exist any decent database. I didn't even think anyone would do something as silly as put an NCI on a heap. Seriously, yes in that case there is an RID used instead of the clustering key, so that's probably what they are referring to.

John K. N.

09:01

:-)

ypercubeᵀᴹ

09:19

@Charlieface I may be totally out here but are uniquifier and row-id mentioned in that q the same thing?

I thought they were different things.

Charlieface

@ypercubeᵀᴹ They are different things. OP seems to be confusing something somewhere.

The words being quoted from the book "In addition, for non-unique indexes, it also stores the row-id of such a row." seem to refer to the uniquifier, not the rowID

ypercubeᵀᴹ

is it? To me it seems to refer to the row-id of the index leaf page

Paul White

Heap RIDs are unique by definition. A clustered index may need a uniquifier. Nonclustered indexes on a clustered table do not, because they include the clustering key (which must be unique, if necessary incorporating a uniquifier).

The distinction Dmitry is making is non-unique nonclustered indexes incorporate the (complete) clustering key at all levels of the nonclustered index. A unique nonclustered index has the complete clustering key only at the leaf level.

The terminology isn't that important. Some people call it a bookmark, RID, or row locator. It's the same thing, something that uniquely identifies a row in the base table.

SQL Server 2005 added an optimization where uniqueifiers were reused when rebuilding a nonunique clustered index so any nonclustered indexes did not need to be rebuilt (as was the case in 2000).

Paul White

10:19

> Non-clustered indexes simply store the clustered index's key as an INCLUDE

@Charlieface That part of your answer is not correct. Nonunique nonclustered indexes have the complete clustering key added to the key at all levels. Includes only appear at the leaf.

Charlieface

10:37

@PaulWhite Basically what it boils down to is that an index must be made of a unique set of key columns, and the uniquifier is added if it's needed for that? I think that's what I was saying?

@PaulWhite Feel free to edit that bit as necessary.

Paul White

@Charlieface I already answered

@Charlieface Greg Linwood used to argue that heaps and nonclustered indexes were optimal for some workloads. His blog isn't live anymore, AFAIK, but if you're interested in his reasoning, you can find it archived at web.archive.org/web/20111030115012/http://…

ypercubeᵀᴹ

11:06

Yeah, I think this part "although why you would have an NCI on a heap is rather questionable" is open for debate, and not relevant to the question asked.

Paul White

It's funny, Azure SQL Database originally came without heaps. They were added after a general outcry.

My favourite quirk of nonunique nonclustered indexes is the clustered index keys become seekable in the nonclustered index.

Erik Darling

13:15

@Charlieface i find it a rewarding thing to do, and it's useful to learn the particulars about a thing. i wouldn't suggest trying to push out five a week unless you're trying to appease various SEO deities though.

@PaulWhite and sortable

Paul White

indeed, all the usual index key goodness

Erik Darling

there's a great (and accurate) post© about it

Paul White

is there

where

Zikato

can it be found with a search engine?

Paul White

Rishi Sunak is the new UK PM

Erik Darling

13:30

@PaulWhite brentozar.com/archive/2018/05/where-clustered-index-keys-dare

Paul White

> Irregardless

Erik Darling

happens sometimes

Paul White

@ErikDarling That is a good post, but it doesn't address the sorting angle

Erik Darling

no, i wasn't as hip to it back then

i do cover it in another post

Charlieface

@ErikDarling Now try the unique table's NCI with ID (the clustering key) as a second key column. You get a seek predicate as well.....

And there I was always thinking that it made no difference.

Oh just realized BrOz mentions that at the end.

Erik Darling

13:41

ahem

Charlieface

Oops, apparently I didn't read the top of the article either. Sorry Erik

Erik Darling

happens all the time

my mom calls me brent

Charlieface

Erik Ozar

Sean Gallardy

Brent Darling

Sounds like a Marvel movie character

Paul White

A non-trivial example: dbfiddle.uk/I2CPJ6FQ

Charlieface

13:48

General Melchett is in Love | Blackadder Goes Forth | BBC Comedy Greats

►

Hello Darling...

Paul White

Also: dbfiddle.uk/R37MWPba

Charlieface

@PaulWhite It quite clearly says StatementOptmLevel="TRIVIAL" so not sure what you mean.

Paul White

ha ha

Erik Darling

if only someone had a 33 part series on trivial plans to help avoid these peccadillos

Paul White

@ErikDarling Your best-selling book made a reference to that

Erik Darling

13:52

that's why it's a best-seller

Paul White

@ErikDarling Can you find it though

Determined to make the SEO joke stick here at some point

Charlieface

@ErikDarling That would be StatementOptmEarlyAbortReason="Timeout"

Or possibly StatementOptmEarlyAbortReason="MemoryLimitExceeded"

Paul White

Or MemoryLimitExceeded

Well there you go

Erik Darling

@PaulWhite if you don't mind watching a video

04 SQL Server Performance Tasting Menu Sortables

►

Paul White

Aint nobody etc.

Erik Darling

13:56

agreed

i probably shouldn't have spent a couple grand updating my recording setup over here

Paul White

Posting all your content in video form without a transcript was next-level SEO

Erik Darling

would you like to transcribe them?

Paul White

Would I!

Erik Darling

you can be my auto-correct

heh

Paul White

There's probably an app for that

Erik Darling

13:58

i can't believe @J.D. didn't plug sp_pressuredetector when talking about sos_scheduler_yield

Paul White

With the shonkiest WP plugin ever written

Erik Darling

i tried a couple auto-transcribers back when and spent more time fixing their errors than anything

Paul White

Sounds right

Sean Gallardy

:O

Paul White

AI was a mistake

Automatic translation subtitles are the best

Still a quite amazing achievement in close to real time

Sean Gallardy

14:08

I like watching them, sometimes, especially when people with different accents are speaking

It's still not great with numbers or technical jargon

Paul White

Yeah, but you can't read

Sean Gallardy

Then it works well!

Paul White

How would you know though

Or do you get someone to read the original to you?

Lot of trust

Zikato

Youtube's auto subtitles are quite decent

twitter.com/Zikato25/status/1493961870969131015

Sean Gallardy

@PaulWhite I watch the sign language people

Paul White

14:15

They lie a lot

Sean Gallardy

How would I know?

Paul White

Quite

Sean Gallardy

Look, I had to call tech support (Josh) last week, alright, clearly I'm ready to be put out to pasture

Paul White

Teams will eat your brain

Josh Darnell

Top of the day to you.

Thanks for your patience and collaboration so far on this ticket.

I am yet to get an update from our team.

I assure you; I will update you immediately when we get feedback.

I apologize for any inconveniences you might be experiencing.

Thanks for your patience with this.

Paul White

14:30

🤖

Sean Gallardy

@PaulWhite Robots would be cheaper to employ, tbh

@JoshDarnell If you want, ping me the # and I'll take a look

Paul White

Oh, I thought that was an automated response

Sean Gallardy

@PaulWhite You'd think that, but sadly that's a paid human being

Paul White

That's what I'd do in their shoes. Take the rest of the year off.

Sean Gallardy

I did offer to replace them with a PowerShell script

Paul White

14:35

heh

Isn't that deprecated

Sean Gallardy

probably

Paul White

Josh is very patient. We should thank him.

Thanks @JoshDarnell

Sean Gallardy

That's where the $$$ are though, right? Make an infrastructure that people must use... let them build upon it for 5 years. Make a new item, sell them all the new things to replace the same things that work.

Paul White

Gotta keep busy

Sean Gallardy

I've been playing with some stuff during my non-existent non-working hours

It's both interesting and not interesting

Josh Darnell

14:39

@SeanGallardy It's not a SQL Server ticket 😁 But if you want to look it's 31781638.

@PaulWhite No need, the value of my patience is affirmed every few days in one of those emails.

Paul White

🤣

Sean Gallardy

Should be roughyl every 2-3 days

Josh Darnell

I'm starting to believe there are few things more valuable tbh.

Paul White

Must be gr8 to be valued

Sean Gallardy

@JoshDarnell That's a weird #... I wonder if it's the old system

Josh Darnell

14:41

Maybe I can sell my patience to people investing in crypto.

Paul White

@SeanGallardy I think we all know it's made up

You have to buy Super Premium Pro to get a real ticket

Sean Gallardy

Yeah it's in the old system, I found it

Paul White

A likely story

Collusion detected

Sean Gallardy

Not bad for a guy that can't read

or write

Paul White

4 mins ago, by Sean Gallardy

Should be roughyl every 2-3 days

Certainly can't write

Sean Gallardy

14:44

lettuce continue

Josh Darnell

Can't believe the super modern Azure Graph Rest API folks are using something referred to as "the old system."

Really makes you wonder.

Paul White

It's just a different waste paper basket

At least they're taking the 'rest' aspect seriously

Sean Gallardy

@JoshDarnell It's a short/long history. It was a system made by a team who decided to do their own thing rather than use what was already being built. Soon as the "new" system, was built this system was deprecated. Then 1.5 years later, a new "new" system was made, replacing the new system. Thus, this system is actually older.

J.D.

@ErikDarling lol, pretty sure I have you a plug or two in some of my other recent answers at least. But yea TBH I still need to download that thing and give it a go when I get a chance. I haven't been doing much tuning in the last month or so here. To busy with menial work. 😐

Sean Gallardy

This system (Rave) was built when the competing system (SD) was being tested. SD replaced MSSOLVE. SD had a shelf life of 18 month, after being built for the last 2 years from the ground up. Now DfM (Dynamics) is used as the newest system.

Rave is exclusively used by Office, everyone else uses DfM

Paul White

14:49

Does everyone rave about it

Gr8 name tho

Sean Gallardy

No one does, everyone hates it

Also this ticket has been open since July...

Paul White

Terrible name then

Sean Gallardy

They rave about how much they hate it

👃

Paul White

Dynamics shudder

Sean Gallardy

Yeah, I counted the number of clicks it took to just OPEN and LOOK at a case. It's 8 clicks.

I think they get paid per click or something

To add 1 note to the case, you have to click 15 different items before you can start typing.

Paul White

14:52

And each click generates 56,000 dynamic SQL executions at the database

Sean Gallardy

most likely

Josh Darnell

@SeanGallardy Hahaha wow.

billinkc

I liked how well bookmarks work in Dynamics itself. 10 clicks to get to the particular report I wanted, which was not the report that sounds liked what I wanted "Item detail master" which was actually the overview and "Item master" was actually detail page. That's 2 years in the rearview so I'm sure it's been cleaned up immensely

Sean Gallardy

It only became worse

billinkc

The url looks like a proper entry point dynamics/Reports/ItemDetailMaster - just a GET but navigate directly, dynamics says pound sand and you're redirected to the main page.

This is me shedding a tear that I do not interact with dynamics :'(

Sean Gallardy

14:57

@JoshDarnell Apparently support dropped the ball, engineering was asking for information and an update, support didn't get back to them at all and it was closed.

Paul White

Sounds like a patience test

Josh Darnell

@SeanGallardy forced-laughing-meme.gif

Sean Gallardy

@JoshDarnell You might want to start using cocaine. That happened in September.

Paul White

> I assure you; I will update you immediately when we get feedback.

Erik Darling

i am currently looking at a dynamics gp database

what a mess

Paul White

15:03

by design

J.D.

And yet, I miss Dynamics compared to the ERP system we use over here.

Paul White

Does it use regex and EF

Josh Darnell

@SeanGallardy Good grief. They sent me that same update on Thursday about waiting to hear back from engineering!

Sean Gallardy

I saw...

:'(

2

Also, and this is for everyone in the room...

J.D.

@PaulWhite Probably regex stuffed in dynamic SQL somewhere in an obscurely abbreviated procedure that's 10,000 lines long with NOLOCK hints everywhere.

Sean Gallardy

15:07

If you're using Azure and have an issue, the data only exists for <30 days. So if anything lasts longer than that, there will be nothing to investigate.

3

:cough: Josh :cough:

Josh Darnell

@SeanGallardy Dang huh.

Sean Gallardy

Don't be surprised if you get asked to "repro" the issue every ~20 days

Josh Darnell

Maybe that explains why they've asked us to repeat the problem so many times since we opened the ticket.

Hahaha jinx

Sean Gallardy

yup

WELCOME TO THE CLOUD

Thanks for your money

3

Paul White

I heard Azure has infinitely expandable storage

And infinitely expendable customers

Sean Gallardy

15:08

You_Sit_On_A_Throne_Of_Lies.gif

Paul White

With infinite patience

J.D.

So this is what Marvel's Avengers: Endgame is really about?

billinkc

mumbles carry the 1 Well, my ticket's only been opened for 24 days, I'm sure I'll get a RCA response this week, right?

Zikato

4

And one for @JoshDarnell

7

Josh Darnell

@Zikato That's too good 🤣🤣🤣

Paul White

15:22

@Zikato That made laugh at my screen

Sean Gallardy

I see my caveman english skills are starting to infect others

bbaird

15:58

Good morning heap

Josh Darnell

16:23

Is it?

bbaird

lol no it's Monday who am I kidding

Sean Gallardy

Every day is a great day :)

No difference but name only between Monday and Friday

J.D.

17:01

@SeanGallardy It's a great day for a great day! 😃

Sean Gallardy

fo' sho'

Have you ever read a response about something, from someone, where you look at it and wonder how that person even works in technology? I mean, seriously wondered.

> Instead of separate tracks like RTM, GDR, CU, Microsoft should make one GDR patch that applies to all versions. For example, if you're on CU5 then the GDR patch can be applied and it makes it CU5+GDR. If a CU comes out after the GDR, the GDR should be able to cover all future CUs since the GDR changes do not interfere with CU release changes. Thus, a GDR produced in 2019 coudl be applied to a CU from 2022.

What, the actual.

Erik Darling

sounds like a real mvp

Sean Gallardy

I have no idea how to respond to that

Luckily, I don't have to

It just makes me wonder

How these people accomplish anything

J.D.

17:18

@SeanGallardy I have no idea how to read that. Good luck.

Josh Darnell

17:32

@SeanGallardy Wow.

> ...we will be talking about the newly announced Azure Arc-enabled SQL Managed Instance Landing Zone Accelerator and the Jumpstart ArcBox flavor – “ArcBox for DataOps.”

That sentence is pretty...um...

Sean Gallardy

@JoshDarnell I see we're now just hitting random on words and putting them into a sentence.

Josh Darnell

Yeah, exactly. That one really boggled my mind.

Naidamag

17:56

Hola alguien podria ayudarme con este error Uncaught (in promise) TypeError: n.indexOf is not a function

ypercubeᵀᴹ

19:12

@Naidamag hola. The language used here is mostly English. We may have someone that knows Spanish but it's not likely.

Also your error seem to be of some obscure script language. If it's not about SQL, this is not the correct chat room

@SeanGallardy come on, it wouldn't be that hard. Just a few million branches and a gazillion merge conflicts to solve ;)

Sean Gallardy

19:27

@ypercubeᵀᴹ /starts rocking in seat muttering "merge conflicts detected..." ad-nausea

Paul White

19:41

Azure Arc Buzzword Accelerator

Erik Darling

20:40

@JoshDarnell I hope the nipples fall off whoever wrote that

Charlieface

21:09

Hilbert's paradox of the Grand Hotel

Hilbert's paradox of the Grand Hotel (colloquial: Infinite Hotel Paradox or Hilbert's Hotel) is a thought experiment which illustrates a counterintuitive property of infinite sets. It is demonstrated that a fully occupied hotel with infinitely many rooms may still accommodate additional guests, even infinitely many of them, and this process may be repeated infinitely often. The idea was introduced by David Hilbert in a 1924 lecture "Über das Unendliche", reprinted in (Hilbert 2013, p.730), and was popularized through George Gamow's 1947 book One Two Three... Infinity. == The paradox == Consider...

bbaird

@Charlieface There was a good Veritasium video on that

Charlieface

@bbaird My father did infinities for his Pure Maths MSc, so he used to tell me this kind of stuff.

J.D.

@ErikDarling so I ended up carving out a little time to play around with it today, and have amended my answer to give you that 🔌. 😉 Looks useful for a quick overview of what's going on with the CPU / Memory. Definitely could've used this at my last gig with the FinTech datas.

bbaird

I wish I had taken more set theory stuff in college, definitely more interested in it now than I was then (more concerned just getting out the door)

Erik Darling

@J.D. i don't care what paul says about you, you're a good guy

J.D.

21:25

😆

Transcript for

The Heap™ – Consultancy ©®