The Heap™ – Consultancy ©®

sp_BlitzErik

12:00 AM

no, i prefer to ambush them

Joe Obbish

sounds like you go for networking then

I wonder how difficult it is for MS to get people to man the booths

sp_BlitzErik

beats working

maybe

Joe Obbish

12:29 AM

finally found the last piece of the puzzle on this repartition streams question

Joe Obbish

12:44 AM

if MS wants to hide something from me

they should make the simple repro take at least three joined tables

Paul White

4:58 AM

@sp_BlitzErik Take @JoeObbish's photo so we can fix his avatar

Mikael Eriksson

6:58 AM

@sp_BlitzErik I plan to, or at least I have cleared with my company that expenses are taken care of so really no excuse not to go :).

@PaulWhite You should go as well. Been a while now has it not?

@sp_BlitzErik hehe me too, I once asked Bob W about some XML stuff in the SQL Clinic. He took my email and said he would come back to me about it.

hot2use

@BradC It's not the best post in town, but I'd nominate the question to be reopened if I could.

Paul White

@MikaelEriksson Yes but time, money, and laptops on planes.

Andriy M

7:38 AM

And, er, three-dimensional objects in, er, accommodation places

McNets

morning

Andriy M

Morning

Paul White

@AndriyM Yeah that!

But to be fair that was Las Vegas not PASS

Tom V

Morning

dezso

@MichaelGreen and in Postgres you don't even have to quote it: CREATE TABLE 🐿 (🐿 integer PRIMARY KEY); just works

Paul White

7:56 AM

OMG Postgres is so amazing

McNets

It's the 10X of databases.

5

Andriy M

You didn't think it was called a proper database for nothing, now did you

ypercubeᵀᴹ

@dezso Right. You only have to quote upper case chipmunks ;)

4

Marian

8:28 AM

@TomV Klaus Aschenbrenner is a smart SQL guy, though his English is kind of rough :)

not sure you still have time to attend his bits as I saw it's early today

Tom V

@Marian It was yesterday where I couldn't pick a session

I agree Klaus is always interesting

Marian

8:57 AM

@TomV I see, so what's on the menu for today?

Tom V

@Marian Unfortunately today I'm not there because of an urgency at a customer, I still need to pick my sessions for tomorrow

I think I would have gone to either Thomas LaRock or Chrissy Lemaire and then Klaus, but I'll be missing those sessions

ypercubeᵀᴹ

@Marian ha, I was just reading this one

16

Q: What was the purpose of the quote "Meat is back on the menu boys" in Lord of the Rings?

I watched the extended cut of the movie and can’t seem to figure out why the director put that quote in there. Is this a hidden joke or a slip up that made it into the movie?

Marian

9:19 AM

@ypercubeᵀᴹ nice

saw that movie series 3 times :)

Tom V

9:31 AM

@PaulWhite you around?

dezso

9:42 AM

apparently, you were wrong

Andriy M

@dezso Are those uppercase chipmunks?

2

dezso

@AndriyM absolutely

Andriy M

Look more like uppest case to me.

Tom V

Or nut case

5

Paul White

@TomV Yes

dezso

9:55 AM

@TomV now I am confused, I might have created the doper-chipmunk table

Tom V

@PaulWhite I can't figure out how to get a chat room for this question. I flagged it

1

Q: Help me tune the long-running query

I have a problem with the following query executing from the application (Microsoft Dynamics AX): DECLARE @p1 INT; SET @p1 = NULL; DECLARE @p2 INT; SET @p2 = 0; DECLARE @p5 INT; SET @p5 = 2 + 4096; DECLARE @p6 INT; SET @p6 = 8193; DECLARE @p7 INT; SET @p7 = 0; EXEC sp_cursorprepexec @p1 OUT...

sql-server sql sql-server-2008-r2 performance microsoft-dynamics

Paul White

@TomV Just handled :) Thanks

dezso

> mariadb jdbc ResultSet.getFloat(xxx) function return error if datatype of column is a varchar: this a big problem.

yeah, sane behaviour is a problem, I see

Paul White

Definitely a problem there somewhere

sp_BlitzErik

@PaulWhite c'mon man, i need someone to make fun of farley's accent with

@MikaelEriksson i finally get to meet my XML dad!

Paul White

10:09 AM

@sp_BlitzErik Just don't make fun of his brand new bright red sports car with RF initials

Midlife crisis

A midlife crisis is a transition of identity and self-confidence that can occur in middle aged individuals. It is a psychological and behavioral observation that commonly occurs with individuals between the ages 45–64. Its observations differ in a diverse manner for each individual. While some individuals may experience feelings of depression, remorse, and anxiety, others may experience feelings such as the desire to achieve youthfulness or make drastic changes to their current lifestyle or atmosphere. == Crisis vs. stressors == Academic research since the 1980s rejects the notion of mid-...

sp_BlitzErik

OH HE DIDN'T

Paul White

@sp_BlitzErik Check his FB if you are able

sp_BlitzErik

@PaulWhite hahaha, this sounds like precisely the reason i don't have facebook

Paul White

There are certainly many reasons available to choose from

sp_BlitzErik

this unbalanced parallelism question reminded me of your pass talk where you fixed it changing maxdop to an odd number dba.stackexchange.com/questions/174249/…

or fixed a similar problem, if the cobwebs aren't too thick

Paul White

10:22 AM

@sp_BlitzErik I'm glad you remembered it. I would share Rob's post and car picture but it's not public.

The issue in that question is simply the join cardinality estimation. The unbalance comes from hash partitioning and duplicates as Joe mentions in his answer.

Anonymized plans are all very well but so often the devil is in the obfuscated details e.g. join predicates in this case.

I should probably write something down in an answer to complement his.

sp_BlitzErik

@PaulWhite i usually bail on anonymized plans. i get why people do it, but the naming convention used is incredibly confusing when you want to give someone quick advice.

Paul White

Yes it puts me off too. I have never truly understood how object names could be quite so sensitive.

Unless the column name is SecretlyUploadToCIAflag or something.

sp_BlitzErik

it would be nice if you could choose a level of anonymization. like object only or code only or parameters only.

i did run into one case where a client had named their filtered indexes after the client they were filtering for.

which is the most sensible thing they had done.

2

Paul White

10:47 AM

I often take the view that if the data is at all sensitive (requiring anonymization) it is simply not suitable for free help and requires money and contracts.

4

sp_BlitzErik

i'll drink to that

Paul White

Well there's a rarity.

maybe at 7am ;)

:)

@PaulWhite True.

11:00 AM

whatever happened to craig freedman? his blog always seems like the reference point for a lot of smart people's knowledge

Paul White

He's still around. I don't recall what he's currently working on. Probably CosmosDB :)

sp_BlitzErik

11:28 AM

hahahaha

sparse little blogging about that one~

sp_BlitzErik

11:55 AM

i don't believe that 25% of these people have query store enabled in prod: twitter.com/BrentO/status/866705129210249217

Michael Green

12:28 PM

Damn you Query Optimizer!!!
Been refactoring and am now getting "Arithmetic overflow error converting numeric to data type numeric." If I pass in 99.95 it fails. If I pass 99.9499 it works. I guess I know what I'll be doing tomorrow. <sigh/>

@dezso It's not quoted, it's caged. I don't want feral squirrels running around in my shiny new SSD.

2

dezso

@MichaelGreen ah

security first

Joe Obbish

1:36 PM

@PaulWhite Fix? There's nothing to fix! All default avatars are unique and beautiful

With that said if you go to pass I will change my avatar

you can even take the picture if you like

@PaulWhite Same issue here. It's corporate policy and I can't do anything about it

That's why some of my questions have silly names for tables

sp_BlitzErik

i find your hand drawn circles on screen shots delightful

shape tools be damned

Joe Obbish

it's art!

Tom V

That'll teach me not to ask for a chat room again

Joe Obbish

1:51 PM

tools are too perfect and impersonal

did you see the one where I drew a bunch of letters by hand?

sp_BlitzErik

hahaha yes

it reminded me of those old 'simon' skits on snl

McNets

0

Q: Different order for columns in Index and Prim Key

I figured out that I can have Unique Index and Prim Key with same name, same columns but with different order of columns. In such case, how Prim Key is using that index? Thanks.

index order-by

It depends...

sp_BlitzErik

howdy whody what

Tom V

@sp_BlitzErik I wanted to comment after voting to close, but I have no idea what to ask for

Joe Obbish

there's a real buffet of choices

sp_BlitzErik

1:56 PM

@TomV i'm stumped on the 'same name' part -- but holy cow, that's a swamp

Paul White

2:52 PM

@TomV Unfortunately doing the right thing is no guarantee of success

Paul White

3:14 PM

Hm actually it seems it is :-/

sp_BlitzErik

@PaulWhite how do you feel about adaptive joins so far?

Paul White

@sp_BlitzErik Haven't played with them at all, just read about them. Seems like it'll be great eventually. Lots of restrictions right now.

How do you feel about them?

sp_BlitzErik

i'd be happy to never have to talk about parameter sniffing again, but it seems like that's a long ways off

most servers i see are nowhere near 2016, high 2017 adoption rates are going to suck for years

i was writing xquery into blitzcache to detect them and realized i had no idea what to tell people if they were found

"you... columnstore... future.... yay?"

Joe Obbish

3:43 PM

I really don't get it

right now there's only a switch from hash to loop, right?

Lamak

right

Joe Obbish

for the really terrible queries we get what I need is a switch from loop to hash

Lamak

(I don't actually know if that's right)

sp_BlitzErik

it can choose either, not just one way

Joe Obbish

that's more reasonable then, guess I read it wrong

I suppose I'll have to actually use the feature before I can complain about it

sp_BlitzErik

3:45 PM

there's a row threshold based on [magic], and depending on which side the number of rows ends up on, the type of join is chosen

Forrest

And it's for batch mode only still

Paul White

@JoeObbish where's the fun in that

Joe Obbish

speaking of fun, apparently the MIN_GRANT_PERCENT hint doesn't apply to memory grants for the insert into a CCI

that's actually pretty funny

had a rowgroup trimmed due to MEMORY_LIMITATION

was inserting 1 M rows

so I forced the query to have a 7 GB memory grant

no effect

in fact I get a warning that there was an excessive memory grant

sp_BlitzErik

what's 7 GB of memory between friends?

if you asked to borrow 7 GB of RAM from me, i wouldn't call it excessive

Joe Obbish

I think that I can still win

I won by inserting NULLs into columns

sp_BlitzErik

3:59 PM

Joe Obbish

dbfiddle.uk/…

sp_BlitzErik

4:19 PM

is there a reason you ditched the order by in the second insert?

i don't know anything magical here -- just curious

Joe Obbish

Without the ORDER BY the MIN_GRANT_PERCENT hint is ignored because a memory grant isn't "needed"

so because I didn't need MIN_GRANT_PERCENT for the second query I ditched the ORDER BY

MDCCL

@PaulWhite Should the following question be considered a duplicate?

1

Q: Best Practice - Database design for 'conditional join'

I need to create a database to store Jobs. Each Job can have 1-* Step Each step is classified as one of either Type A, B or C. Each Type has specific details and other tables that join on them so putting them into the Step table is not an option. This is depicted in the diagram below; In this...

database-design join normalization best-practices subtypes

sp_BlitzErik

cool, thanks.

MDCCL

@PaulWhite Although the solution to both questions is employing a similar structure, the scenario shown in the tentative duplicate is different. Let me know what you think, thank you.

Tom V

@PaulWhite depends on your definition of success I suppose

MDCCL

4:28 PM

@TomV Do you think that the Q I linked to above should be closed as a duplicate? (It was closed by community)

BradC

15

Q: SQL Server 2012: Generate Scripts from command line

I am running SQL Server 2012. The SQL Server Management Studio has the option to right click on a database then select Tasks and Generate Scripts. Is there a way to automate that via command line somehow? I want to create a script that includes the schema and data of the entire data base. Too...

sql-server

So I wanted to bring up an edit I rejected yesterday, and what I found when I did a little more research. Here's the question is was on:

And here was the attempted edit by a new user:

dba.stackexchange.com/review/suggested-edits/92979

They pretty clearly hijacked the top (accepted) answer and replaced it with their own content, so I rejected the answer.

After looking into it a bit more, the user appears to be a Microsoft project manager who is part of a team working on a product for generating scripts from the command line:

blogs.technet.microsoft.com/dataplatforminsider/2017/05/17/…

Tom V

@MDCCL if it's closed by community I think that means the OP agrees

BradC

So I added an answer of my own, pointing to that blog.

After further thought, I think this was new user ignorance, not malicious. The post is locked to answers from new users, so she couldn't add a new answer the proper way

I don't believe any further action is warranted at the moment, but just wanted to mention it if we see any further activity from this user

MDCCL

@TomV So someone flagged it and the OP accepted or something like that? It's a bit strange to me.

No big deal, but I don't find the issue particularly clear

Paul White

4:54 PM

@BradC I can't see any reason for that question to be protected so I unprotected it.

@MDCCL Ordinary vote to close as duplicate accepted by the OP. On phone now I'll have a proper look later or someone else will

@TomV Accepted answer

Andriy M

@BradC The user may be involved in the project. The userpic seems to suggest she's one of the contributors (taken from here; increased version).

Not sure how much it matters if the project is free but usually one is expected to disclose their affiliation, involvement etc., which she didn't in her edit.

Joe Obbish

@PaulWhite Yet another benefit of the default avatar

MDCCL

@PaulWhite Thanks

Andriy M

Again, though, she probably did out of ignorance rather than malice.

ypercubeᵀᴹ

@MDCCL also:

@MDCCL This is exactly (and then some) the type of information i'm looking for. Thank you. — Intern87 1 hour ago

Tom V

5:06 PM

@PaulWhite yeah but he stopped responding in chat and abandoned his previous question which also posted incorrect plans/queries

I was attempting to school him so he posts correct information next time but I don't think that worked

BradC

@AndriyM Yes, her byline is on that blog post as well: blogs.technet.microsoft.com/dataplatforminsider/2017/05/17/…

For what its worth, her Stack Overflow profile mentions her affiliation: stackoverflow.com/users/7448685/tara-raj

Andriy M

@BradC Ah, I didn't look at that one. So she's from SQL Server Team then – well, maybe that somewhat changes the situation.

Didn't look at the info in the linked profile either.

BradC

@AndriyM Well, it doesn't exempt her from following dba.SE rules, but it does mean that her newly completed tool is worth mentioning, if it works as promised

Especially as an answer to "is there a command line equivalent to the script feature in SSMS". Yes, here is a new command line tool, created and supported by MS, designed explicitly to be equivalent to the script feature in SSMS

Tom V

I suppose I overestimated the OP

Andriy M

@BradC One other point regarding your answer that I meant to raise is, do you think it would be fair to mention her as someone whose idea it actually was to suggest the tool in the first place?

2

MDCCL

5:20 PM

@ypercubeᵀᴹ I think it's the type of information they're looking for, but it's not "exactly" so, though I get the idea. Anyway, there's no big deal, as I commented above.

BradC

@AndriyM Perhaps. By name? By dba.SE profile link?

the blog already says: "This post was authored by Tara Raj and Vinson Yu, Program Managers – SQL Server Team"

Andriy M

By her DBA.SE profile link, I think. She did try to post her answer, and only failed to do so because of the protected status the question had at the time.

BradC

@AndriyM That's probably fair. I'll add that to my answer

Andriy M

@BradC Great, cheers

Max Vernon

6:11 PM

124

A: How to exit the Vim editor?

To the best of my knowledge, vim is unexitable. Buying a new computer solved the issue for me.

Tom V

6:22 PM

@MaxVernon Ha Ha, but no fun allowed

Oh @swasheck welcome back, missed you

swasheck

thanks

i've missed (some of) you too

Andriy M

Slacking does that to you sometimes.

swasheck

6:44 PM

so i've heard

ypercubeᵀᴹ

7:04 PM

It happened today. The first stable mysql version (MariaDB 10.2.6) with Window functions, (recursive) CTEs and CHECK constraints - that work.

4

sp_BlitzErik

@ypercubeᵀᴹ i can almost stop making fun of mysql

swasheck

7:25 PM

@sp_BlitzErik ... almost

Max Vernon

I'd like to provide an answer to this question - however it needs a couple more reopen votes. My answer would be for SQL Server, although I think it would be generic enough to help future visitors even if the OP never comes back.

sp_BlitzErik

yeahhhh but i probably won't

@MaxVernon full disclosure: you have my vote

Max Vernon

@sp_BlitzErik LOL. Thanks Erik!

@sp_BlitzErik it's what they call "low hanging fruit"

McNets

Do it

swasheck

@sp_BlitzErik it has a tough reputation to overcome. the first eventually consistent acid-compliant database on the market!!

sp_BlitzErik

7:32 PM

@sw

dammit

@swasheck i feel bad because a lot of developers who are mad about sql server/oracle licensing costs think mysql is going to save their lives

swasheck

when in fact, postgres would save their lives ;)

(also, you can edit chat messages)

sp_BlitzErik

yeah i'm lazy

McNets

someone has propegate my edit

sp_BlitzErik

i try to point those people towards postgres as much as possible

Max Vernon

8:04 PM

so I'm not sure if it's a really helpful answer, but there it is

swasheck

@MaxVernon that's so deceptively canadian of you. essentially what you said was, "sorry if this isnt helpful, but ..."

Max Vernon

@swasheck I'm sorry if my comment was too vague and indirect ;-)

swasheck

that's more like it

dezso

@sp_BlitzErik very nice of you

McNets

@dezso is there any 'big' ERP working on Postgres?

Joe Obbish

8:22 PM

That question still doesn't make sense?

Andriy M

Not to me, no

I do have a guess, though, that he may be confused about how indices work, in the sense that he may be thinking that an index name has anything to do with whether the index is used or not, or how it's used.

Max Vernon

@JoeObbish perhaps I'm reading into it? It seems to me like the OP is asking what happens to a table when you have a primary key and a clustered index that are different; specifically when they are different only in the order of the key columns?

At least, that's what I thought when I read the question.

the bit about the names of the indexes being the same must be incorrect.

McNets

Perhaps it isn't the real question.

@ypercubeᵀᴹ Would you help me ? — Rafael Calino 52 mins ago

ypercubeᵀᴹ

weird edit: dba.stackexchange.com/posts/148238/revisions

(rolled back now)

Joe Obbish

8:38 PM

@ypercubeᵀᴹ Looks like he felt a need to anonymize the column and table names?

ypercubeᵀᴹ

yeah, a year later ...

dezso

@McNets Emerson, Rake and Palmer?

swasheck

@McNets i'll repeat myself from a few weeks ago ... are there any ERP tools that "work"

3

dezso

@swasheck yes, that's the real question. I don't know any, but then I also don't really know any that don't work.

Andriy M

@dezso In short, you don't know any.

Any Emerson, Rake or Palmer

dezso

8:59 PM

@AndriyM or Emelson, Rake and Parmel

I always confuse these two

McNets

swasheck

welcome back my friends to the show that never ends

dezso

@swasheck I was introduced to ERP's music through The Barbarian (obvious Hungarian connection)

and then obtained a CD of Pictures at an Exhibition, but cannot remember how and from whom

in a family that did not listen to any rock music, at least not on purpose

the rebel I was

McNets

I have this one (vinyl).

dezso

and in the end I tricked myself into using the wrong acronym. Now I will have to pretend it was an intentional joke.

McNets

9:30 PM

Does this question remain opened?

4

Q: SSIS Error: To run a SSIS package outside of SQL Server Data Tools you must install Standard Edition (64-bit) of Integration Services or higher

SQL Server 2016 SP1 Enterprise Edition Data Engine (with SQL Agent) installed on an FCI called inst1. SSIS 2016 SP1 Enterprise Edition installed on comp2. (comp2 is not in the inst1 cluster) Filesystem SSIS packages on comp2. SQL Agent job using the SSIS step type on inst1 calls the package stor...

sql-server-2016 sql-server-agent ssis-2016

McNets

9:46 PM

Does = Should

dezso

@McNets got one VtC

interestingly, it got a number of upvotes beforehand

McNets

@dezso Initially I thought it was using excel files, as far as I know it requires 32 bits odbc drivers.

dezso

@McNets well, that is like the ERPs: I don't know much about them. However, the self-answer clearly shows (to me, at least) that the question is too localized.

McNets

10:03 PM

@dezso yes, I VtC too.

Transcript for

The Heap™ – Consultancy ©®