The Heap™ – Consultancy ©® - 2017-08-28 (page 1 of 2)

« first day (2428 days earlier) ← previous day next day → last day (2444 days later) »

12:12 AM

@Philᵀᴹ and its not like SQL is all that hard, really.

Even a crap implementation without RI is still miles ahead of a document database.

1 hour later…

1:39 AM

@JoeObbish I have your IP address now.

1:56 AM

@sp_BlitzErik me too, thanks

4 hours later…

5:40 AM

Mba'eichapa ndepyhareve (Guarani)

5:53 AM

Morning

Guarani language

Guarani ( or ), specifically the primary variety known as Paraguayan Guarani (endonym avañe'ẽ [aʋãɲẽˈʔẽ] 'the people's language'), is an indigenous language of South America that belongs to the Tupi–Guarani family of the Tupian languages. It is one of the official languages of Paraguay (along with Spanish), where it is spoken by the majority of the population, and where half of the rural population is monolingual. It is spoken by communities in neighboring countries, including parts of northeastern Argentina, southeastern Bolivia and southwestern Brazil, and is a second official language of the...

2 hours later…

8:09 AM

I was wondering if anybody has an opinion on this question: SQL query to display names of customers who have purchased all the DVD. I VtC because I thought it was "not suitable for this site" and have since read this question on meta: Shall we add a new 'off-topic' reason for questions that are 'too basic'?.

...and have retracted my vote again.

2 hours later…

10:02 AM

There have been a few relational-division questions lately.

They're all duplicates really, just with different specifics.

10:33 AM

Pretty much every Q & A we have here in that tag references the SO canonical somewhere stackoverflow.com/a/7774879

10:48 AM

@PaulWhite There is an answer there by user "wildplasser"

I'm trying to come up with an english translation

"urinating in public" seems to be the closest I can come up with

How lovely. They get a better class of user over there.

@PaulWhite true

11:12 AM

@hot2use Regarding your comment it is also true that three people voted to leave the question open (a quite unusual close review result). Also, the question has two upvotes and no downvotes.

More to the point though, there is a pending flag on that comment which means I am likely to delete it soon. Most of those comments seem to want to be an answer @hot2use. Do you wish to do that?

(sorry about the multiple pings: chat formatting tried hard to defeat me yet again)

@PaulWhite Sorry. Can I see those statistics too?

@PaulWhite No issues with that.

@hot2use Yes by clicking the link I gave, or more generally, by looking at the question's timeline.

While researching the issues and duplicates (SO) I came to the conclusion that the issue could only be resolved by the developer and that's why I added the comment.

@PaulWhite Cheers

I will refrain from adding such comments in the future.

Ok I'll need to reopen the question (with @ypercubeᵀᴹ's help) so you can answer.

(and thanks)

11:41 AM

@hot2use Oh I see you were saying you're OK with removing the comment, not that you were going to add an answer. That's fine too.

I can only reference the official documentation and the SO Q&A.

Other than that I would have to contact the developer/community over on Github for an official statement.

Then it becomes a SO question, because it's about how CouchDB was programmed.

11:59 AM

That's fine I made an answer that people can vote on and/or improve.

@PaulWhite sweet

afternoon

@PaulWhite The question has since been slightly altered, as that the OP noted missing entries in the cluster_nodes section and all_nodes was fine.

@hot2use Please feel free to edit the answer as necessary. I have to spend time elsewhere for a bit.

12:56 PM

@TomV I suspect the unmatched filtered indexes (due to parameterization) plus the reads associated with the table spool. Hard to be certain without an actual plan.

I'm a long way from interested in debugging an AX cursor plan though :)

Though it reminds me a little of the static vs. dynamic question we had once.

@PaulWhite I was leaning to his estimates being way off and an enormous amount of key lookups to the inventtable

given the predicate and the typeidx definition that seems like a very strange decision

It should probably just seek/scan the clustered index since partition and dataareaid are the first 2 fields in the index definition

@TomV For sure none of that will help.

Does AX include filtered indexes by default, or have those been added manually?

and it does seek the clustered index in the no-cursor plan

@PaulWhite Those are added automatically on tables that use table inheritance (don't get me started)

Ok :)

but you are right, if the table spool does have to swallow 200K records instead of the estimated 300 that could be painful too

1:06 PM

Especially since spools report reads proportional to rows rather than pages

It'd be easy and perhaps even trivial to tune if it weren't for AX and all its restrictions/unknowns (to me, at least)

15

A: sp_cursoropen and parallelism

FAST_FORWARD cursors do not support parallelism (though the server generating the plan would need to be 2012 or above to get NonParallelPlanReason as part of the showplan XML). When you specify FAST_FORWARD, the optimizer chooses between STATIC and DYNAMIC for you. The provided execution plan s...

That's the Q & A I was referring to btw. Not that I expect it is directly relevant, just one of the considerations.

1:36 PM

I think if he removes the parametrization so it uses the filtered indexes and (probably) the correct clustered index seek on inventtable he should be good to go.

It would still take the 30 or so seconds he has without the cursor but he seems ok with that :)

good morning

> "What do you mean?" he said. "Do you wish me a good morning, or mean that it is a good morning whether I want it or not; or that you feel good this morning; or that it is a morning to be good on?"

@PaulWhite are you citing gandalf?

36

Q: What is the purpose of the "Good Morning" dialogue from The Hobbit?

In the novel The Hobbit, towards the start of the story Bilbo wishes Gandalf a good morning: "Good Morning!" said Bilbo, and he meant it. The sun was shining, and the grass was very green. But Gandalf looked at him from under long bushy eyebrows that stuck out further than the brim of his sha...

tolkiens-legendarium the-hobbit gandalf

2:00 PM

Well that's a weird question as I join. Did I end up on hobbit.se or something?

@Lamak he is our Gandalf :)

@PaulWhite it's too early for all of that

@Marian he's just trying to remind us that parts of the LOTR were filmed in new zealand

Time zones

About 15 years ago I started a contract gig. Every sa password had "gandalf" in it somewhere

2:02 PM

google.com/maps/d/…

were the hobbit movies filmed in NZ too?

@Lamak Just the parts between the beginning and the end

> The Hobbit Trilogy was filmed entirely in New Zealand

newzealand.com/int/feature/the-hobbit-trilogy-filming-locations

@PaulWhite Why so proud of this?

ah, no wonder they were so bad

2:05 PM

@gbn Why not?

Well, you have the All Blacks and COTW...

@PaulWhite at the moment of making my comment, I didn't know were it was filmed other than the notion than new zealand was invilved

@gbn He left!

@JoeObbish Mostly google.com/maps/d/…

@PaulWhite do you appear in one of them?

The first LOTR film bored me to tears. I tried to read the book again. Tedious.

2:07 PM

IMO the best thing in NZ is Kim Dotcom

you know...as an extra kiwi

@gbn MODS

@Lamak All of them. Just way, way in the background.

@PaulWhite He's only on loan to the Brits

@JoeObbish We're hoping to export him your way

2:08 PM

@gbn I found them kind of boring too. Though not enough to make me cry

@Lamak I've never been more disappointed in you

@JoeObbish you liked them?. I guess I found them too long

and I started watching the hobbit trilogy and gave up in the middle of the first one

Lamak prefers the emoji movie

@PaulWhite I need to research

@Lamak When I watch them I watch the extended editions

except for the final hour of the third movie

2:10 PM

@PaulWhite I'm proud to say that I didn't even know that this existed

since it's an hour of hobbits crying

@PaulWhite but I now doubt your supposed lack of knowledge about pop culture

Jul 31 at 19:03, by sp_BlitzErik

@Lamak no, she likes that movie. she really wanted to see emoji movie though. i think she's been cured of that.

@PaulWhite ah, well, it was a comment by Erik, I just thought it was made up and disregarded it

fair

2:20 PM

I can't believe this query causes a stack dump

so far 2/3 tables involved have 3 rows each

in fact all of them have 3 rows

Hi all

@PaulWhite I had a go at an answer, I'm sure I got a bunch of stuff wrong though so I wouldn't mind a review

3

A: sp_cursorprepexec causing 53 million reads?

First of all, it surprises me that the actual number of rows for both queries from SQL Sentry isn't more or less the same. Second. It's hard to tell how correct your estimates are in the plan without a cursor without an actual plan but some things stand out to me. (P.S.: refer to my answer here ...

@TomV Looks good. I like the AX-specific knowledge there.

@Lamak No I just happened to have seen that question on the Hot Network Questions list just before Joe said "good morning".

voting ring activate!

@PaulWhite It helps to have access to the table definitions :)

2:32 PM

No doubt that's true as well

@gbn my wife slept while watching it (or in one of the harry potter movies, I can't remember for sure).

@Lamak @PaulWhite you're both in the danger zone now!

@sp_BlitzErik My remark wasn't linked to Lexmark's.

My lawyer assures me this is a tenable defence.

and I was talking about another erik

it's the same "lowly erik" from @JoeObbish's blog post

I'll see you all hanged for besmirching my good name

2:48 PM

I'm promoting you

@JoeObbish "highly erik"?

100% pure erik

@PaulWhite Sounds like a new street drug.

"'ere, got some Erik guv'nor?"

@Lamak he's an elite hacker

what more could you ask for

@JoeObbish not much

2:59 PM

this brings up an interesting question

if you were a drug, would you get high off yourself?

@sp_BlitzErik What do you mean "if" and "would you"

@gbn Yes and I hear surprisingly small doses can be fatal

@PaulWhite i mean them very plainly, sir

oh

well then "yes"

I'm still perplexed by this stack dump

I wonder if real databases have issues like this

pix

3:05 PM

but does it repro on linux

it doesn't repro anywhere

well then you seem not to have a problem

got this one database that I don't want anyone to touch

have you tried running dbcc checkdb on it

someone tried restoring the database to another one and it didn't repro there either, doesn't make sense

3:06 PM

blame quantum

no, someone else ran check table on the guilty parties

@sp_BlitzErik pictures of what?

I don't have permission to run checkdb

is there an aloofness competition in chat today?

2

today

yes and all other days as well

aloofness is a great word

3:07 PM

but really, you want a picture of a stack dump?

it's fun to say

don't they all look the same?

that's racist

are we not allowed to say that in 2017?

I'd prefer a picture of all the forest creatures having a nice picnic

3:09 PM

there are lots of wild rabbits

that's all I got

You wouldn't think rabbits could be so wild. They look so...innocent

that's what they want you to think

@PaulWhite so, a picture of @JoeObbish?

@Lamak ...and friends, yes

Aug 24 at 17:31, by sp_BlitzErik

@Lamak he is all creatures of the forest

I know you may have disregarded his comment, but that was when he was just lowly erik

3:12 PM

It's hard to keep up, but I'm trying. I keep a diary now.

Well it's more of a scrapbook

this soup has lettuce on the side

what the hell

who does that

rabbits

hmm

I have seen a rabbit eat lettuce before, not but soup

good effort though

carrot soup, side of lettuce

standard rabbit

carrots aren't good for rabbits

3:15 PM

4 mins ago, by Joe Obbish

that's what they want you to think

no, they want you to think the opposite

5 mins ago, by Joe Obbish

that's what they want you to think

it's turtles all the way down

so, who's winning today's competition?

Erik's so aloof he's stopped chatting

@PaulWhite me too

3:17 PM

he's hit the aloof roof

@PaulWhite that's what happens when he's high on himself

We used to feed our rabbit carrot all the time and it died. Makes you think.

Mind you there was a dog involved too.

my favorite thing about rabbits is how they run when you try to put them back in their cages

is rabbits code for girlfriends?

i'm sensing similar patterns

ha!!!

though Joe is only old enough to have a rabbit

3:24 PM

In what sense?

steady

don't worry Mr. Moderator, my parents give me permission to create an account here

I was going to ask if you knew the minimum age requirement

@sp_BlitzErik what does it mean for @PaulWhite and his overfeeding carrots to rabbits with unintended consequences?

@Lamak Probably best not to pursue it

3:25 PM

I thought the same

Apparently too many carrots can turn your skin orange

Maybe lettuce can compensate for that

I'm not too sure on the exact science

it's like messing with rgb balance

@PaulWhite they should remove carrots from trump's diet then

or feeding him more lettuce

hm we need something blue

blueberries I guess

@Lamak what are they feeding you when your avatar turns grey?

3:29 PM

@PaulWhite smurfs

@sp_BlitzErik you are the hacker, you tell me

@Lamak perfect

BP stations are giving away smurfs with petrol here at the moment

@Lamak i am lowly~

@sp_BlitzErik you were

@sp_BlitzErik Like a North Korean leader? Oh no that's ronery.

user image

4

3:32 PM

Chat needs negative stars. Maybe they can be called black holes or something

4

@JoeObbish We call him Joe

mic drop

→ 1 message moved to Trash

:D

@PaulWhite lol

How cool my reply link still works

Oh that's a nice definition for aloof, "conspicuously uninvolved".

← 1 message moved from Trash

3:37 PM

Ooo.

@PaulWhite hey, that works surprisingly well

@Lamak Surprised me. Twice.

yup

Someone put some thought into that.

how is chat better developed than comments on this site?

3:38 PM

Because chat has value. Not here of course. But in general.

the link url just has a message ID

@sp_BlitzErik comments are deliberately bad

@JoeObbish See that just removes the magic and mystery. Boo to you.

@PaulWhite You do that every day?

Well not every day.

But ok, sure, boo to me too.

2

Mirror, mirror, on the wall, who is the most conspicuously uninvolved of them all?

@billinkc

3:46 PM

this is funny

join elimination: brentozar.com/pastetheplan/?id=BJr852btb

no join elimination: brentozar.com/pastetheplan/?id=r1s3c3bKW

@sp_BlitzErik jeagl

@Lamak i shouldn't need to ;)

@sp_BlitzErik no one does

@sp_BlitzErik expected behavior, no?

although I don't know if I've fully parsed the demo, so maybe I'm missing something

@JoeObbish expected by whom?

3:51 PM

@sp_BlitzErik Oh, I see what you're doing there

-2

A: Can I disable execution plan caching for debugging purposes?

Ugh, same problem here. And none of the responses are what I'm looking for either. The general problem is that occasionally application users WILL NOT experience a pre-compiled/cached/optimized execution of a query or stored procedure. THAT is what we are trying to debug here, not the precompi...

Talking like a true manager

that's a complicated demo

> declined - flags should not be used to indicate technical inaccuracies, or an altogether wrong answer

> Dumb and wrong – sp_BlitzErik yesterday helpful

What you wanted was ON cc.UserId = pv.OwnerUserId AND cc.UserId = u.Id to be rewritten to: ON cc.UserId = u.Id

In which case the derived tables pv, pl, and pp could be eliminated. But that doesn't happen.

Do I have that right?

yeah

emails joe sack

3:55 PM

If you just change it to ON cc.UserId = u.Id then you get join elimination as expected, right?

do you mean just that join?

wait

now I'm confused again

What if there's no match in the derived table?

Who the heck writes demos against the SE database

what if pv.OwnerUserId is NULL

It seems like the optimizer cannot do that rewrite

12 mins ago, by Paul White

Mirror, mirror, on the wall, who is the most conspicuously uninvolved of them all?

3:57 PM

true

@JoeObbish brentozar.com/pastetheplan/?id=rJaq62btb

"you have less than 4 seconds left for editing"

so exciting!

I wish it showed a countdown

@sp_BlitzErik Thanks. That makes sense, but I don't think it's safe to eliminate the joins in the original demo

For example, if the posts table has no rows then no rows could be matched from cc

@sp_BlitzErik Comment flags are so rarely declined because there are only two options: delete or dismiss.

I could be missing something though

tbh i don't know how any of them are safe to eliminate

4:02 PM

@sp_BlitzErik four things: blogs.msmvps.com/robfarley/2008/11/08/…

i think you've linked me to that page 3 times

@sp_BlitzErik I'm happy to try to explain it if you want

keeping up the humor

brentozar.com/pastetheplan/?id=SJceyT-tW

@sp_BlitzErik Oh sorry I forget

if i dump everything out to temp tables, the join to #cc isn't eliminated there either

quite the rogue~

4:05 PM

what are the primary keys?

just wait 24h all will become clear

there's an established process

@JoeObbish brentozar.com/pastetheplan/?id=Hy2YkT-tZ

@PaulWhite i am prompt in my chat responses, unlike some other sql playboys

We should create some sort of self-contained demo site. I'll ask Jack if he can host something.

ask him if he can put the SO data dump on there

@sp_BlitzErik Is it just SO?

4:08 PM

@sp_BlitzErik hard to read

two col primary key on #cc, right?

single col PK on the others?

I've almost downloaded the SE database several times. But torrents and huge. Also xml.

Yeah it's not going to work with multi-column keys.

@PaulWhite I just create my own tables when needed with the right data distribution

@JoeObbish Well done you!

well, it's easier than a 100 GB torrent...

@JoeObbish BO-RING

4:12 PM

Support join elimination for multi-column foreign key constraints #WontFix

If it doesn't work in pubs, Northwind, or AdventureWorks, it's objectively not a real demo

@PaulWhite just to be clear it can work with multi-column joins

it just can't work on the basis on FKs alone, apparently

Right

(only said that so others wouldn't misinterpret)

Worth being clear, even though very few people will have any idea what we're on about

Or care, probably

But that's their loss :)

@PaulWhite we have a version with no xml, just mdf and ldf to attach (if you want)

i can send you a thumb drive with the files. it might get there faster than you downloading it.

4:20 PM

@sp_BlitzErik If I had the local SSD storage space, and thought it would be accessible enough to others, I would.

Funnily enough, my ISP changed us to 200/20 Mbps this week. The new basic plan.

Was offered 900/95 for $15 more but meh

There's literally nowhere I can download from at that speed

And I'd have to connect to my cable modem by wire

@PaulWhite i'm trying to figure out how to parse this statement

@sp_BlitzErik 1. I don't have enough spare local storage

@sp_BlitzErik 2. Any demos I wrote using it would require others to do the same to follow along

I'm OK expecting people to have AdventureWorks to hand

I didn't mean others should be able to access my local storage, though I see how it could be read that way

do you have very small SSDs?

or are they filled up with sql contraband?

I have one 500GB SSD

240GB free

best of both worlds

i'm gonna buy you a new ssd with my hardware budget

don't tell brent

4:28 PM

ha ha ha

Ok I'm out for a few hours. Look after the place.

moves all of joe's messages to trash

2

900 Mb download speed

500 GB hdd

if he got that speed he could fill his hdd in two hours

he could also do that creating indexes

anyway, did you have a question about join elimination? I'm no Pablo but I can probably answer it

no, i just found it amusing and wanted to share

but join elimination would be a great thing to blog about

4:42 PM

@sp_BlitzErik Which part of it?

all of it. make it a series. i can't recall anyone writing anything interesting about it in ages.

eh, I don't know all of it

problem with a topic like that is that other people have already done it better

link

43 mins ago, by Paul White

@sp_BlitzErik four things: https://blogs.msmvps.com/robfarley/2008/11/08/join-simplification-in-sql-server/

it's certainly informative, but i wouldn't say it's a great post, brent®

4:46 PM

the community disagrees

"Good article and helps definately to increase performance"

> 2017

> not blogging about something because someone blogged about it on 2008/11/08

part of the problem is I don't know anything about foreign keys

we don't use them

of course not, you're a columnstore man

you don't use fks in a dw product?

@sp_BlitzErik I... don't want to talk about it

lol

i wouldn't either

that's like a bar with no glasses

4:52 PM

because you just have to drink out of the bottles?

kind of like reading all of the data out of tables

put your head under the tap

5:42 PM

Is it possible to run a select / insert query on a remote server from within the mysql shell?

@Alan not a lot of mysql people here

Ok, what about with Postgress?

I want to run a command like this:
SELECT @rowcount:=COUNT(*) FROM production.database.table1;
INSERT INTO production.table1 SELECT * FROM workhorse.database.table1 where `id` >@rowcount;

@Alan there are more of those, but I'm not one of them

@Alan if you were asking about how to do that in SQL Server, you'd be getting answers.

kk

5:51 PM

@Alan I'd ask the question on the main Database Administrators site since there are plenty of MySQL and PostgreSQL pros looking there.

Thanks Max, I will

This room isn't called toy administrators

/ducks and JK

Come to think of it I haven't seen our resident postgres guru around for a while

:)

« first day (2428 days earlier) ← previous day next day → last day (2444 days later) »

Transcript for

Aug '1728

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile