The Heap™ – Consultancy ©® - 2015-03-05 (page 1 of 2)

« first day (1521 days earlier) ← previous day next day → last day (3347 days later) »

12:52 AM

@bluefeet - that optional second table question -- Necreaux has the right idea. If the @parameter is in the join condition the second table will not be accessed at runtime (Acutual Number of Rows = 0) even though the node is in the plan. If added a comment to his answer to that effect.

10 hours later…

10:24 AM

Man, some questions just make you go "Why are you trying to use a database for this?"

2 hours later…

12:24 PM

@JackDouglas are you going to the SQL thing?

They're turning away Oracle people at the door

PostgreSQL people are allowed, but only after additional security screening

2

12:52 PM

This is interesting. I wonder if it's a real bug or just a misunderstanding: social.msdn.microsoft.com/Forums/sqlserver/en-US/…

1:04 PM

@swasheck let us know if you want some help, I like stuff like this

though I am arguably much less qualified than a majority of people in the room :)

1:19 PM

@ypercube not unless they want me to cough on them :S

1:31 PM

oh man we have some terrible inline sql in our legacy web pages

I just saw one that was written in this style:

SELECT ColA FROM TableA Where BId = (SELECT BId FROM TableB WHERE CId = (SELECT DId FROM TableD WHERE EId = (SELECT EId FROM TableE WHERE SomeColumn = @Param))))

@JNK yuck

Yeah I don't even know

Looks like something written by a person who read a blog post 10 years ago about how SQL is slow because of JOINs

I'll try to post one I found yesterday. I'll just have to make the code a bit anonymous first

1:54 PM

@jnk do you have plan explorer?

@JNK see ^^that

the WHERE IN uses a view with all transactions....ever

twice

I tried to put it in a sql fiddle but it's not working

I'll delete it shortly

@swasheck yeha man

@JNK Looks like something I would have written when I first started with no guidance...

@bluefeet wtf

Box.com available to you at work?

2:03 PM

I'm at home today

and looks like it

@JNK it's a terrible proc with so many issues

@bluefeet all mine are like that

Our most commonly executed web service has a 1200 line stored procedure at its center

and this thing has a swiss army knife of options which make it super ineffecient

ugh

and NONE of which the web service uses when it calls it

the WS calls it the same way except for like 2 params every time

this thing is run 800k times a week and takes like 600ms per run now

also how is it still frigging snowing

@bluefeet I guess you could combine the 2 parts of the UNION into 1 ?

Seem identical, except for the cross apply

2:13 PM

@ypercube We've got a few different things we are going to do to it.

but consolidating those bits of code will be part of it

@JNK app.box.com/s/j9a7fpslmyv7cjcim6s2b0hjjuaxkjhd

also known as: "why i wish @jnk would just work in the city with my company."

lol

it's opening

did you actually time them?

or are you using the cost %s to determine which is fastest?

because the cost %s are a lie and the "most expensive" one actually looks to be fastest even though all your row counts are messed up

2:31 PM

These are actual plans and I used the duration and plan shape

ok

so the last one is the fastest

but this is frigging bizarre

it actually runs in 250ms but the row estimates are all super duper off

Like theres one in the last query where the estimate is 6 billion rows and actual is 0

ohhhhhhh is that table paritioned?

3:10 PM

sorry. tables are partitioned. stats are fresh but sample rates of .01% given the size of the tables

@JNK last one is heavily hinted ( with (forceseek) ... option (loop join ...))

@JNK i think that the big question is, "why does my no-cte rewrite (just a straight find/replace) have a completely different shape?"

@dezso are you around?

@swasheck I'm actually not sure

I'm wondering if the CTE forcing SELECT * affects things

yeah. it's a mystery. i would invoke the @PaulWhite summons, but i already owe him one beer (at least)

Kris Gruttemeyer

3:28 PM

Shot in the dark here, anyone have experience replicating from iSeries/AS400 to SQL?

where dark = 'black hole'

Kris Gruttemeyer

@swasheck Amen to that. Didnt think anyone really did, but cant hurt to ask

ConcernedOfTunbridgeWells

@KrisGruttemeyer IBM Infosphere CDC will do this sort of thing - for a price.

Kris Gruttemeyer

@ConcernedOfTunbridgeWells It's gotten so bad with our current replication provider that money may become no object

Replciations failing, falling out of sync, the management application freezing, crashing or not displaying current information

ConcernedOfTunbridgeWells

@KrisGruttemeyer Like most of this sort of IBM kit it's enterprisey and sphincter-puckeringly expensive. However it should probably work.

Alternatively, if you don't need low latency, you could just build an ETL process to pull the data you need across.

Kris Gruttemeyer

3:32 PM

We need <30 seconds difference

thats our threshold

ConcernedOfTunbridgeWells

@KrisGruttemeyer OK, you will need CDC or some other low latency solution.

Kris Gruttemeyer

This seems fitting: dbareactions.com/post/112636067927/…

@ConcernedOfTunbridgeWells Any homegrown solutions or all they all third party?

ConcernedOfTunbridgeWells

@KrisGruttemeyer I don't think that either DB2/400 or SQL Server comes with a replication tool that will work with the other out of the box.

Kris Gruttemeyer

@ConcernedOfTunbridgeWells Yeah, I didn't think so. Our current on is complete garbage. We just got an iSeries DBA finally so we are working together on getting a new solution on the table. I seriously was told by our current software vendor that our error 'may just not happen again'...

ConcernedOfTunbridgeWells

@KrisGruttemeyer Talk to IBM about solutions and pricing. I recommend a good, silicone based lube.

4

Kris Gruttemeyer

3:41 PM

@ConcernedOfTunbridgeWells I'm sure we will be walking funny for a while after they see the price tag. Thanks for the advice, im tired of barely sleeping because the replicator keeps borking itself.

ConcernedOfTunbridgeWells

@KrisGruttemeyer I think they also have another replication solution ibm.com/developerworks/data/roadmaps/qrepl-roadmap.html

Kris Gruttemeyer

@ConcernedOfTunbridgeWells Just forwarded that to the iSeries DBA.

ConcernedOfTunbridgeWells

@KrisGruttemeyer The link or the recommendation?

Kris Gruttemeyer

@ConcernedOfTunbridgeWells Yes. Ha.

@ConcernedOfTunbridgeWells that's standard IBM fare

ConcernedOfTunbridgeWells

3:51 PM

@swasheck Yeah, but they don't actually supply the lube. That's extra. FRU#4055X69 $173.99/100ml

Don't forget VAT

i'll maintain that it's still better than oracle, who'd charge you for the privilege of bending you over in the first place

ConcernedOfTunbridgeWells

I think you need a metalink or OTN subscription to get the part from Oracle.

twice the pain, quadruple the cost!

> I have a hard time understanding the purpose of a gritty movie based on a line of toys with characters named Mechaneck, Stinkor and Fisto. My brother and I had a ton of He-Man toys when we were kids (30+ years ago!) and I'm absolutely stymied as to who the audience for this is supposed to be, and who figures that audience is large enough to make back the money on even a mid-budget movie.

> Watch out when Stinkor and Fisto combine.

4:15 PM

user image

Well allrighty then

Jonathan, I'm gonna kill you...

No, no, no. He's just laying down the Spock eyebrow

Kris Gruttemeyer

This is all I can think of now

user image

wow. kehayias is actually a feely kinda guy

4:24 PM

Yay, I get to write an unpivot at work... Boo, it took me a bit to realize I needed to use that to resolve this.

@bluefeet was it confusing because the problem was stated clearly?

@JNK it was confusing because I've never really needed to use it in the real world. :)

@ypercube now I am

@dezso Thnx. just fixed the issue (in postgres)

took me like 30 minutes to realize how silly I was

Aaron looks distinctly unimpressed...

4:29 PM

@ypercube that's relatively fast, it takes usually longer for me

I need more practice in the cli.

I always have to llok for what command does what

all these abbreviations do not help really

you mean psql?

Now I have to fix another issue. Good that it's my local machine.

I managed to mangle the installation somehow and psql cannot connect to the database

I get a Error: No existing local cluster is suitable as a default target. Please see man pg_wrapper(1) how to specify one.

So this is weird. I've got a sproc for DBCC checks that creates snapshots to be used (so I don't rely on the default snapshot). I programmed in a drop of those snapshots, which SQL Server doesn't like (DROP DATABASE statement cannot be used inside a user transaction). Yet, even with the error, it still appears to carry out the drop snapshot.

Anyone seen that before?

weird, one of our dev servers suddenly has a DB in "Suspect" state

4:42 PM

@MikeFal I have seen DDL inside a trans throw an error and still execute

I cant recall the context though, it's been a while

@ypercube is the cluster running at all?

yes

I had version 9.1, then added 9.3 (2 instances)

All was well

@MikeFal also it sounds like you're doing the create and drop in the same STP/transaction?

@JNK Yeah. Not explicitly declaring transactions. Maybe I should?

and I managed to uninstall several programs. Now the 9.1 is not running

and I connect only with psql --cluster 9.3/localhost:5433

4:46 PM

which psql version is it?

and/or which OS?

Ubuntu.

How do I find the version?

psql --version

this --cluster option is unknown to me

That gives the same error:

> Error: No existing local cluster is suitable as a default target. Please see man pg_wrapper(1) how to specify one.

locate psql hows that it's probably a 9.3 version

and if you issue which psql ?

Ah, wait.

/usr/bin/psql gives the above error about cluster not found and pg_wrapper

4:52 PM

I guess it's only a link to somewhere

@MikeFal if theres no declared trans then it should be fine, but I think some of those commands expect batch terminators

like you can't add a column without a GO and then index the column

b/c the create index will not see the column without a batch separator

@JNK Ah, ok. Lemme try that.

well you can't put them in a SETP

STP

without dynsql

STP? I'm having the dumb.

stored proc

you can't put a GO inside a stored proc b/c the GO will terminate the definition of the stored proc :)

so you gotta use dynsql in an inner scope

5:02 PM

@dezso Yes, it was. I fixed it. Thnx

roger.

I'm already doing the delete within a sproc with dyn sql. Declare the string, execute it with sp_executesql

5:17 PM

I know this is a sad story but the phrase "Knife shower of Justice" still made me laugh....

bbc.com/news/world-asia-31743055

@Zane sick sense of humor ;P

5:41 PM

nelnet can go to hell and burn painfully and slowly while it's there

@swasheck I'll be paying them back for another 20 years

@bluefeet find someone else

(if you can)

@swasheck @AaronBertrand always on his phone...

5:58 PM

@swasheck Why?

I have some student loans left, but the interest rate is less than inflation...

@JamesLupolt they miscalculated interest so i misreported interest.

@swasheck Oh. And now the IRS wants money?

taxes submitted

irs wants $$

and now is threatening future audits because of it

Fun times.

so they've effed me not just this year but for all future years too

6:00 PM

Just declare you've seceded from the union

went well the first time

@swasheck the IRS is the most messed up organization in our government. It's the only place that you have to prove your innocence.

6:19 PM

@Zane that's also increasingly descriptive of our law enforcement

Sadly yes that is the case.

@swasheck if you have evidence that the interest was misreported to you it should be fine once you settle the balance

It's one of those scenarios where government power is increasing and an exponential rate. Giving up more and more liberties but standard of living is so high that people just don't care.

I'm assuming you have whatever the form is

@JNK yeha. it just sucks that the refund has already been paid ... now i have to go through the rest of that rigamarole.

oh well. check this out. i think my comcast rep is hitting on me

> Seth_: thanks so much for the advice

OYO: You are most welcome!

OYO: You know what, I am so amazed with the results.

OYO: 100 packets transmitted, 100 packets received, 0% packet loss

OYO: This means that your modem is at its powerful and strongest state.

Seth_: great

OYO: No, Seth you are great!

6:23 PM

yikes

@swasheck Oh seth your modem is sooo powerful.

This must be their latest ploy to get away with the crappy method that they treat their customers.

The rep's name is OYO?

Simply flatter the customer.

i guess

@Zane yeah. nobody sees through that /sarcasm

It reminds me of that old bit in the Pokemon South Park episode.

6:55 PM

@Zane i'm not familiar

7:07 PM

if ya havin' IO problems i feel bad for ya son
i got 99 problems but a disk ain't one

7:26 PM

Billboard TOP 5 Mitzvah Invitation ft. Brody Criz

i feel like i'm in here talking to myself

I'm too afraid to click that link

The still looks like it's a creature from The Dark Crystal

worth the click

what in the world

I admire his commitment

can't stop laughing

@billinkc i envision you as this kid

7:39 PM

sql fiddle you are broken far too often lately

@swasheck I was chubby enough but no where near cool/clever enough

side note, the fact that both of these answers including the accepted have a syntax error is a real shame - stackoverflow.com/questions/28885656/inner-join-syntax-error

@bluefeet the missing alias?

@ypercube yes

sloppy answers

@ypercube @bluefeet there's also a space between orders and .*

7:43 PM

but that's not an error

@ypercube that'll parse?

sure

select orders .* from orders?

select
t
.
*
from
t
;

whitespace

@ypercube but that's not whitespace

7:45 PM

I'm pretty sure it parses in sql-server, too

holy crap

that's stupid

why?

because i said so

this is confusing and hurts my brain

declare @test table (
	id int,
	name sysname);

select t .*
from @test as t;

Because it hurts my brain.

Oh... Not quick enough on the draw aparrently.

@swasheck I hate you

7:47 PM

@billinkc i hate myself for that

I want to punch a kitten with another kitten for knowing that is valid]\

and mash additional keys when I hit return

RAGE QUERY!!!!!!!!!!!!!!

@Zane but great (hurt) minds think alike!

It won't run for me....

now i'm going to go fsck with my developers and refactor their code with random (but valid) whitespace

declare @test table (
	id int,
	name sysname);

select t .* you better not run this damn query you stupid thing
from @test as t;

Didn't work.

7:50 PM

Oh Zane, this is why I was loving LICEcap earlier.

0

A: How to use selected SQL statement in SSIS package as source variable?

This is the basic Shred Recordset pattern. I have 3 variables declared: SourceQuery, CurrentQuery and rsQueryData. The first 2 are Strings, the last is an Object type SQL - Get source data This is my query. It simulates your table and induces a failing SQL Statement if I take out the filter....

@Zane you need to quote your identifier

(except it's .*)

Essentially it didn't work because I didn't want it to.

gotcha

@billinkc I did it first.

3

Q: Can't use Fuzzy Lookup in BIDS

A coworker of mine was having an issue with BIDS. He's doing a simple fuzzy lookup and is only looking against two rows(For Testing Purposes). The weird thing is that when he tries to execute an command line prompt appears then vanishes and then the package hangs. So I had him send me the packag...

@billinkc Good answer though.

Based on their comment, I'm guessing it's going to be a shitty chameleon question

Is that fuzzy lookup question still valid?

7:55 PM

ironic that their username is "ahacode"

since there's yet to be an "aha!"

@billinkc probably.

Pretty sure it's our borked sql install.

I figured it was their name and was more like an order. "Aha, code now!"

I've used it in a few internal blogs as well.

engadget.com/2015/03/04/lockheed-martin-laser-athena-test

Huh, you can now truncate a partition with SQL Azure msdn.microsoft.com/en-us/library/ms177570.aspx

7:59 PM

that's frickin' awesome just because ... still trying to ascertain the utility

I think you can only empty partitions at the beginning/end with partition swapping so another mechanism for blowing away vast amounts of data

no. i've swapped partitions out when they're not the bread of the sammich

Well then ummm unaligned LOOK, A SQUIRREL!

Noooooo! MGM is remaking the Secret of Nimh...

Ohhh a sparklie!

8:08 PM

@Zane so much wtf from hollywood these days

Also just found this nvarchar(1)

Someone needs to remake Watership Down so I can remember my children's faces when they learned what nightmares were

"The new film, which is set to be a mixture of live-action and CGI, will be written by Ice Age 5 scribe Michael Berg, who reportedly plans to focus on the origins of the Rats of NIMH"

"describing it instead as a tale of an “imperiled mouse protagonist” who “befriends a comical crew of lab rats as they turn hyper-intelligent.

I think you may have missed the point of this film sir.

> MGM Wants To Smurf-itize ‘The Secret of NIMH’

@billinkc It would probably about a bunch of bunnies who befriend the farmers dog in an attempt to steal cookies or some bs.

8:15 PM

user image

Are there literally no more ideas?

It would be one thing if this were some sort of new creative imagining of this idea but to hear it being shat out by the writer of Ice age 5 as a generic kids movie makes me sad.

I blame the Greeks. They wrote all the stuff down and ever since then, it's just be a rehash.

holy crap - komonews.com/home/video/…

I'm just so happy they had their phone oriented in the proper direction

you shouldn't have cthulhu's children captive

8:31 PM

poor pus, it just wanted to be free

8:47 PM

I don't think it realized the sheer volume of not water that was on the other side of glass there.

The door and the harbor is not terribly far away from anywhere in the aquarium

We should prevent users with no rep from commenting and force them to add all their comments to their question. Or something. Maybe jumper cables to their nips instead

You really need to click the Edit button on your question and give a full accounting of what you're trying to do. If nothing else, there is rich formatting available to you where you can accurately describe your problem, what you have done to solve it, where it is failing, how it is failing and what the desired end state is. — billinkc 11 mins ago

@billinkc bail.

Yeah, gonna pull the dupehammer on it soonish

Oh, I need to get Madison travel and lodging taken care of. Zane, you want to crash with me again or did you say you were bringing people down with you?

I am.

Not that I didn't enjoy the sex dungeon vibe to you bring to a hotel. :)

I do what I can

Anyone else want to shack up with me?

8:56 PM

eeeew

I bought a new car and so my friends were looking to go on a road trip. This is the best time for me to go and if I bring a few people then they can entertain themselves when I'm doing SQL Sat stuff.

@Lamak no kidding

I'm super excited.

Last time I didn't really know many of the SQL peeps.

i felt much more comfortable as i went to more events

you guys are making me jealous

9:02 PM

Wear a kilt. You'll be so focused on feeling uncomfortable in that, you'll forget to be uncomfortable with all the strangers

I leave such lovely things in my code.

@Lamak SQL Sabado Gigante Chile

2

funny

teleMUNdo

@billinkc have you been practicing your jokes?

9:04 PM

@Lamak every night in the mirror. that's what i heard

user image

@swasheck that poor poor mirror

My actual error handling is super verbose and most of the names are usually functions so I tend to leave placeholders that might make the review team laugh.

@Lamak We can skype if you'd prefer to hear them live

ah, didn't know you were into torturing people, there was some truth on the dungeon thing apparently

9:07 PM

50 shades of fried chicken

wow

9:29 PM

user image

"fist them all"

Heh

The Shut up, it is a real telescope is the one that I keep laughing about

This is probably either a misunderstanding or something that should be a Connect item, but I thought I would ask here first:

0

Q: Is sys.stats_columns incorrect?

Let's say I have a table Foo with columns ID1, ID2 and a composite primary key defined over ID2, ID1. (I'm currently working with a System Center product that has several tables defined this way with the primary key columns listed in the opposite order they appear in the table definition.) CREAT...

sql-server statistics

@JamesLupolt i asked that question and then deleted it

almost exactly

it's a misunderstanding

9:32 PM

@swasheck Can you elaborate

Or answer it for unicorn points

@JamesLupolt i suppose i can

i wish i could find my deleted question, though

@swasheck if you go dba.stackexchange.com/users/4490/swasheck?tab=questions, then at the bottom there is a link for recent deleted questions - it may or may not have the link there

Or you could ping a mod and ask

@bluefeet wasnt recent. it was, like ... february

It looks like I'm the third person to get this wrong:

social.msdn.microsoft.com/Forums/sqlserver/en-US/…

9:36 PM

I don't recall the timeframe on that page

1

Q: stats_column_id and index_column_id do not update with physical order of clustered index is changed

Unless I'm misunderstanding the purpose of the column, the following code indicates that a change of the structure of the clustered index does not change the ordinal position (stats_column_id)of the column in the sys.stats_columns DMV. (Tested in AdventureWorks2014, AdventureWorks2008R2) select ...

sql-server statistics dmv

@swasheck 60 days

Kris Gruttemeyer

"though i vaguely recall someone noting that those helper functions may not be the best idea"

@JamesLupolt it appears as though it maintains cluster order. since you're checking multi-column stats behind an index i'd stick with index order

@KrisGruttemeyer probably a crack whore... They're not to be trusted with your wallet or best practices

9:40 PM

@KrisGruttemeyer was a thinly-veiled joke toward @AaronBertrand

Kris Gruttemeyer

@billinkc HA! I went through a whole song and dance about 2 months ago with those helper functions, I think Aaron ended up including it in that article as a case example.

thought I had 2 SPs that were still executing when I know they were zz'ed. Turns out, I didnt have AND t.dbid = DBID()

and object ids arent unique across DBs.

it was actually a really good learning experience

@JamesLupolt tell me how you're wrong

also ... WTF is the order of interactions on those microsoft forums? it's like they're thrown in there in standard entropic order

@swasheck accepted stuff goes to the top

regardless of timing

I think BELOW the accepted or "right" answer everything is in order

@JamesLupolt have you had any luck finding anything on Connect?

@JNK ugh.

thanks for the help

@swasheck I'm still not sure that I'm wrong, but it seems hard to believe that a bug that obvious wouldn't be widely known after almost 10 years.

I couldn't find anything related on Connect. Will try again now

9:55 PM

@JamesLupolt i dont think you are. i think that connect would be the best way to find out. i may submit feedback

and let you vote on it

;)

@swasheck Cheers

I agree with the workaround, though

Just rely on key_ordinal in sys.index_columns for stats on indexes

In Connect is Incorrect with lazier r's.

@JNK is this a language?

@JamesLupolt since it's backing an index, yes. if it's just a stats object ... screwed

@swasheck Anglish

anguished english

now upvote my quest

1

Q: stats_column_id and index_column_id do not update with physical order of clustered index is changed

Unless I'm misunderstanding the purpose of the column, the following code indicates that a change of the structure of the clustered index does not change the ordinal position (stats_column_id)of the column in the sys.stats_columns DMV. (Tested in AdventureWorks2014, AdventureWorks2008R2) select ...

sql-server statistics dmv

;-)

now vote to close as dupe!

10:01 PM

Which one is real and which one is Memorex?

@JamesLupolt connect.microsoft.com/SQLServer/feedback/details/1163126

you're welcome to suggest edits

@swasheck Upvoted. It does seem to reflect the order within the stats object in some (most?) situations, though. I added some more examples to my question

> Use index_col() for stats objects (not really a good option)

Perhaps link to Aaron's post on why it isn't a good idea

@JamesLupolt unless altered

i'm an idiot

I have 999 (UK equivalent of 911) rep now

10:16 PM

duuuuuuuuuuuuuuuuuuuuuur

@JamesLupolt nice

@JamesLupolt interesting. i wonder why that is

seems to be specific to index-backing stats?

@swasheck Maybe, I'll look at it more later unless someone else does first

« first day (1521 days earlier) ← previous day next day → last day (3347 days later) »

Transcript for

Mar '155

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