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

Joe Obbish

15:00

I'll test again later just to minimize the changes of making a fool of myself

Paul White

That does not mean it is correct.

Joe Obbish

but I'm pretty sure they're wrong

They do seem to respond to comments on blogs, so I'll give that a shot

Max Vernon

I tell you more: if the optimizer will choose the parallel plan to calculate a sum of a float column, your result will be different when executed with different degree of parallelism — sepupic 4 hours ago

^^^^ strange

Joe Obbish

Makes sense

Right?

Paul White

Sure.

Joe Obbish

15:01

Never thought about it or ran into it personally

don't work with floats often, in that capacity

Paul White

Order of operations can affect the result.

But it's all rather missing the point of floating point values.

And the point of the "answer" box on a Q & A site.

waffles

swasheck

i had a developer tell me there was a bug in the sql server engine (ergo, we should move to mongo) because he couldnt get consistent results on calculations. it was serious because, well, we're an investments firm. then i saw he was using float for data types.

Max Vernon

nice - the moral of the story - don't use imprecise data types when you need precision

Paul White

@JoeObbish Be careful, the tiger team like nothing more than humiliating delicate fawns in public :)

They're quite famous for it

Joe Obbish

I'll use my "Eric Darling" account to ask the question

2

Lamak

15:11

@JoeObbish link?

Paul White

...and limited range or huge storage size

Joe Obbish

@Lamak blogs.msdn.microsoft.com/sql_server_team/…

Paul White

@JoeObbish A sensible precaution

Terrible title for that post

sp_BlitzErik

everyone knows my ms blog troll account name is charles liebling

Lamak

@sp_BlitzErik you have a non troll account??

3

Paul White

15:14

zing

Joe Obbish

Lamak is feisty today

Paul White

Lack of KFC in his diet

sp_BlitzErik

lamak is just mad that he got fired as my best friend

Lamak

@sp_BlitzErik I haven't recovered yet

@PaulWhite you know me too well

Paul White

Jul 25 at 18:33, by sp_BlitzErik

@Zane you can be my new best friend, then. @Lamak, you're fired.

Brutal

Joe Obbish

15:18

and public too

Paul White

Luckily @Lamak is a magnificent stag

Despite persistent rumours of being a camel

Lamak

@PaulWhite yeah, he was cruel

Paul White

Suck it up princess

Max Vernon

@PaulWhite how very droll of you.

Lamak

@PaulWhite your dark side keeps showing lately

;)

swasheck

15:21

llamak

4

Max Vernon

nice

Lamak

Joe Obbish

@PaulWhite oh I finally got the pun here

nice one

Paul White

Mar 3 at 7:01, by Paul White

@EvanCarroll Strike me down with all your hatred, and your journey towards the dark side will be complete.

@Lamak I love that picture

Oct 26 '11 at 14:03, by gbn

@DTest Thank you. I'll turn you to the dark side eventually

Goodness me it's been going on forever.

Lamak

@PaulWhite just yesterday I was searching for a comment and realized that we've been here since at least 2011

Paul White

15:24

If this room had IGNORE_DUP_KEY set we'd likely never see a new message.

@Lamak Kinda scary huh

bluefeet

@Lamak old timer

Lamak

definitely

Joe Obbish

I'm a 2017 kid

Lamak

@bluefeet yeah...I've had good times here

bluefeet

@Lamak I think we all have, that's why we come back

swasheck

15:26

good times bad times, you know i've had my share

bluefeet

Hi @swasheck how are you doing?

Lamak

@swasheck well, you are part of why I've had good times, so good to see you're still here

2

swasheck

@bluefeet ok.

our company is either laying off people or moving to the cloud. it's toxic. i'm now "the cloud sql server guy" and i hate it.

Paul White

Whatever happened to @billinkc. I miss him and his photoshopped fried chicken owls.

bluefeet

@swasheck oh geez, I'm sorry

swasheck

15:28

i missed out on a good job because the timing didnt work well with my vacation to italy.

@bluefeet 1st world problems.

@Lamak thanks.

dezso

@swasheck I guess those are left behind upon whom gravity has a bigger effect

Paul White

@swasheck How Far would you Go from Wells?

Lamak

@PaulWhite and kangaroos

well, maybe we don't miss those ones

Paul White

Not too much

swasheck

@dezso that sounds like greek philosophy

dezso

15:30

@swasheck except that things are falling upwards in your case?

Paul White

Oh good grief

swasheck

@PaulWhite what do you mean? are you recommending a book? are you asking me how far i'd go from Wells Fargo (that's @Zane ... i work for oppenheimer). or would i move to wellington?

bluefeet

@Lamak scary

dezso

→ 1 message moved to Trash

Paul White

@swasheck Bugger, sorry. I don't have an Oppenheimer pun to hand.

Joe Obbish

15:31

booooo

Paul White

yay!

Andy K

see ya

Lamak

@bluefeet yup, the stuff that nightmares are made of

swasheck

@PaulWhite that's ok. usually it's something about a bomb

dezso

/me the small Caesaromaniac

swasheck

15:33

how are you, @bluefeet?

bluefeet

@swasheck I'm good, thanks

swasheck

great to hear.

Lamak

@dezso I still have more pictures

;)

Paul White

Hard to disapprove of that one

Who was the original author?

I kinda want to say it was @AndriyM

swasheck

not to change the subject because this is really fun ... but my appearance is because i believe i read an article the other day that mentioned that SQL Server's (readahead?) reads are no longer limited to 512K. default chrome history can't find it. anyone have any recollection of such an article?

Lamak

15:38

@PaulWhite that was actually me and my mspaint skillz

Paul White

@Lamak Really? Oh well congrats then.

sp_BlitzErik

does anyone in here happen to have sentry one monitoring running?

swasheck

@sp_BlitzErik yes

Paul White

9

A: Bug in Rebuild on SQL Server 2016 and above?

The queries in question exercise the SQL Server read-ahead feature. With read-ahead performance optimization, the SQL Server storage engine prefetches data during scans so that pages are already in buffer cache when needed by the query so less time is spent waiting for data during query execution...

Lamak

@PaulWhite yeah, and the good thing is it doesn't even need context, since a picture of @billinkc getting hit is always appropriate

swasheck

15:40

@PaulWhite yes. that's the one!

sp_BlitzErik

sweet! can you tell me the application name that runs the trace using who is active or something?

Paul White

@swasheck Sweet as

Joe Obbish

Paul White is the best search engine

Paul White

Seek and ye shall find

Scan and ye shall still find, eventually

3

I just saw on TV President Trump say newcomers to America would be assimilated

swasheck

@sp_BlitzErik SentryOne 11.0-Server

sp_BlitzErik

15:44

wasn't there a blog with "seek and ye shall scan" in it?

@swasheck thanks!

swasheck

ya. sorry for the delay. on my morning standup

though i'm sitting

Paul White

@sp_BlitzErik Itzik in SQL Magazine

Joe Obbish

not much better than a good scan

sp_BlitzErik

@swasheck i won't put that on your permanent record, then

Lamak

@PaulWhite I no longer can tell if those are joke articles or real ones...too bizarre

Paul White

15:46

@Lamak Was live on TV

The Borg reference caught my attention

Struck me as a new direction

swasheck

@PaulWhite still can no longer tell if they're jokes or real statements

Paul White

Well maybe but I'm not getting into that :)

dezso

@PaulWhite much better than being assassinated

swasheck

article on CNN yesterday ... trump "jokes" about things that aren't funny

Lamak

@PaulWhite so, it still doesn't happen here, just in the future

Paul White

15:47

@dezso Given a straight choice, yes I suppose so!

sp_BlitzErik

i'm starting to suspect cnn doesn't much care for this trump fella

Paul White

@Lamak Yeah you'll get it tomorrow

Lamak

good. I really can't stand the way he talks though, it's like a parody, don't know how you managed to watch him long enough

Paul White

This time of the morning there isn't much to watch. I have the Aussie news on in the background much of the time, but it switches to UK Sky News about 3am

It's just background noise really

Lamak

I see

sp_BlitzErik

15:52

do you work better at these hours or do you have clients in the right time zones?

Paul White

Bit of both.

Lamak

@sp_BlitzErik "do you work better" implies that @PaulWhite sometimes works worse...that's blasphemy

sp_BlitzErik

@Lamak i leave that judgement up to paul, my question is about his estimation of his work :)

Paul White

I have several clones; it kinda depends which one is on duty

sp_BlitzErik

@PaulWhite do you all have a moderator hat, or do you switch off?

Lamak

15:54

@PaulWhite are those like Homer clones?

sp_BlitzErik

is this where your fascination with the switch operator comes from?

2

Paul White

@Lamak Probably. That's a modern culture reference and you know how we are with those

@sp_BlitzErik There can be only one (at a time)

@sp_BlitzErik Maybe :)

Joe Obbish

When's the last time we got a new operator anyway?

Paul White

Like adaptive join?

swasheck

BOOM!

sp_BlitzErik

15:57

tssss damn

Joe Obbish

not out yet!

Paul White

Or referential integrity?

Joe Obbish

Anything released/worth paying attention to

swasheck

i'm sure bWin is running it in production

Paul White

Isn't SE on 2017 yet?

They're normally pretty swift and pre-RTM

swasheck

15:59

they are/were part of that program ... i forgot its name.

one too many concussions

Paul White

@JoeObbish Window Aggregate?

@swasheck TAP?

swasheck

yep

Joe Obbish

ok, I'll accept that one

sp_BlitzErik

[batch mode window aggregate]

Paul White

"What have the Romans ever done for us?"

@sp_BlitzErik I'm very pleased with your addendum

Lamak

16:00

@PaulWhite I remember that too

Joe Obbish

all I know about is the TF 2301 incident

Paul White

Hey @sp_BlitzErik is it actually Erik with a k or is that an affectation?

sp_BlitzErik

@PaulWhite that was a silly addendum to your four lines up, pal

Paul White

Our dewy eyed deer spelt/spelled it Eric earlier and it got starred. Bugging me.

sp_BlitzErik

@PaulWhite i was born this way

Joe Obbish

16:02

@PaulWhite I spelled it wrong on purpose

sp_BlitzErik

@PaulWhite he's mannerless

brb office hours

Joe Obbish

because it's not actually him

and it would be wrong to use another man's name

Paul White

@JoeObbish OIC

Joe Obbish

I could also use my "Pablo Blanco" account

Paul White

Or another man's identicon

@JoeObbish Whenever people write that I hear a song

Paloma Blanca

"Paloma Blanca", often called "Una Paloma Blanca" (Spanish for "white dove") is a song written by the Dutch musician George Baker, and first recorded and released by his band The George Baker Selection. The single was a hit throughout Europe, and was taken from the group's fifth album of the same name. The song became a #1 hit on the U.S. Billboard Easy Listening Singles chart on 14 February 1976 (becoming that chart's overall #1 song for 1976) and peaked at #26 on the U.S. Billboard Hot 100, as well as peaking at #33 on the U.S. Hot Country Singles chart. and #10 on the UK Singles Chart. ��2...

Lamak

16:05

uh

> Our usage of SQL is pretty simple. Simple is fast. Though some queries can be crazy, our interaction with SQL itself is fairly vanilla. We have some legacy Linq2Sql, but all new development is using Dapper, our open source Micro-ORM using POCOs. Let me put this another way: Stack Overflow has only 1 stored procedure in the database and I intend to move that last vestige into code.

Paul White

Sounds like the sort of crazy thing Nick Craver might say

Joe Obbish

wow they wiped out the system stored procedures?

that's impressive

Lamak

@PaulWhite you know Nick Craver very well

Paul White

There's a reason we blame caching for everything

Joe Obbish

How do they troubleshoot issues? perfmon?

Paul White

16:06

Prayer

Lamak

@JoeObbish yeah, surprised me a little

Paul White

People that can't write fast SQL abstract the problem away

Lamak

> Exceptional - Error logger for SQL, JSON, MySQL, etc.

Joe Obbish

least favorite words in an SE question: "I'm using an ORM"

Paul White

Automatic move on

Lamak

16:08

what about "Micro-ORM"?

Joe Obbish

that sounds even worse

Paul White

Put enough layers on a thing and you can get away with anything

No one knows who to blame any more

Except caching of course

Lamak

> Dapper (.Net Core) - High-performance Micro-ORM for ADO.Net
StackExchange.Redis - High-performance Redis client
MiniProfiler - Lightweight profiler we run on every page (also supports Ruby, Go, and Node)
Exceptional - Error logger for SQL, JSON, MySQL, etc.
Jil - High-performance JSON (de)serializer
Sigil - A .Net CIL generation helper (for when C# isn’t fast enough)
NetGain - High-performance websocket server
Opserver - Monitoring dashboard polling most systems directly and feeding from Orion, Bosun, or WMI as well.

Paul White

smh

Andriy M

@PaulWhite My painting or graphics editing skills leave much to be desired.

Paul White

16:11

@AndriyM Maybe you just need practice?

Andriy M

Or inspiration

And Bill's photogenic persona might be just the thing, who knows

Lamak

the post is quite interesting though

and the comments too

> Stored procedures are the source of evil. I am happy to hear that you are staying away from them. Also I would have expected more problems in the websocket connections. I would be happy to hear more details about that tier. Great overview anyway! Thanks for sharing.

Paul White

11 herbs and spices should be enough to inspire anyone

@Lamak Yes. Read it before. All joking aside, it is interesting.

No idea if it is kept up to date.

Lamak

don't think so

Paul White

And of course it was all before all problems were solved by Hekaton.

</s>

Lamak

16:15

> Stored procedures are, at a minimum, harder to debug, log, profile, and source control. We prefer simplicity, and stored procedures are a layer of abstraction that don't net us any benefits.

Joe Obbish

@Lamak Link?

Lamak

@JoeObbish nickcraver.com/blog/2016/02/17/…

go to the comments

Joe Obbish

the second half of that comment is more reasonable

Paul White

Ooo SQLPrompt is finally working with SSMS 17.

I might actually start using it.

sp_BlitzErik

16:48

ahem

docs.microsoft.com/en-us/sql/sql-server/…

Joe Obbish

RC2 is out? I just recently installed RC1

Paul White

I installed it literally an hour ago. RC1 that is

Max Vernon

Dapper looks like an interesting "ORM", although I'd say it's not really an ORM, since you pass it actual T-SQL.

> The Database Tuning Advisor (DTA) has additional options and improved performance. (CTP 1.2)

lol

sp_BlitzErik

oh cool, they make you download a downloader

dezso

Hi, @dezso, your comment is superfluous, the moderator will close the question if he thinks is not appropriate, dont think they need advice on that. If you have any advice please help. The database respond when I connect with PgAdminIII, since the routing seems ok there might be some configuration file of the DB I might have not pay attention to. — bostongeorge 51 secs ago

@PaulWhite you were summoned :D

Andriy M

16:55

Do they really make you download something?

dezso

@Lamak well, in this case the comment is a custom one

Lamak

@dezso yeah, I figured, but doesn't really matter

sp_BlitzErik

@AndriyM if you wish to install rc2, yes

Max Vernon

@dezso lol, there's one way to get your question closed really fast.

dezso

@MaxVernon well, they are new to the site

you can make mistakes

well, only one

Max Vernon

16:59

:-)

@Lamak I have JavaScript turned off by NoScript so I don't see comments by default. It makes the web so much nicer ;-)

Lamak

@MaxVernon yeah, I use ghostery

Max Vernon

I love Ghostery too

Lamak

but I enable comments when I'm curious

Max Vernon

plus AdBlock

yah, I just enabled the comments javascript so I can read them on nick's site.

Lamak

@MaxVernon exactly what I use too

Paul White

17:05

@dezso You all seem to be handling it. I have no idea if it's a Postgres configuration or network thing, or whether SF or SU would be best so I'll pass.

Max Vernon

I love this comment by Nick:

disq.us/p/167nqlb

> We couldn't agree more on code-generated queries (EF, L2S, etc.), that's why we don't use them anywhere complicated or anywhere in new code. All that exist are very simple and legacy. Only the insert order for FKs which is non-trivial to rewrite allows Linq2SQL to survive at all in our solution. It works well enough though, one day we'll get to finishing the tearout.

We write almost every query (as you would using Stored Procedures), they simply live in code - not hidden in a stored procedure. Also keep in mind that we manage hundreds of copies of the same schema across hundreds of datab…

Paul White

If I had to choose, I'd probably go with Super User but really the OP might as well delete and repost rather than migrate

I'm not sure vague troubleshooting questions are a good fit anywhere TBH

I suppose I should check the OP can actually delete

Yep

0

Q: PostgreSQL Remote Connection on NAS Synology DS216j

This is what I would like to achieve: I have postgreSQL database running on NAS Synology Diskstation DS216j connected to my router. I want to be able to connect and use the database from remote outside my LAN. In order to do that I have set a portforwarding on my router to the NAS on ports: - 54...

Joe Obbish

17:22

fixed width font?

that looks terrible

sp_BlitzErik

you saw the unix right?

Andriy M

@PaulWhite Ah, but he didn't say anything about lunch in his DBA question.

Paul White

> Lunch the database

@AndriyM I think U & L provide tea and sandwiches

Andriy M

T & S

Paul White

😎

Joe Obbish

17:25

they probably use lynx anyway

Paul White

Ha. Max added an answer

Max Vernon

@PaulWhite its probably a poor answer

at least it's very short.

oh he asked that 4 days ago.

I love that he has 6 questions on U&L and no upvotes. That tells you something, I guess.

Paul White

@MaxVernon Yes indeed

swasheck

grumpy neckbeards

Max Vernon

@swasheck I downvoted the dba variant

swasheck

17:32

now stay off my lawn!

Andriy M

Have you tried here: Ask LibreOffice? — Andriy M 56 secs ago

Apparently they have their own Q&A

Max Vernon

Ask LibreOffice! Who knew!

swasheck

someone needs to do a clippy gif

looks like you're trying to ask a LibreOffice question. have you tried AskLibreOffice?

Max Vernon

@swasheck lol

sp_BlitzErik

17:44

there are 194 new xe events from 2016 > rc2

2

Max Vernon

that's a lot

swasheck

xml is cheap to create but expensive to shred

fortunately you can only use SQL server to parse XE data. why would you want to pull the schema and use powershell or .net to parse your .xel files?

sp_BlitzErik

interesting

Lamak

@sp_BlitzErik can you make that smaller?

sp_BlitzErik

@Lamak only if i put it next to your forehead

Max Vernon

17:52

@Lamak He was in the pool! youtube.com/watch?v=ZDdjeCtux3k

Lamak

@MaxVernon lol

@sp_BlitzErik you seem on the edge, everything ok?

;)

sp_BlitzErik

@Lamak dandy!

Joe Obbish

18:21

@sp_BlitzErik What makes that interesting?

honest question, not being snarky

sp_BlitzErik

@JoeObbish i had more until i was rudely interrupted

swasheck

18:43

i'm sure that @PaulWhite has more, but it looks like when a rewrite that uses an implied predicate and CE is based on the implied predicate and not off of the join calculator

sp_BlitzErik

20:57

fanning the guid debate

sql server 2017 is a troll

ypercubeᵀᴹ

21:25

@sp_BlitzErik If I understand correctly, a join on char(3) is considered more expensive than on bigint?

sp_BlitzErik

would seem that way, yes

Forrest

if it has to load collation info for a char(3) join, then I guess that makes sense

Lamak

@ypercubeᵀᴹ I don't see it

Max Vernon

21:37

Interesting view

ypercubeᵀᴹ

@Lamak 2nd line: When a condition ... However, even a varchar(6) is considered expensive ...

Joe Obbish

22:29

@sp_BlitzErik intuitively it doesn't make much sense

@MaxVernon That is interesting. I feel like I don't vote enough

sp_BlitzErik

@JoeObbish eagl

were colocated joins not possible in batch mode prior

interrogation mark

Joe Obbish

22:55

@sp_BlitzErik well consider a hash join

I hash the join keys to some value

does the data type really matter so much in that case?

is it so much more expensive to hash a varchar(6) compared to bigint?

maybe it is, I don't know

I'm not a CS guy

sp_BlitzErik

is it more work to hash a guid than an integer? i'd guess so.

is it more work to hash a varchar 6? probably to account for collisions.

Joe Obbish

I wish that stuff was exposed

number of hash buckets and fun things like that

sp_BlitzErik

same

i bet you know who knows

Joe Obbish

hm

sp_BlitzErik

but he's sworn to some black-robed secrecy

Joe Obbish

23:00

suspect database

sp_BlitzErik

DROP IT

Joe Obbish

error log full of... stuff

[WARNING] Database ID: [5]. Container ID: [{8ED5C850-4BF5-461C-8C8E-09C8B6DC1237}]. Container Path: [C:\db\Stage1\Memory\$HKv2]. Failed to allocate free file in container, will try again later. Error: 0x80070070. (d:\b\s3\sources\sql\ntdbms\hekaton\stglib\freefilemgr.cpp : 724)

got a stack dump too

wtf happened

we don't even use hekaton

sp_BlitzErik

did someone try it out once?

Joe Obbish

I need a real DBA for this

know any?

sp_BlitzErik

for free?

Joe Obbish

23:12

I'm not being serious

I'm sure that it'll be fixed by tomorrow morning

that's the plan

sp_BlitzErik

yeah these things usually just automate themselves out

Joe Obbish

personally, I suspect all of my databases

sp_BlitzErik

by "your databases" i assume you mean "tempdb"

Joe Obbish

I created my own database

I called it "my_first_database"

sp_BlitzErik

Example

CREATE SCHEMA Example

CREATE TABLE Example.Example

Joe Obbish

23:18

why would I create a schema?

you used dbo

CREATE TABLE dbo.dbo

sp_BlitzErik

SELECT * FROM Example.Example.Example AS Example

sp_BlitzErik

23:36

a trace flag just for joe

Transcript for

The Heap™ – Consultancy ©®