The Heap™ – Consultancy ©® - 2014-08-06 (page 1 of 2)

@MikaelEriksson Personally I find @AndriyM's construction easier to read than your original.

Wow: whymicrosoft.com/Pages/linux.aspx

> However, sometimes your solution is focused solely on server-side operating systems, and that's where Linux falls short. Windows Server gives you the ...

Marian

@ypercube meh, marketing sucks

dbareactions.com/post/93774871771/…

that's a reaction for @JNK

9:19 AM

@PaulWhite I guess there's a CROSS APPLY version as well.

@ypercube Sure. It's also a little sad your EXCEPT version doesn't simplify to the same plan (it should!) but Anti-Semi joins don't have as many transformations.

Andriy M

@MikaelEriksson @PaulWhite So, is it WHERE EXISTS (INTERSECT) that is easier to optimise than the equivalent WHERE NOT EXISTS (EXCEPT) or is it just about INTERSECT vs EXCEPT where both involve a FROM-less SELECT outercolumn1, outercolumn2 and a select from VALUES?

@AndriyM Closer to the first option.

Mikael Eriksson

9:57 AM

@PaulWhite I can agree on that. I really don't have INTERSECT (or EXCEPT) in my mind as a tool to consider when writing queries. I guess I'm just too procedural minded.

12:01 PM

@Marian yeah!

12:18 PM

@MikaelEriksson I find the non-intersect easier to read myself but like you I seldom think to apply except and intersect operators

A: How to get index last modified time in Oracle?

0

I will elaborate on the @Jeffrey Kemp's comment. Since the information about authors of modifications is not collected in the objects' metadata, you will want to use database auditing mechanisms. As per Verifying Security Access with Auditing chapter of Database Security Guide, there are sever...

0

Q: Delete backups from old incarnation in rman

I did a refresh on a non-production database and these are the incarnations: List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time 1 1 ORCL 1355555557 PARENT 1 20-DEC-11 2 2 ORCL 1355555557 ...

VtC plz before it's too late to migrate.

Thanks ;-)

@YasirArsanukaev What's wrong with this one? (the Delete Backups I mean)

@ypercube Do you mean whether it's on-topic or off-topic?

12:35 PM

Yes. You suggested we vote to close but I don't see your vote. And you have 2 answers.

@ypercube Hey, I spent my two votes for either! [:

http://stackoverflow.com/posts/25123729/revisions
http://stackoverflow.com/posts/25092590/revisions

@YasirArsanukaev The question I meant (Delete Backup) is already migrated here.

No idea why it shows "Locked by Community" tho. Probably because it should never be reopened with new votes once migrated.

@ypercube Yup. It did.

OK then. I thought you meant we should close it here.

nonono, I noticed there's moderate activity on the [oracle] tag over SO with 70 questions in the review queue on average. Definitely lack some votes there.

1:59 PM

its another day on the heap

Q: ZIP files not working in SSIS (server level issue?)

2:18 PM

It's another day everywhere else too

Any thoughts for this user? I'm guessing server issues but no idea where to look

1

I'm posting this rather odd issue here in the remote chance that someone has come across this before, or possibly just has an idea or two about what I could try or check next because I'm stumped. Summary: SQL 2008 SSIS package tasks that attempt to create files with .zip extension fail with "Acc...

sql-server sql-server-2008 ssis zip

lolz

> a proof of behaviour

Q: Important Sounding Title for Analytic Report

2:36 PM

0

What is an important sounding title for an analytic report? The only thing I can think of is ' Analytic Report' but this title pales in comparison to the actual importance of the report. Here are some requirements for this report title: Garner positive vibes that will lead to a higher open cli...

reporting sharepoint database-projects

Really strange question to pop up here...

closed

@NickChammas "The Best Report Ever" sounds fine

@Lamak How about, "You won't BELIEVE what's in this report..." accompanied by a thumbnail of something that could vaguely be interpreted as sexual in nature?

The Internet is a wonderful teacher.

"We ran some numbers, and you won't BELIEVE what happened next..."

@NickChammas I don't think it gives the super duper important vibe the user is going for ;-)

2:44 PM

"This one weird statistical method is going to change everything you thought you knew about reporting!"

4

@JNK Hahahhahaha exactly

I hate those things

@Lamak Hmm, true, but it should get him the clicks!

@JNK hahahahah JNK knows how it works

I've been unfriending people on facebook who repost them

"Regular Analysts hate this report!"

2

2:45 PM

@Lamak Also good example

now you're with us Lamak :)

Hash Match (Flow Distinct) sounds like a chill operation

Does it have mad flow?

Mark Storey-Smith

@NickChammas is back?

or just passing through?

@MikeFal Maddest

2:55 PM

hey @NickChammas

@billinkc I'm hitting the same problem as this guy stackoverflow.com/questions/19949402/…

Yours crash too?

@billinkc the conditions seem to be similar, I'm doing a foreach loop on ~1000 XML files and ISServerExec is taking 1,5GB of memory

@billinkc not yet, but the execution is going for 8h where it takes ~15m usually

@billinkc I haven't stopped it because I want to trace the root cause, any ideas?

What all does your servers vital signs show? How about the execution report or query against the ssisdb.catalog.operaion_messages

Is it stalled on something, blocked, etc or does it look like it's alive but just crawling along

@billinkc Or just generally pissed off?

I have 4,8G of free memory, but the filesystem cache is taking 4,6 so it's out of memory, basically

on operation_messages it's complaining a lot that it's not able to page out buffers

it seems like some buffers are not being released from memory on each iteration

3:00 PM

In a normal execution, you'd zip through ~1000 xml files in 15 minutes?

on operation_messages it says only 2 buffers are being locked per iteration, and it's allocating like 9 buffers each time

yeah, they're pretty small

around 10-100K the average file

Just wanted to verify that this wasn't an exceptionally large volume. I seem to recall something about OS and the file cache there. But it sounds like the process doesn't have enough memory to work with and so it's a crawl

but it shouldn't be taking 1,5G of memory, that's the problem

The problem has happened before, but someone else cancelled the execution

Have you ever profiled the memory usage when it ran normal? Do you know that it shouldn't be taking 1,5G of memory?

SQL Server version is SP1 CU7 (3381), but ISServerExec.exe details show SP1 (3000), don't know if that's related to the problem somehow

yeah, I don't have exact numbers, but it doesn't balloon to 1,5G

3:08 PM

k, just checking.

Hi @mmarie

hi

hey @mmarie

I'm out of ideas to trace the problem

I'm gonna copy some sysinternals tools to the server to see if I can't find anything

@MarkStorey-Smith @Gonsalu - Hey guys!

Long time no see :-)

Yeah, I'm no longer a combo DBA-DBD. I'm actually starting to visit SO more often than here. shudder

How've you been?

3:15 PM

Fine. How are you?

in the US now?

Yep, back in the US of A.

@billinkc TIL that when you use the Attunity connectors for Oracle in SSIS the Oracle connection manager doesn't save your password even though you type it in and it shows up when you open the conmgr. If you parameterize your connection, you can see that the password isn't in the connection string. But if you add it in your parameter everything works fine.

Doing "Big Data". (don't hate me)

@NickChammas What flavor or big data?

@mmarie Lack of saved password will be due to project protection level as dontsavesensitive

3:18 PM

@mmarie The Hadoop + Vertica flavor.

Every time you say Vertica, a Kermit gets his wings

4

@NickChammas Cool. What kind of data do you look at? (What makes it big?)

@billinkc Oh that's right, we have a frog in the Vertica house now. Recently started working for them or something, right?

Sold his soul to the Highly Performant devil a few months back

@billinkc Ah, that makes sense.

3:23 PM

did someone say ..... VERTICA

I noticed nobody has answered your max join question on the vertica forums. 50 DKP MINUS

@mmarie It's not really big yet, hence the quotes around "Big Data". But it's varied (JSON + relational + unstructured text) and we are hoping it will become pretty big soon (like, ingesting 1TB/day).

@NickChammas @JNK hates you

Jul 30 at 14:15, by JNK

BigData is a buzz word

@Kermit lol lemme go add another star to that

there

@NickChammas traditional dbms people don't understand the buzz word

how it buzzes

3:24 PM

like a Kermit with wings?

bzzzzzzzzzzzzzzzzzz

@NickChammas going to boston next week?

I got 20 million rows in my data set. I'm a hunting, I'm a data scientist. This is big data

@Kermit Yep.

You gonna be there?

@billinkc, it seems the process has 1,2G allocated in heap to garbage collector

@NickChammas yes sir. shoot me an e-mail at norbert<dot>krupa(monkey)mirussoft.com

3:30 PM

Why, in our current modern society, must I be forced to mail things?

@MikeFal try faxing

They won't take it

gbn

@MikeFal So some people can muddle along working for gov'mint

It's the bank that manages my HSA. They're about as archaic as you can get. They don't accept faxes or emails.

@billinkc, support.microsoft.com/kb/2938192

3:34 PM

@Gonsalu Nice find.

@billinkc I'll push the client sysadmins to patch the server and will let you know if that was it if you're interested

You should blog about how to detect the situation along with links to that KB (so I can reference this in the future)

@billinkc I'll try :-)

Otherwise, I'll have to put it on my list of things that I'll never get done

@billinkc the abridged version is that I used vmmap and sorted the committed memory of the process which had 1.2G allocated to "GC"

then I googled SSIS garbage collection heh

3:47 PM

@NickChammas did you check out live aggregate projections yet?

4:28 PM

@Gonsalu not all files in an entire installation are updated with the same version as @@VERSION, also you can often choose to only update certain components (engine, SSIS, etc.) depending on the SP / CU.

@AaronBertrand thanks – is there any way to know the versions of certain executables in a given SP / CU?

I'm guessing no

@Gonsalu if you go to an SP's KB article they have a list of all the files that have changed, so if that file has been changed by that SP it should be listed there. If it hasn't, then no, AFAIK the SP isn't going to tell you the versions of all the files it hasn't touched.

There should be a much easier way to determine the version of SSIS installed, no?

@AaronBertrand found a KB article that seems to answer it (for SQL Server 2005, but I guess it still applies) support.microsoft.com/kb/942177/en-us

5:17 PM

Wonder what kind of ampage this provides? mobile.nytimes.com/blogs/bits/2014/08/06/…

@Phil yikes, if you can charge a phone with sound, I wonder how long it will be before you can wipe it, hack it, melt it, etc

Can't see it being able to provide enough current. Also, dogs will hate it. They mention they've found they can transmit data with ultrasound too.... TV remotes used ultrasound well before they used infrared :)

If I create a unique nonclustered index on a single column, table's a heap and that column's a guid, page compression isn't going to do damn thing, is it?

Since the guid's unique, it's not repeated and therefore it's just burning cpu? Or am I thinking of columnstore compression where it won't do anything (useful)

Thanks. I do have a variable at the package level that is being used in expressions for multiple ADONET.SqlCommand — Jay 5 mins ago

Talk about clearing things up...

In fact, page compression might actually make it worse in my limited tests. Why on earth is that a heap? Trying to impress Kesjer?

It's a staging table. We truncate and reload. It was clustered on the guid so figured we could save on the sorting of data

5:27 PM

Is the destination table clustered on the guid? If so, you're going to have to sort sooner or later, and I'd rather pay that price during ETL. YMMV. :-)

In fact in a previous life we wrote a C# parser to take incoming files and reproduce them sorted before ever letting SQL Server or SSIS touch them.

@AaronBertrand this comes from ignorance, but does the sorting of the staging really helps inserting it into the clustered one?, it should still need to sort it again, right?

(or wrong?)

@Lamak if you put it into the staging table sorted (or let the staging table sort it for you), then it is a much more sequential operation when you insert into the destination table (which is much more sensitive to locking and blocking, unless the system is offline).

@AaronBertrand yeah, I see what you mean

You still want an order by to be sure, but that will be a lot less work if that order by is the clustered index, rather than just a heap

yup, I get it

5:32 PM

It's going from table (OLTP) to a heap (staging) to a heap (DW)

Oh, is one of those the table with a lot of GUID columns you talked about?

Well, we're making staging and dw fact tables into heaps

Every table in my life has guids, clustered on nonsequential but this case is not the one with 300 columns, 1/3 of which were guids

I think I need to switch from getting paid twice a month to daily. Not because I can't manage money but I could at least rub myself down with it after dealing with this mess

@billinkc I get paid only once a month...daily would make it seem like it's not worth it

How would they even split a chicken into 30 pieces?

4

@billinkc I would request my payment in chorillanas, not chickens ;-)

5:37 PM

el snako ate my chorillanas

@billinkc hey, we have money dude.

@billinkc and then it died of a heart attack due to way to much cholesterol

mmmm....chorrillanas

@Lamak Remind them of football, when the gringos imply that they are the best in the world.

don't worry, I can't take offense from a comment of the guy with the weird head ;-)

(if we go from the avatar, that is)

5:44 PM

It's way weirder on the inside

@mmarie Well, you made me go to eat a chorrillana for lunch, thanks

Save me some

@billinkc I'll mail it to you

@Lamak I suppose that plate has peppers, right? I wish it was available here.

The English cuisine is, well, ...

A: Is there a way to supress the "rows affected" messages in a T-SQL loop and still get other messages in realtime?

RTFQ

-1

Put SET NOCOUNT ON Documentation

5:46 PM

@ypercube in most places isn't by default, but can ask for them with no problem

@ypercube is it that bad?

Do birds fly?

Does the pope poop in the woods?

@ypercube not in new zealand

bluefeet

@AaronBertrand every hour

Should this go to SuperUser? dba.stackexchange.com/questions/73311/…

or ServerFault? Or just closed as OT?

5:52 PM

More server fault I think

porton

6:02 PM

If I ask whether to make reliable Perl code I should to use or not use exceptions (RaiseError in DBI), would this be considered as an opinion-based question?

Oracle makes me lose it. @ypercube, what's wrong here?

@porton probably

and well, this chat room doesn't really deals with programming

@ypercube nevermind, I figured it out

and by "figure it out" I mean I saw how some other user did it ;-)

@Lamak was it the quotes?

@ypercube nah, that worked (it actually comes that way from the Text to DDL option). It needed to have a cast in the else '---' part

A query for @Aaron's collection of bad datetime conversions:

> WHERE
(CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101)) = (CONVERT(varchar, DATEADD(hh, - 7, c.CallStartDt), 101))
AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101))= (CONVERT(varchar, DATEADD(hh, - 7, d.CallStartDt), 101))
AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101))= (CONVERT(varchar, DATEADD(hh, - 7, e.CallStartDt), 101))
AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101))= (CONVERT(varchar, DATEADD(hh, - 7, es.CallStartDt), 101))
AND (CONVERT(varchar, DATEADD(hh, - 7, b.CallStartDt), 101)) >= '08/01/2014'

jesus

6:17 PM

Question here: stackoverflow.com/questions/25166992/…

Notice how they add -7 hours first - to both sides! - and then convert to the horrible '08/01/2014' format.

stop, you are gonna give him a heart attack

@Lamak The last drop is the single quoted aliases: as 'Dt'

Q: Generate a report that shows how many sql jobs (or same) are running at any given moment

0

I am using sql agent jobs. In the table sysjobshistory I have the columns: run_date, run_time, run_duration From all those columns, I would like to create a graph that shows me for a given date, how many jobs were running lets say at any given 3 minutes. I am using powerpivot in excel and que...

graph scheduled-tasks duration powerpivot sql-agent-job

@ypercube yup, it's like that code was created to purposely annoy @AaronBertrand

bluefeet

@ypercube are you trying to get his head to explode? :)

6:34 PM

/puke

@JNK happy work anniversary at Arrowsight

@Kermit Did LinkedIn tell you to say that

dba.stackexchange.com/questions/73344/… Scared him into deleting it :P

@Kermit yeah its been a year!

@AaronBertrand yes... /ashamed

6:42 PM

LOL no I just think it's funny how LinkedIn tries to make itself relevant by encouraging you to congratulate your colleagues.

And it also takes that opportunity any time you change anything - if you update your title (maybe it changed 5 years ago but you never updated LinkedIn) your connections start getting e-mails encouraging them to congratulate you on your great career move!

I just picture a bunch of LinkedIn marketers stomping their feet and bashing trash can lids together in a corner. "Hey guys! We're over here! We're cool!"

This one weird trick @jnk used that recruiters hate. Click here to read about his workiversary

@AaronBertrand you can disable all that.. especially if you're buffing your experience for potential prospects

@AaronBertrand seems to be working though

My only complaint is that it's starting to turn into Facebook.. 1 like = 1 prayer type crap

If you're always looking for a grass is greener job, then I could see how LinkedIn could be useful. For better or worse, I've been lucky my entire career so far.

Probably a lot of people in their 20s that think they are (or want to be) "movers and shakers" eat that shit up. The SQL Server community has been quite different from all of that.

6:57 PM

@AaronBertrand not sure if diversifying my skill set is moving and shaking, but i'm always looking for something that i can get exposure to as many technologies as possible

LinkedIn exposes you to technologies? Does it read to you?

Sorry, not trying to be an ass, just trying to understand how LinkedIn helps you learn

@AaronBertrand it doesn't help me learn, it helps be get found by potential clients or employers

I'm not saying that's wrong, just that it's not for everyone

i agree

7:18 PM

@billinkc Are you talking about page compression for the heap or the index or both?

I could use some help on pgsql on Ubuntu... Just trying to restart the server, and im at a complete loss of words.

For the index

been trying to get that goddamn server to restart for 20 minutes now and getting slightly annoyed...

yum sql-server

Restart the server or the postgres service?

7:19 PM

or is it apt-get sql-server

all of it, so it reloads the config, I dont know, just trying to get remote access to work >_<

so pg_ctl restart gives me pg_ctl: Coordinator or Datanode option not specified (-Z)

and I dont have a clue what those are

sudo service postgresql restart

that doesnt work

first thing I tried

Helpful....

no, to restart pgsql you need to install 5 packages

and i dont have a clue what else, probably black magic

7:23 PM

@PaulWhite my testing was on the index

pg_ctl restart -Z datanode -D /etc/postgresql did something

eventhough I dont have a clue what

lemme make an alias for that, im not gonna type that every time I want to reload the goddamn config...

Did you install it from a proper package, or manually?

Because service should work on a vanilla install, and so should using the /etc/rc.d/.... script, which is the same as using service

@billinkc rpm son

.deb

I used the digitalocean django setup

dont ask me what that does

also, I managed to break the ls command.

thats cool.

lemme add that to the list of basic commands in linux ive broken in the past, aside from sudo, su, cd.

7:28 PM

Just do sudo /etc/init.d/postg , hit tab and let it autocomplete then whack restart after it

/etc/init.d doesnt seem to exist

i tried that earlier

didn't know people use ubuntu for databases

would expected some fedora distro

@CBenni What does this show?: service postgresql status

@kermit LAMP people etc do

@Phil those lamps

7:30 PM

I love lamp!

@Kermit LAPP as well.

@Kermit, dont ask me why digitalocean uses it. I should have done the installation manually. But I know how much pain doing literally anything on linux is, so I didnt.

lamp brightens up my day

and it doesnt even set up LAMP

haha that kermit lamp.

7:31 PM

@CBenni linux is this shiznit

take away the "zni"

I remember installing and configuring pgsql on windows. Download the installer. Click OK and NEXT a few times. Done.

@CBenni It's easy in Linux as well. But if you let others install it, then they may do any weird thing, Linux or Windows.

im dieing here >_<

it now completely broke everything

lovin' it

What does service postgresql status say?

postgresql.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)

PGSQL's dead.

7:36 PM

@ypercube, nothing at all

literally nothing.

I blame that Torvalds chap

#blametorvalds

#bringbackourpg

@CBenni are you running it as root?

im getting slightly annoyed here

well pgsql cannot be run as root

so, no

7:37 PM

@CBenni you should still be able to get the status as root

there is no service running

What do you mean nothing? It should say "unrecognized service" or whatever

pgsql somehow runs 6 background processes

have you tried turning it off and on again?

I have @Kermit

second thing I tried

but guess what, I am currently trying to goddamn turn it off and on again

i might just rebuild the server again and move back to sqlite3

that was plug+play.

7:39 PM

@CBenni just use text files

lolno. But sqlite3 comes close to that

no database drivers, no nothing

just a single file with data in it.

its aweful regardless, and the python binding is buggy as hell

For pOstgres or SQLite?

both.

I have no issues with Postgres and Python. none at all.

I didnt have with sqlite3 for like... 1 hour

7:42 PM

Both are awesome. And Linux, too.

@AaronBertrand @billinkc It's interesting. I seem to be finding quite good compression (almost 2:1) on a single column index populated with NEWID if there are enough index entries (several million).

(Pray to Torvalds!)

tell me how to fix my installation then >_<

@PaulWhite I was on a lowly VM so I stopped at ~500000

@PaulWhite Can't wait to not understand the blog ;)

7:43 PM

@AaronBertrand I saw 2% compression at 1 million rows.

After the "restart", theres no database anymore.

nice stuff, eh?

Interesting, I'll have to do some more elaborate tests on my other box

RIP my database and django installation.

@PaulWhite did you ever look at that forum answer again to see if I'm ok or high? :-)

@billinkc I assume its just a standard dictionary compression effect. So with lots of ids chances are actually quite good for repeating bit patterns across the page.

@AaronBertrand Next-ish on my list. Still on my first coffee here :)

7:44 PM

ha ha no worries

@AaronBertrand And I'm still thinking the costs-sum-to-100% thing has a part to play.

@PaulWhite i dont know what you're talking about, but vertica has a 10:1 compression

It is web scale

@Kermit We're discussing basic compression in regular SQL Server tables, not XVelocity.

@PaulWhite dictionary compression on a single page shouldn't be any better just because there are more pages

7:46 PM

@AaronBertrand No that's true, but if the values on the page are denser...

oh thats cool too: I need to specify the -Z option, but if I do, it says "unknown option: -Z"

thats pretty cool, and im giving up.

@PaulWhite thank you for acknowledging the ferocity of the product

goodbye, nice to meet you.

Sorry, but you are not helping anyone with the rant. You are not helping yourself. You probably have made a mess but I doubt that everything is gone. It's very hard to mess that hard, in Linux. I suggest you take a short break, step outside and try again in a forum that can help you better. (The irc channel at #postgresql is very good.)
But don't rush like you did here. Explain what you did clearly, which user you are using (root or something else, etc.)

@billinkc "web scale". Behave yourself :)

7:49 PM

@ypercube There you go, being helpful again...

@billinkc I know, I'll never be a true DBA. :(

He wasn't helping us in the slightest. Was an incoherent stream of stuff

@AaronBertrand @billinkc Ah never mind, it turns out the 'compression' was because the uncompressed index happened to be very fragmented. When I rebuilt it, the compressed index was still smaller, but only a tiny bit ~ 2% not ~50%. Interestingly, SQL Server still chose to use compression (so it must have made space for at least 1 extra row in each index page).

Troubleshooting is a step-by-step process, not a messy brain dump and GTFO process

I agree 100%

7:53 PM

It's also possible that only the heap RID is being compressed.

Yep, with a unique integer clustered index, the compressed pages are not compressed.

ah the flipping rid

For 500K rows (heap, only column is NEWID) I had 42K index uncompressed, 45K compressed (!). After rebuild they were closer but compressed was still larger (30K vs. 31.6K)