The Heap™ – Consultancy ©®

ypercube

10:56

Can this at SO: stackoverflow.com/questions/28890544/…

be merged with the identical at DBA?: dba.stackexchange.com/questions/94545/…

@bluefeet, I've flagged the SO one.

Or is it a problem that both have an accepted answer?

bluefeet

I just migrated the SO on here, and VtC as a dup. A mod will have to merge it

ypercube

thnx

Phil

11:46

Clickety

JNK

12:40

morning all

Lamak

13:26

morning

ypercube

13:49

@Nico what are you talking about? Can you prove your claim that ISNULL() is faster than COALESCE()? — ypercube 53 secs ago

His profile show he's a SQL-Server guy. I bet he did not notice it's a mysql question.

Lamak

@ypercube doesn't ISNULL in MySQL returns 1 or 0?

Andriy M

@Lamak No, it returns an error. IFNULL, on the other hand...

Lamak

@AndriyM ah, I shouldn't talk about anything MySQL related, my ignorance shows

ypercube

@Lamak Yes, you are right.

Andriy M

Ah, you see, @Lamak, it was me who was ignorant :)

Lamak

13:56

well, look at that

ypercube

Thnx Lamak, I commented about that. I rarely use ISNULL and IFNULL and NULLIF, for that exact reason

(that I have to look up what each one does every time)

Lamak

@ypercube yeah, I just vaguely remembered about ISNULL on MySQL

Aaron Bertrand

@ypercube ISNULL() can be faster than COALESCE() in one scenario: mssqltips.com/sqlservertip/2689/…

ypercube

@AaronBertrand Yeah, I remember you had a blog about it. The comment was on a mysql question ;)

Phil

Kill:

-1

Q: How redo is transfered to standby database?

As we all know that redo is transported from primary db to standby db .By referring diff docs I came to know that redo data is transfered by i)reading from redo log buffer in one of the modes(max availability/protection/performance) , ii)redo logs are transfered in other mode and iii)archive logs...

Can't find his previously closed question that he posted yesterday

Aaron Bertrand

14:07

He self-deleted

Max Vernon

14:45

@MikaelEriksson - nice answer dba.stackexchange.com/a/94697/10832 - to bad the OP did not ask his question properly.

ypercube

15:01

@AndriyM You were right, too ;)

swasheck

EVERYONE GETS A RIBBON!!!

Mikael Eriksson

15:29

@MaxVernon Thanks, I knew that the effect of switching the column order in the clustered key was depending on the cardinality of ServerID but I did not expect just one value for that column. He did say "replicated from multiple servers" in another comment.

bluefeet

ooo, sql fiddle got an upgrade

swasheck

it got the "make it work" patch?

bluefeet

looks like it dropped several DB engines and now only has sql server 2014

tons of links across SO just died

ypercube

also dopped mysql 5.5 and postgres previous to 9.2

bluefeet

easier to support fewer

Aaron Bertrand

15:40

The links will still work

swasheck

@bluefeet i heard the screams of millions of links, dieing in unison

Aaron Bertrand

Extra version gets added to the dropdown

bluefeet

@AaronBertrand doesn't load a result set for data

Aaron Bertrand

Oh yeah you went further than I did

bluefeet

ha

Aaron Bertrand

15:42

bluefeet

yup

I've got so many broken links now

billinkc

lakhs of broken links

Aaron Bertrand

millions of lakhs

billinkc

I wonder if that was intentional or even a consideration when Jake rolled out the upgrade.

Hard to complain about a free service doing whatever they want but I'm sure there will be much gnashing of teeth about it

bluefeet

39k+ links

billinkc

15:47

Well, that's just like your opinion, man

Aaron Bertrand

10,000 for SQL Server 2008

Only 200 on dba.se

Probably a ton for 2012 across both, too

(I forget what number goes in the URL for that, though)

bluefeet

it's too bad that SE doesn't integrate something in their system

Lamak

@bluefeet yup, would be really useful

billinkc

Looks like my authentication to sqlfiddle is dead

May just be my browser, but my gmail account doesn't have a G+ profile so after clicking Accept, it gets stuck on the return auth page

Fortunately, I only have 11 fiddles to my name in answers

John

16:03

@billinkc same issue for me

Lamak

there's authentication in sqlfiddle?

Mark Sinkinson

@Lamak *was :)

Lamak

ah, right ;-)

Phil

16:41

The error messages you have provided are not from the alert log. There is a problem with the database, and we need the information from the alert log to diagnose what the problem is. I have provided a link in a comment above with instructions that tell you how to find the log file. Help us to help you. — Phil 54 secs ago

Like pulling teeth. Only more painful

How are you all? Been very quiet in here over the last few days...

billinkc

It's because you stole all of our people for some bitter sequel thing

Lamak

17:02

what?, I didn't get that one

Mike Fal

He refers to SQLBits, where many people were in the UK eating fish and chips and drinking warm beer.

Aaron Bertrand

I drank no such thing - I abhor the lack of refrigeration. I had Carlsberg (barely beer) and Strongbow (cold, not room temperature, cider).

Lamak

ah, thanks for the explanation

bluefeet

getting a new hard drive at work = a day of little productivity

Aaron Bertrand

I'm getting a new pair of monitors today, so will suffer the same this afternoon

But then my productivity tomorrow, whoa, watch out!

swasheck

17:14

@AaronBertrand i was wondering if you'd be available for a quick query plan question

Aaron Bertrand

Depends on how complex

(Recovering from time shift and catching up on a lot of things on the homefront)

swasheck

@AaronBertrand no worries then. it's complex, but i'm perplexed by a query that i tried to rewrite with no CTEs ... it has a different plan shape

Aaron Bertrand

Can you send me the two .pesession files?

(You have actual, post-execution plans, right? :-))

swasheck

17:33

@AaronBertrand sorry for the delay. i have multiple queries in one .queryanalysis file

Aaron Bertrand

ok

swasheck

query away

ypercube

@Aaron is there a way to add 2 time values? (apart from the hack I did here?): dba.stackexchange.com/questions/94787/…

(converting both to datetime and the do d1+d2)

Aaron Bertrand

@ypercube no, they're not numbers.

ypercube

It's a pity that SQL-Server does not have a type for time intervals (and more functions for date and time arithmetic)

swasheck

17:48

@ypercube that's what clients are for

ypercube

I don't agree.

swasheck

18:00

that's fine. i was just being a bit silly

Aaron Bertrand

@swasheck how are stats? Are they up to date? Estimates seem way, way, way off. Also if you look at some of the parallel operations, they are only being processed on two threads, and the other 4 do nothing. Is this really a 6-core processor? Or 6 vCPUs assigned to a VM? Is there NUMA? Have you tried with MAXDOP 1 or MAXDOP 2?

billinkc

Swasheck runs as MAXDERP = infinity

2

bluefeet

^^ This just about covers today

Kris Gruttemeyer

@bluefeet Sounds about right. Bought a Surface Pro with mine. The rest went to the wife and whatever she feels it needs to be spent on.

Lamak

18:26

@bluefeet are you getting an iWatch?

bluefeet

@Lamak I have a watch problem so I'd really like to get one. But I really need a new computer at home

Lamak

a watch problem?

bluefeet

I love them. I have a lot of them

Lamak

ah, that kind of problem

@bluefeet I also love watches, but only have 2

Limited quantities of the Apple Watch Editiion, Prices from $10,000

wow

Mike Fal

@bluefeet You have probably some idea how applicable that is to office conversation here today.

bluefeet

18:31

it's a shut up and take my money kind of day

Lamak

The new macbook is very impressive. But for me, the apple watch is kind of hideous

Phil

That new Macbook is insane

Lamak

yup

JNK

I didn't follow the announcements

:(

billinkc

I thought people were joking about the 10k price tag for one of the premium watches

I think I'll concede that must be a real price point with as much chatter I see about it

Lamak

18:35

Many people thought so

JNK

that sounds like one of those Onion articles that comes true in a few years

like "Gillette says 'Screw it, we're going to use FIVE blades!'"

Phil

People hate on Apple, but I don't see any other company making laptops that even begin to rival that new Macbook...

Aaron Bertrand

@JNK thinnest laptop ever (thinner than all airs), 12" screen, 2308x1440 I think

< 2lbs

Lamak

well, it's "some group of people" hate on Apple and "some other very large group of people" loves Apple

JNK

@AaronBertrand How many mortgages

Aaron Bertrand

18:39

@JNK $1299/$1599

JNK

oh that's not bad

Lamak

nope, it's really impressive

Phil

apple.com/uk/macbook

Aaron Bertrand

I will not buy one of those for me until they get past the 8GB cap. But I will probably replace my wife's 2007 MacBook with it, even though she says "it still works fine."

Phil

I want to do things that a man should not do to machines. Drool

bluefeet

18:52

confused much?

Teradata in the title but tagged with MySQL? What database are you using? — bluefeet ♦ 34 secs ago

billinkc

our thickest pancake yet http://t.co/JPVabVh1qB

Tweeted by DennysDiner on March 9, 2015 at 5:51 PM

swasheck

@AaronBertrand stats are up-to-date but sample rate is crazy low due to large table ... 6 NUMA-node AMD POS

Aaron Bertrand

There are 6 NUMA nodes? How many cores in each node?

(I miss the "power of 2" days.)

billinkc

Ahoy Diplomat in chief

bluefeet

wtf

This is teradata but I saw it was suggested to tag in MySQL. I think they are very similar. — angelcake 3 mins ago

I think they are very similar

billinkc

19:01

think is a bit generous there

bluefeet

@angelcake Teradata and MySQL are not similar in any way except they execute sql. — bluefeet ♦ 10 secs ago

Aaron Bertrand

Those stupid suggestions need to stop.

swasheck

@AaronBertrand 4

Aaron Bertrand

So try the query with maxdop 4

Or maxdop 1. I can't even see the whole query but that looks like a monster (the join diagram makes me nervous). I'd have a hard time making suggestions at the table / query level.

swasheck

@AaronBertrand it's awful

@billinkc update @billinkc set [a-hole] = 1

2

Aaron Bertrand

19:10

Or just declare @billinkc [a-hole];

swasheck

@AaronBertrand true

Lamak

Let's agree that the best option would be DROP TABLE billinkc

swasheck

@AaronBertrand so run both the CTE and nonCTE with maxdop 4?

nevermind. someone just added an index. hooray for release management

Aaron Bertrand

Yeah, worth a try, also worth a try with no parallelism at all. Your big problem is the seek on fund_bmk, if you can't get accurate stats (they're 1,179% off!) you may be better off trying FORCESCAN or create a filtered index that can support the query (you'll have to manage those stats yourself though).

swasheck

@AaronBertrand thanks. yeah - the hinted query uses forcescan

Aaron Bertrand

19:18

Oh yeah look at that - it shows how terribly useless estimated costs are too

swasheck

:)

JNK

Has anyone here had to hire a DB professional recently? If so what resources did you use?

Kris Gruttemeyer

@JNK We hired an iSeries DBA, does that count?

JNK

Close enough

I may be hiring a Sr. Developer/DBA soonish

Kris Gruttemeyer

@JNK I believe we posted the ad to our website, monster and used a recruiter. Do you want me to try and find which one?

JNK

19:23

And prior attempts have had bad luck identifying good people

@KrisGruttemeyer nah I'm just trying to get a general idea of what works

swasheck

@JNK 100% remote?

JNK

I think part of our issue here is we are close to NYC but not IN NYC and finance sucks all the talent to Manhattan

Kris Gruttemeyer

@JNK I think the recruiter was what finally pulled him in. We got incredibly lucky, though. He has 30 years experience, worked for IBM and did consulting for about a decade.

JNK

@swasheck probably 0% but I can find out :)

specifics are being worked out

im gonna delete that for posterity

Lamak

you are gonna delete it for posterity?

JNK

19:27

yes

PURGED

billinkc

I don't think that word means what you think it means

Mike Fal

Posterity hears ya'. Posterity doesn't care.

Damn kids these days

JNK

@billinkc I want to make sure nobody in the future ever sees it

it's the inverse of preserving for posterity

billinkc

I think your first mistake was in thinking anyone in the future would seek out your ramblings.

bluefeet

functions galore

0

Q: SQL Statement running extreamly Slow

Okay I have look through several posts about SQL running slow and I didn't see anything similar to this, so my apologies if I missed one. I was asked about a month ago to create a view that would allow the user to report what hasn't been billed yet, and the SQL is joining 4 tables together. One i...

sql-server-2008

JNK

19:32

@billinkc It won't be my last.

@bluefeet Also can you WHERE SUM()? Should be HAVING no?

billinkc

I long for a GetBillInKCProgress method to be standard everywhere — billinkc 16 secs ago

And deleted for prosperity

JNK

@billinkc Delete it to 10 friends and money will come your way this month.

billinkc

I don't have 10 friends

Even in binary

JNK

@billinkc Not even "friends" on facebook?

You have at least 10 spambots following you on tweeter

billinkc

And I paid good money for them

Andriy M

19:37

@AaronBertrand 3310 on SO and 101 on DBA.SE. They aren't broken actually but they will now execute in SS2014.

swasheck

19:48

25 or 6 to 4

ypercube

@AndriyM 9987 and 206 for mysql 5.5

Kris Gruttemeyer

0

Q: SQL Date Conversion Query

TimeStamp is a numeric datatype field in my table R for example: 2445302102010 (02/10/2010) I am trying to query by a date range of 02/09/15 - 02/15/15, the problem is I am getting results from 2010? Select distinct R.CID, RIGHT(R.TimeStamp,8) from TableRev R...

sql db2 date-conversion

I don't always store dates as numeric, but when i do....

Andriy M

@ypercube It's amazing that the figures for SS2008 are exactly the same. To the point that one might think there's some kind of mistake :)

JNK

@KrisGruttemeyer no as STRINGS

because they want to keep those padded 0s in there

because datatypes are for suckers, man!

Kris Gruttemeyer

@JNK It's just sad, really. Just use the correct datatypes. You can convert, cast and reformat to your little heart's content, just make sure the damn underlying type is right.

/rant

Just drives me crazy when people use a spatula to rebuild a car engine.

JNK

20:01

"But I'm really comfortable with spatulas!"

Kris Gruttemeyer

HA!

It's not even CCYYMMDD

ypercube

@AndriyM Or I did a mistake. I thought !3 was for 5.5

Kris Gruttemeyer

It's all bass ackwards

0

Q: SQL Server hitting System.OutOfMemoryException but server has 10GB free

SQL Server 2012 11.0.5058.0 I'm running a very large batch of queries (about 2700) to simulate a user doing X, Y and Z in our app. I have SQL Server's max memory set to 2147483647 MB. However when I run the query I get several messages like so: An error occurred while executing batch. Error me...

sql-server sql-server-2012

Answer: Don't have SSMS return 18 million rows to the results window

Andriy M

@ypercube 19279 and 298 for MySQL 5.5

billinkc

And the award for the most obnoxious comment goes to

Help me understand: your source query has some identifier to indicate what catalog the data resides in. These catalogs have consistent entities in them and you need to run some query across them all (perhaps a billing report). Is that about accurate? That sounds accurate. As Ako asked, is it possible that Client20 suddenly accumulates so much data you need to push them to their own server? I am not sure to be honest, It's a pretty simple lookup (at least with script component) and the results are saved as flatfiles if that info is of any use — August 5 hours ago

Lamak

20:16

it wasn't what I was expecting

billinkc

I should delete my comment to make that look really insane

Lamak

actually, you should delete your comments, period

ypercube

This query works great except I have encounter two issues. 1. I get an error "Athen — angelcake 45 secs ago

How can it work great and they get an error? ...

Lamak

maybe before he got many errors

Kris Gruttemeyer

many errors. work great. so much broke.

JNK

20:35

@billinkc And the award for the most obnoxious comment goes to I'm not sure what you find obnoxious about it

billinkc

It's like the people who respond inline to emails - see my response in chartreuse below

JNK

we have some of that here actually

I've done it if there's a bullet list of Qs in the email or something

Aaron Bertrand

@JNK the OP copied all of Bill's comment and then added the answers at the end

billinkc

That I can at least understand. I have people that weave sentences into existing paragraphs

JNK

@AaronBertrand the OP copied all of Bill's comment and then added the answers at the end Yeah I saw :)

5

Aaron Bertrand

20:37

@JNK And the award for the most obnoxious comment goes to I'm not sure what you find obnoxious about it Here, allow me to illustrate

JNK

@AaronBertrand note that my response to him was in the same format

Aaron Bertrand

Sorry, jetlag

JNK

Its' fine

How was Britain

billinkc

Just when I'm finally giving up on being distracted and doing some work, y'all go and entertain me

JNK

I saw you and Kevin Ghostbusting

Aaron Bertrand

20:39

It was fun. Love that conference. Cool folks.

swasheck

55,892,200 rows = 1.25% default sample rate (SQL Server 2014 Compat Level 12 TF 2371 enabled) ...

JNK

@AaronBertrand That seems like one of the better conferences

It also saddens me I couldn't go and probably won't make PASS this year

Aaron Bertrand

Booooo

JNK

yeah I know

We spent a lot of new money on stuff recently

like monitoring tools from this company based in NC

Kris Gruttemeyer

I'm going this year, my first one.

Aaron Bertrand

20:46

Then I meant YAAAAAYYY!

JNK

lol

We also dropped some money on a SSRS config manager thingy too

so database funds got used up

billinkc

Here's a Ferrari. Nope, you can't have training manuals until next year

Kris Gruttemeyer

@JNK Mine didn't even make it into our budget. Guy who was in charge of it (higher up, after I asked repeatedly if he wanted me to do it) 'forgot' to add it to the budget. Might not be able to get our prod server off of 2005 til next year

@JNK Company in NC that does SQL monitoring? Has to be based out of Raleigh or Charlotte

JNK

@KrisGruttemeyer It's SQL Sentry, was messing w/Aaron (who works for them)

which BTW I highly recommend

Kris Gruttemeyer

@JNK Ahhh, I see now. Carry on

billinkc

20:50

$1495 is the current registration price, can usually find someone offering 150 off of that price. Then it's a question of airfare, hotel and vacation

Kris Gruttemeyer

@JNK Yeah, we have foglight and redgate. I've tried SQL Sentry but can't convince the higher ups to buy it.

Alright, time to head out on this scorching 43 degree day. Nice to see that white crap on the ground start to melt. Have a good one guys

JNK

Good night

ypercube

21:33

@KrisGruttemeyer I hope you don't have *= and =* in your code, like this guy

0

Q: Moving from SQL 2005 to 2012

Here is my Stored procedure written in SQLServer 2005. Now we are moving to SQL2012 and getting bunch of errors where I used *=. I know I have to use LEFT OUTER JOIN syntax, but somehow I am stumped. Can someone please help me ? Also getting error on where I used ABS in where clause. These 2 li...

sql sql-server sql-server-2005 sql-server-2012 outer-join

Wasn't the syntax deprecated from 2005 version? or it was in the 2008 version?

Aaron Bertrand

21:58

@ypercube deprecated in 2005, but last working compat mode is 80. So can't be made to work in 2012.

ypercube

@AaronBertrand So this guy must have had the db in 80 mode?

Aaron Bertrand

@ypercube yes, that's right. I can validate later but not at the moment.

swasheck

YOU SUNUVA MOTHERLESS GOAT! I FART IN YOUR GENERAL DIRECTION

ypercube

@AaronBertrand Thnx. Just curious I was.

@AaronBertrand Perhaps you should make a blog post about how to convert such queries. I'm sure there are still more dbs out there and it's going to get more difficult, when they finally decide to upgrade to 2012 or 2014.

I searched the Net (to confirm that *= is LEFT) and it wasn't easy.

Phil

22:18

@swasheck I fart in your general direction!

Transcript for

The Heap™ – Consultancy ©®