The Heap™ – Consultancy ©®

@JoeObbish In general? Yes. And it can be wrong also e.g. index spools, but for an individual plan, the per-op est. no. of execs should be 1 + ReB + ReW also per-op.

Joe Obbish

yeah, was thinking of the row count spool in that plan I linked earlier

would need to classify operators into buckets depending on how they behave

don't envy the sql sentry guys who reverse engineered it

oh, since you're here I have a proposition for you

Paul White

@JoeObbish Not following you. What's the issue in that plan?

Joe Obbish

oh, nevermind

that's fine

no issue

Paul White

The spool itself has 7617 ReW and 2 ReB, +1 = 7620 executions

Oh Ok

@JoeObbish I'm already married

Joe Obbish

3:35 AM

let's keep it family friendly

Paul White

k

Joe Obbish

if I blog about join cardinality estimates will you tell me which parts I got wrong?

Paul White

@JoeObbish Have you read Dima's post on that?

Joe Obbish

his site is down. not sure

does he cover it well?

Paul White

@JoeObbish Yes indeed. webcache.googleusercontent.com/search?q=cache:http://…

That's cached. I doubt the main site will be down for long.

Don't take that as me saying don't bother; just read that first

Joe Obbish

3:39 AM

my confidence is destroyed

Paul White

You sound like Erik

Joe Obbish

I feel like Erik

Paul White

Nov 17 at 1:17, by sp_BlitzErik

but like most things as soon as someone asks my confidence shatters

Joe Obbish

well the title was probably going to be something like "join cardinality estimates for mere mortals"

Paul White

@JoeObbish Get a neck tattoo

@JoeObbish Sounds good. Give it a crack.

Joe Obbish

3:40 AM

I'll probably do it anyway, but aim for something that's easier to digest

for whatever reason none of the explanations for containment made sense to me

Paul White

That's fair. It's a tricky concept in some ways, and different people appreciate different ways of explaining the same thing.

Containment would make a post of its own

Joe Obbish

yeah I think that you're right

that was the original goal

Paul White

@JoeObbish Speaking of goals, I'm on-and-off writing a bit about that row goal thing we discussed a while back. Would you prefer I not link to your post and Connect item?

swasheck

i think that containment would be an excellent research topic/post.

Joe Obbish

@swasheck I'm still embarrassed that it took working on an Oracle query for me to mostly understand it

@PaulWhite I would be honored if you would link to me, but please do whatever you feel is best

Paul White

3:45 AM

@JoeObbish Well the identicon made me think you value your anonymity

swasheck

@JoeObbish why? sometimes it takes a different perspective. i didnt really have a grasp of grammar until i studied greek and german.

we're humans and how we process information isn't always linear. if oracle is what did it for you then so be it. amazing! you mostly understand it. help the rest of us :D

Paul White

It might not see the light of day. It depends on how happy I am with the final result.

Joe Obbish

@PaulWhite well I have my name on the blog post, no so worries

do you mind sending it to me if you don't want to publish?

@swasheck now I think I don't understand grammar either..

Paul White

@JoeObbish Sure.

Joe Obbish

@swasheck hopefully I can make it through the entire post and still think that I understand it

swasheck

3:48 AM

@PaulWhite i'd like to read your draft, if that's available

Joe Obbish

we're starting to slowly use the new CE at work

trying to understand it a bit better

swasheck

which "new CE?" the 2014 flavor or the 2016 flavor?

Joe Obbish

130

wonder how different 140 is

I think for 130 they announced a change to start using multi-column stats. Can't remember anything being said for 140

swasheck

were there any changes in 140? yeah. i know that exponential backoff barfed on multi-column in 120. i've not heard of anything new in 140 and i dont see anything in the "what's new" section

Joe Obbish

no one knows! or no one is telling

swasheck

3:55 AM

hah!

special consideration for linux and ce

Joe Obbish

with a real workload to test it might be possible to get somewhere with the query store

swasheck

unless it's on a readable secondary

:D

Joe Obbish

someone told me to vote for that connect item so I did

swasheck

@sp_BlitzErik that really was an excellent read. i dont see many personal, IT-related stories that are so engaging.

@JoeObbish do you work with this guy?

L_ N___

@sqL_handLe, Fitchburg, WI

No-no-no… nobody's fault but mine. ~~database fikkxr~~

73.4k tweets, 1.4k followers, following 2k users

Joe Obbish

@swasheck yep, what makes you ask?

swasheck

4:10 AM

i thought i remember you saying that so i was just wondering. he's really a pretty great mind and i've used his work a lot

Joe Obbish

Well I don't know if I linked to his twitter, but yes, I'm lucky to work with him

Do you mind if I pass what you said along?

swasheck

you didnt. but i was able to do some math ... 1+1=2 and all that.

also, please do.

Joe Obbish

done

I think he's only been doing SQL Server for five years or so

swasheck

yeah, but a lot of what he does with storage and disk queues translates regardless of platform. it's pretty remarkable stuff that i've learned from him

Joe Obbish

I don't follow all of it, but I don't think that I would have the patience to defend my position so vigorously

dezso

7:26 AM

@sp_BlitzErik after the BrentO newsletter had the link, it is very surprising to see people reading it. I also wanted to link (to collect the stars here, of course), but was too lazy to search if it was already posted.

hot2use

7:38 AM

Hello everybody

Andy K

8:21 AM

morning and evening

James L

10:04 AM

@JoeObbish For some reason I thought you were in the UK (and that the company he works for didn't have a UK presence)

McNets

1:44 PM

morning

Joe Obbish

2:42 PM

@JamesL never been to the UK. might go in 2019 for sql bits

we do have a small UK presence

Tom V

@JoeObbish Too bad he was only active for a week on this site

He left after posting 2 answers

Joe Obbish

@TomV yep, and quite an answer it was

gbn

3:02 PM

@JoeObbish Don't get too excited. Probably exotic to some people but I think it is a shithole

hot2use

@gbn four reasons to visit U.K. 1. Beer 2. Fish 'n Chips 3. Walkers Crisps 4. Pubs

Joe Obbish

@gbn can't be exotic if everyone speaks your language

gbn

@hot2use I'll agree point 2. Points 1 and 4 roll into "cheap beer, get w*nkered for a tenner on cheap Stella"

Joe Obbish

I will be sure to temper my excitement though

gbn

Point 3: you sir, are a barbarian.

hot2use

3:12 PM

:-)

The Hop Pole in Willerby

The Old Bridge Inn, Ripponden

(grew up in Ripponden)

Joe Obbish

nevermind, apparently everyone doesn't speak my language there

gbn

@JoeObbish It depends where you go. As we northern Brits (me and @hot2use) say, London's OK if you like foreigners.

hot2use

@gbn You know what? I've never been to London. I'd go visit Edinburgh, Hull, Manchester, York, Beverly, Southampton, Chester, Kent, Reading, Bath, Aberdaron (Wales) any time, but I've never had the urge to visit London.

gbn

@hot2use I've spent about 4 weeks in total there. 3 week of courses, 1 week as a tourist June 2016 (from Malta)

I know a fair few European cities better than London

hot2use

gotta hop. Train will be leaving in 10.

gbn

3:25 PM

Ciao

McNets

4:41 PM

Does anybody know/use a SFTP .net library other than edFtp?

Forrest

@McNets I'm out of my element with this, but I think WinSCP has a .NET library that supports SFTP.

McNets

@Forrest oK Let me take a look, thanks

Evan Carroll

5:01 PM

how are my people doing!

When I join #mysql, #php, or #perl. I always get <3 /s

gbn

5:20 PM

@McNets I've used chilkat before

@EvanCarroll We're DBAs. Have you just outed yourself as one of them front end types?

Evan Carroll

Heh, on the matter of identity I'm certainly not a DBA.

I'm just a programmer with an awry interest in PostgreSQL who came to learn more. Though in my year stay here, I've learned a good bit.

ssh -D is awesome

for those not using Linux, I wonder how you do it

And on that note, has anyone used cgroups

Andriy M

5:35 PM

Those not using Linux might wonder what ssh -D does.

Evan Carroll

it opens a socks5 proxy to a desired host.

SSH is a method of remote administration on Linux, it allows us to access remote computers with a shell, but it's kind of a bloated system.

So one of the things it can do by default is open a socks5 proxy on the remote server.

So for instance, I can while at a shitty wifi area open up a socks5 connection to any machine and then open up chrome in my socks5 profile, or firefox and things just work. I usually reserve firefox just for socks5 communication

ssh -D 5050 remotebox and then firefox is set to use socks5 on localhost 5050, and I bypass all firewalls that have ssh open.

I also use VPNs, but they're much more a pain in the ass. I often don't want to slow down all traffic just to bypass a firewall for one site and the like.

sp_BlitzErik

@gbn going to add error handling to databaserestore do you have a style preference on that?

gbn

5:50 PM

@sp_BlitzErik nah

Andriy M

@EvanCarroll I think I've got the idea. No clue about an equivalent of ssh -D in Windows or any other OS, though, but that's solely because I've never been well versed (or much interested, to be honest) in fine details of how network works. Thanks anyway, it was educating.

Evan Carroll

It's a chasm when you're on the other side but innate desire pushes most to pick it up when it's put in front of you, and Linux puts a lot more in front of you.

sp_BlitzErik

like an atlantic city buffet

Tom V

6:33 PM

@EvanCarroll how are things like dns leakage and stuff on that setup?

Not criticism by the way. I just noticed some firewalls can be hard to get around

Evan Carroll

@TomV it's a good question. You can always leak with SOCKS5 so you're reliant on the client applications support for SOCKS5. In the case of Firefox, I trust it but I also tested it myself.

Tom V

I mostly rely on IVPN when on public wifi or on behind weird firewalls but I agree it's overkill in a lot of scenarios

Evan Carroll

I just don't like the slow down of the added hop.

I run dual strong swan and openvpn depending on if one is blocked.

Manly so I can always print.

Tom V

You have your own remotebox on all the time the I suppose?

Evan Carroll

I have LEDE running on a router at home.

So usuaully I can get to it, but about a month ago I just canceled ISP service

I always have a few remote boxes though.

Tom V

6:44 PM

I mostly use it on public wifi or to access restricted content I would have access to when at home by pretending to be in Belgium

Evan Carroll

the ISP wanted $80 a month, when their promo was $25 and they didn't want to give it to me.

So printing wasn't worth $80/mo

Yea, connecting to home/vpn to get around restricted content is a totally cool too. I imagine a lot of windows users set that up, or use openvpn.

So what I found with my phone was that using EasyTether I could get around download caps.

So now I run OpenLEDE on the router at home, and just plug in my phone.

The only thing that sucked about canceling the internet was that I found out I couldn't Chromecast without internet access, so my phone has to be on the router.

jcolebrand

@EvanCarroll I ...

Is this fictional?

Evan Carroll

No, Chromecast requires constant access to the internet to play local files.

jcolebrand

Wow

Evan Carroll

Which is stupid and fucked and makes me want to throw them out and just build an RPi box.

yea chromecast really sucks.

It also can't watch youtube at 2x speed

Chomrecast third party support is also horrible -- especially this late in the game.

What the world really needs is a cheap h.265 hardware controller SOC on an open platform

Tom V

6:53 PM

@EvanCarroll you could cast your screen if you are on Android

@EvanCarroll I don't consider content restricted if I pay for it at home but can't use it because I'm traveling

Evan Carroll

Well, you can if your phone runs a client that supports 2x playback and casting to chromecast, which is afaik not possible.

I'm not even sure if android VLC supports playback to chromecast, vlc 2x playback to chromecast is supper shitty but it'll support 2x playback.

Tom V

The easy play would probably be picking a decent isp

Evan Carroll

In the USA they're all big asshat monopolies. It's really unbearable working with them. T-Mobile is so much better. But anyway, a RiPi box is $35, I can buy two of them and solve this problem.

The only thing about the RPi solution is h.265 I'm not sure how well they'd handle that.

You can technically write a hardware h.265 encoder using ARM's NEON, but I'm not sure anyone has perfected it yet.

Tom V

Don't you have a elec./kodi thing on aliexpress somewhere 🙂

Evan Carroll

I'm sure there are a lot, but the question is which one does h.265 playback.

Tom V

7:02 PM

decently

Evan Carroll

Yep!

And of course run PostgreSQL.

I've been wanting to get it working on my phone for a while with the arm port

Just so I can say I have a billion rows running.. ON MY PHONE.

Tom V

To be honest that's one of the few successful government interventions in .be

Opening the cable infrastructure to multiple providers

Evan Carroll

The market solution is so bleh. I'm more of the mindset it should be nationalized and provided for free.

We have the private-unregulated monopoly thing here now, it's the absolute worst though.

Tom V

I would have guessed so

It's a difficult discussion. Some of the infrastructure is paid for by tax money some by private capital and I see how they all want something out of it

Evan Carroll

It's in the best interests of the people to concern themselves with the interests of private capital precisely 0% of the time.

Generously provide them with the ability to walk away without a gulag'ing if they leave the infrastructure and peacefully walk away. =)

Tom V

7:14 PM

I disagree but this isn't the political chat room

gbn

8:54 PM

There should be a separate room for this.
Us Europeans have experienced nationalisation: it's not good

Evan Carroll

That weird.

I'm confused. are you trying to open the topic or kill the conversation?

gbn

"it depends"

Evan Carroll

Well, I'm on irc if you want to have a conversation

gbn

I'd suggest that we each think the grass is greener over there. Your experience with "the market" is harsher than a Europeans, and vice versa on many things

hot2use

9:27 PM

ooo, some person doesn't like my answer

gbn

It happens.

Evan Carroll

@hot2use link?

hot2use

1

Q: what would be the right permission to allow everything else but overwriting or creating a database?

I have a test environment where the AD group mycompany\developers is currently sysadmin. Recently I have had some issues, specially regarding to people restoring databases. My concerns are: sometimes the database is involved in replication the permissions are overwritten - the correct way i...

I'm not goint to retract my answer on this one, because I have proven it works. I will let the community decide which answer is correct and suitable for OP.

Evan Carroll

That markdown formatting is the balls.

hot2use

Mine? Possible.

Evan Carroll

9:35 PM

I upvoted it and fixed it.

@hot2use if you're going to put that much work into an answer, you should learn markdown lists. =)

ypercubeᵀᴹ

@hot2use I've no idea. I'm clueless about permissions and roes in SQL Server - so I'll not participat ein voting, either up or down.

sepupic seems to have a point when he mentions the diff between restore and backup. But I've no idea who is right on the points you diasgree.

0

Q: Which of these two MYSQL queries is better (and what is the default join type)?

My SQL: select count('1') from plays, promotion, partner where partner.id = 1 and promotion.partner_id = partner.id and plays.promotion_id = promotion.id SQL generated by Eloquent (a laravel PHP ORM) select count(*) as aggregate from `plays` inner join `promotion` on `...

mysql

When the ORM produces better SQL than the developer ...

gbn

9:56 PM

@hot2use It's a curious question. I'd tend to go balls in and revoke sysadmin. Oh wait, I did and they don't have it back yet here.

Then wrap the privileged stuff into stored procs with EXECUTE AS, or even old fashioned alerts + jobs

Evan Carroll

@ypercubeᵀᴹ evil glance...

Though I will actually watch it because both of you two seem way vested in the answer.

hot2use

@ypercubeᵀᴹ There is no DENY RESTORE... permission

ypercubeᵀᴹ

@hot2use As I've already said, I wouldn't know. But I believe you.

hot2use

@gbn Agreed.

ypercubeᵀᴹ

The point I see valid is the "why are we talking about backup permission when the question is not about back up"

hot2use

10:05 PM

@ypercubeᵀᴹ I understand that

gbn

What are devs going to restore? Their own backups quite often. Which will be anywhere and everywhere with odd names

I assume they are idiots until proven otherwise. And never waste a good crisis when someone drops a DB or otherwise destroys things

ypercubeᵀᴹ

@hot2use I hadn't read the part (of your lonooooog answer) where you state that "No backup privilege = No restore privilege."

And of course, as gbn points, if they can't backup, they probably won't try to restore anything, privilege or not ;)

Joe Obbish

10:52 PM

Does anyone know offhand if it's possible to define constraints on a table so that no rows can be inserted into it? (SQL Server)

Forrest

Googling shows yes, you can create a 1=0 constraint. Cool!

Joe Obbish

thanks, was too lazy to google

Forrest

np, too intrigued not to

Joe Obbish

have to prevent bad actors from inserting into your tables

Transcript for

The Heap™ – Consultancy ©®