The Heap™ – Consultancy ©®

Paul White

12:14 AM

Not really. Out and about on phone only.

swasheck

ok

ypercube

1:10 AM

Just close: dba.stackexchange.com/questions/96647/…

Andriy M

9:04 AM

This person seems to be able to read an entire book in 7 min:

0

Q: How is the B-Tree created for a composite PK in SQL Server?

This is a theoretical question in regards to how SQL Server stores the data for a table that has a clustered index over two columns (both columns form a PK). Consider a table with columns PersonID, PersonAge and Name. I have a clusteredIndex on both PersonID and age.. how is the information store...

Andriy M

9:14 AM

Just noticed: the question is on the verge of being sent over here. Do we want it?

Paul White

10:16 AM

It's probably a better fit here than SO yes.

ypercube

11:05 AM

Weird. A recruiter sent me an email about a sql-server dba position.

ypercube

11:34 AM

@PaulWhite fetched. in case you plan to answer.

Paul White

Thanks.

Kris Gruttemeyer

12:25 PM

Anybody else noticing very slow response times on SO and DBA?

bluefeet

@KrisGruttemeyer yes, it appears to be due to the StackEgg game in the right sidebar

Kris Gruttemeyer

Ahhh, didn't notice that.

bluefeet

that's the assumption right now. The slowness is from people playing that game

Kris Gruttemeyer

Disabled that thing like it's hot

much faster now

Paul White

Stack Egg lol

Kris Gruttemeyer

12:38 PM

Spoke too soon, even with it disabled it's slow.

Lamak

@bluefeet I don't see it

bluefeet

@Lamak weird

Mark Sinkinson

@KrisGruttemeyer Doesn't even load for me.

Kris Gruttemeyer

@MarkSinkinson Yeah, it did that this morning from home. I thought it was just my interwebs being dumb (time warner cable customer here).

Lamak

@bluefeet this is discrimination!

ypercube

12:53 PM

@Lamak you are a lucky guy

Lamak

I didn't say it was a bad discrimination ;-)

ypercube

I didn't say you did ;)

Lamak

ah, well, I got it now

ypercube

Ah, pity. I thought you'd reply.

JNK

morning

ypercube

12:57 PM

I didn't say you said I did.

I didn't say you said I said you did.

Lamak

@ypercube I was, but I thought I would annoy everyone here (not that I don't do that already)

@JNK hi

billinkc

2:15 PM

Possible Duplicate from Meta.MoversAss meta.stackoverflow.com/questions/289022/… — Dmitrij A 13 mins ago

I see what you did there

JNK

2:29 PM

@MikeFal nice post on invoke-sqlcmd

Mike Fal

2:55 PM

@JNK Thanks. Got the idea to write it after a community member was bitching about how he had to do all this manipulation to get a value from Invoke-SqlCmd because he couldn't suppress headers.

And my first thought was "well, that's because you're doing it wrong". And then suddenly blog post.

Andriy M

3:07 PM

So they were simply issuing a SELECT without an ORDER BY and assuming the clustered index's order would apply.

Guys, thanks for showing me how to verify this. I think knowing how to verify this (via looking out for the SORT on the execution plan as opposed to just SELECT it and hoping to see sorted results) is worth being an "answer". If one of you puts that into an answer, I'll vote it as CORRECT. — LearnByReading 16 mins ago

Is there a canonical question about that on DBA?

Trying to find one, this seems closest so far, but somehow not close enough:

5

Q: Storage order vs Result order

This is a spin-off question from Sort order specified in primary key, yet sorting is executed on SELECT. @Catcall says this on the subject of storage order (clustered index) and the output order A lot of people believe that a clustered index guarantees a sort order on output. But that's not ...

sql-server execution-plan clustered-index sorting

ekaj

Any recommendations to take an into database management class online? I've come up short so far

ypercube

@AndriyM This: dba.stackexchange.com/questions/5774/…

Mike Fal

3:22 PM

@JNK Heh, I was tweeting that exact response to Jack. :)

JNK

good point on the constant

Mike Fal

IT's a nitpick, but I try to use that over strings

JNK

No I do too

I was just trying to type it into twitter

I need to actually blog again

Worked on a new problem the past week that was interesting dealing with parsing iis logs

and forced me to find a more scalable alternative to get-content

Shawn Melton

If you have a user come to you wanting to know if SQL Server FileStream/FileTable is a viable option for storing around 1.3 million documents at about 1.5TB of space....would you (1) go jump of a cliff or (2) tell them to use SharePoint or (3) _____?

JNK

@ShawnMelton Suggest storing the files in a file system and storing pointers to the files in the DB?

Shawn Melton

3:32 PM

@JNK That is what I was thinking, not sure how their app wants to interact with the documents but that seems like a bad idea to put it in SQL Server even with Filestream.

billinkc

mangodb

JNK

@ShawnMelton I can't think of a reason to put it in the actual DB unless you want to leverage some HA/DR features or something, of which there are better tools for file system stuff

Or I guess if you want to use RI to delete files when the parent is deleted or something else silly

Shawn Melton

Appreciate the feedback

Vogel612

Hello there DBA people.

JNK

Hi

Vogel612

3:40 PM

I just right now (aka. 5 minutes ago) asked a question on SO, and now I realize it might be a better fit here...

bluefeet

@Vogel612 link?

Vogel612

sure

Lamak

hm...it does seems like it would be better in dba

JNK

yeah it does

we can migrate it

Vogel612

thank you very much, no objections.

JNK

3:42 PM

voted

Lamak

me too

Vogel612

migration completed

ohh.. interesting.. I got the upvote rep twice.

JNK

@Vogel612 yeah you will get it on both sites I think

They can't reconcile cross-site votes because userIDs don't line up I believe

Vogel612

didn't know that. then on the other hand this is my first migration of a question of mine...

Lamak

@Vogel612 alright guys, time to downvote to level things up

Mike Fal

3:46 PM

@Lamak On it!

Vogel612

ehh?? what about my score then??

Aaron Bertrand

Well, it's something they could do. After all the top bar, profile, reputation leagues, etc. can figure out my various user IDs.

JNK

everyone just go downvote this thing

Lamak

@Vogel612 joking

Vogel612

also I actually doubt you guys can get enough downvotes to counter assoc bonus

JNK

3:47 PM

@AaronBertrand I think it's too expensive an operation to do because of record volume

Lamak

@MikeFal I knew I could count with you

@Vogel612 did you hear a challenge?

JNK

or maybe they just don't care that much

Mike Fal

@Lamak I have a script for that.

Aaron Bertrand

Also if the original ultimately gets deleted I'm not sure the double rep gain is permanent.

ypercube

@Vogel612 You can probably use pg_restore for what you want.

Vogel612

3:48 PM

hmm.. then I'll be back to reading the pg_restore manual again... maybe I find out myself!

ypercube

But do not restore to the original table. First create an empty table, identical to the one you want restored but without any foreign keys triggers. then restore to that table.

Then you can check data and if they are ok, you can disable triggers foreign keys (in psql) and move the data to the final destination.

(There might be a better option, just wait and someone will answer on the site)

Vogel612

I already got a comment that looks like an answer...

Aaron Bertrand

Think about speaking in English. "I'd like ham and eggs or toast." Does that mean you want ham and (eggs or toast) or (ham and eggs) or toast? See databases.aspfaq.com/general/… — Aaron Bertrand 1 min ago

swasheck

4:14 PM

ok. i'm getting a few different conflicting recommendations

MAXDOP ... number of cores per socket, or number of sockets?

billinkc

1

Mike Fal

@swasheck One sec, there's an MSDN article I tend to refer to.

billinkc

NO SET IT TO ONE!!!EXCLAMATION POINT

Mike Fal

http://support.microsoft.com/en-us/kb/2806535
Though my general rule of thumb is 1/2 number of cores up to 8, then 8. That's more anecdotal than based on any hard evidence.

And @billinkc can shut the hell up.

billinkc

Like I know how to shut up

I could be hiding under the bed from the boogie man and I'd still be rambling on

On further thought, the only sound I'd make if the Owlman was about was the sound of pee hitting the floor

Lord of Tears

Lord of Tears is a 2013 Scottish horror film directed by Lawrie Brewster. The film first released on October 25, 2013 in Whitby at the Bram Stoker International Film Festival, where it won two awards. The film follows a Scottish schoolteacher that begins to see visions of the Owl Man, a strange figure that he was obsessed with as a child. == Plot == James (Euan Douglas) is an average school teacher that has been estranged from his mother for years and has only returned to her home to settle her estate after her death. This somewhat baffles his friend Allen (Jamie Scott Gordon), as his own father...

swasheck

4:30 PM

@MikeFal total cores? NUMA nodes? logical cores? i think that's where we get all kinds of confusion

@billinkc lord of the thighs

billinkc

@swasheck No, that's the 80s slasher flick at a fat camp

Mike Fal

@swasheck Logical cores

I don't get to hung up on numa nodes.

swasheck

foreign memory can be 'spensive

billinkc

Mangy foreigners taking up all our IRQs

Mike Fal

@billinkc Yeah, that really irqs me

billinkc

4:35 PM

/golfclap

Mike Fal

Don't interrupt me, jerk

billinkc

Don't get your jumper in a wad

Paul White

@swasheck So they say. Never really seen a convincing case myself.

I can usually find more important perf problems before I worry about local vs remote memory access. It's tough to get parallel queries to stay within a NUMA node anyway, under anything except lab conditions, even with #schedulers = NUMA node size.

Like Mike, I like 8.

All these numbers are just starting points really. Go with whatever you find convincing.

swasheck

@PaulWhite MS just told me to scale back to 2

@PaulWhite ... so what does Sack mean here?

> the legacy CE joins the histograms on the join columns by aligning the two histograms step-by-step using linear interpolation

i understand linear interpolation, but not when it comes to this

Mike Fal

4:53 PM

@swasheck What's the problem you're trying to solve that MS wants you to scale back to 2?

Paul White

@swasheck To match two histogram steps, they need to have the same boundary values. To move a boundary, it uses linear interpolation.

billinkc

Yesterday I delivered a presentation to the client's developers (SQL & .NET) on a hodgepodge of SQL related things. One of the items I said was get the 2014 version of SSMS because it can do (all these demoed features). I do have on its very own slide: Known issue - the IS object explorer can only talk to same SQL Server version so keep your 2008 client for legacy SSIS stuff

Paul White

@swasheck That might be right for you. No way to know from here. If you trust the MS person, and the information you provided to them, go with it.

billinkc

I even included the error message to let them know what error is generated when they try to use 2014 to talk to a 2008

NumberOfEmailsAboutNotAbleToConnect just overflowed bigint

Max Vernon

5:08 PM

@swasheck I take it you've seen this? dba.stackexchange.com/questions/36522/…

swasheck

@MikeFal deadlocked schedulers and high cxpacket waits

@MaxVernon yes. but i dont trust the answerer ;)

Max Vernon

@swasheck lol

I don't either.

Mike Fal

@swasheck Surprised they didn't tell you to eliminate parallelism all together. #notfixingtheproblem.

swasheck

@PaulWhite cool. thanks

Paul White

@swasheck Did it make any sense at all?

swasheck

5:15 PM

@PaulWhite initially it did ... but which steps and values in the histogram would it use?

@MikeFal MAXDOP UNO

Paul White

@swasheck It uses the modified histograms that arrive at each side of the join from whatever is underneath. In the simplest case of two unfiltered tables being joined on a single column, it would be the histograms for each column from the base statistics.

Point being the two sets of histograms will generally have different low and high values, and different RANGE_HI_KEY steps in between, so alignment is necessary.

Mike Fal

@swasheck You seem to be running a Sharepoint instance. Can I help you set MAXDOP for that?

Paul White

The question the join is answering is: to what extent do the incoming ranges of values overlap/match up.

Max Vernon

@MikeFal lol! I don't always set my MAXDOP, but when I do it's to !1!

Mike Fal

2

Max Vernon

5:26 PM

@MikeFal NICE

Whats with this stackegg thing?

and why do we suck so bad? dba.stackexchange.com/stackegg/leaderboard

Aaron Bertrand

I don't know, is sucking at candy crush saga a bad thing too?

bluefeet

is anyone even playing stack egg?

Max Vernon

@bluefeet I got suckered in, but I guess I'm not smart enough to understand the point

Aaron Bertrand

I haven't touched it.

Max Vernon

I just keep hitting "close"

Andriy M

5:39 PM

Tried it on SO. Good source of frustration. Other than that just a time waster.

bluefeet

there is no sense in playing because one of the sites basically ruined it for everyone

32

Q: When something that was supposed to be fun became not fun

StackEgg! What a great invention. Probably the best thing to hit Stack Exchange since the invention of unicoins! Very entertaining game, very fun to play. Many users have probably spent several hours playing this. And April Fool's day is not over yet. However, where there is great inventions t...

discussion fun april-fools stackegg

billinkc

I've looked at many SO questions today and until I deliberately went looking for this stackegg thing, I hadn't noticed it. I just assumed it was advertising and ignored it

bluefeet

it's the annual April Fools day ~~prank~~ fun

billinkc

John Oliver's pledge was too long for me to remember it, much less recite it

ypercube

@AndriyM you were right

@ypercube yes, that's exactly what I was expecting because the rows have to be EITHER physically OR logically sorted. Either way, the displayed rows should have been sorted first by ID then by Age... which didn't happen. However, when I used "ORDER BY", there was no "sort" function in my exec plan. — LearnByReading 49 mins ago

bluefeet

5:53 PM

run away

One solution would be to normalize your database, why do you have separate tables for each product? Storing separate products in separate tables is a bad design. — bluefeet ♦ 2 mins ago

ypercube

It's the inverse of EAV (one table to rule them all).
One table for every piece of data.

2

Aaron Bertrand

Hey who's running what version of Windows here?

bluefeet

@ypercube that's why I said run away

billinkc

Microsoft Windows [Version 6.1.7601]

Aaron Bertrand

Just wondering when "Run as different user" appeared on the context menu for executables, and in what versions you have to hold Shift to see it.

billinkc

6:00 PM

Shift-right click has run as different user for my windows 7. I think there's a registry setting on whether that's visible but it's been too long since I've mucked with it

Marian

same as @billinkc, shift+right click is there

bluefeet

Bluefeet - This is an example of the query I have, I changed the table names, just named them productA etc — user3512530 5 mins ago

then you did it really bad

billinkc

6:31 PM

I don't expect there is an answer, but in SQL Server is there any record of queries that errored out?

Aaron Bertrand

brentozar.com/archive/2013/08/…

Severity 20+ are captured. Anything else - unless it happens to accompany something else that is tracked natively - you must track on your own.

swasheck

which queries

Kris Gruttemeyer

@AaronBertrand Win7, but I have Server 2012R2 and Win10 on VM

Kermit

can't believe this got downvoted

5

Q: Which device would work better for mining unicoins?

I found this infographic on mobile devices, and needed help deciding which device would work better for mining unicoins. I know that the "Galaxy the Unicorn" runs Unicorn Linux, but I'm a little skeptical because it was released in 2003. Will expandable memory be a problem?

discussion mobile-web unicoins april-fools

swasheck

the nerve

bluefeet

6:42 PM

@Kermit april fools attention

billinkc

@AaronBertrand Appreciated. I have no idea what sev the error was. And "the error only happens in Agent. Runs fine from my machine"

Kermit

@bluefeet two years old girl

bluefeet

@Kermit I know but the april-fools is getting activity today

JNK

@Kermit Wow is it 2016 already?

bluefeet

@JNK he does work with Vertica

JNK

6:48 PM

<burncream.jpg>

Kermit

only use .gif... absorbs better

Zane

7:30 PM

Yo yo yo.

Kermit

7:41 PM

speaking of which.. when are all of you going to buy vertica?

Zane

Tuesday.

Kermit

Woot woot

Zane

Actually Wells Probably used it somewhere.

bluefeet

in 6-8 weeks

Kermit

@bluefeet thank you

Lamak

7:56 PM

how can I do something impossible?

0

Q: SQL Seect Column if NOT Null

I have a Microsoft SQL table and let's say it has 10 columns. One or more of these columns may contain ALL null values. How would I construct a simple SELECT * query that excluded columns where ALL values are null?

sql ssms

billinkc

I think they want to exclude all the records where all the fields are NULL

Lamak

@billinkc nope, the other way around. He wants to select only columns where there exists some non null value

billinkc

You can probably get away with: SELECT * FROM TABLENAME WHERE COLUMNAME NOT LIKE '%NULL%'; — ryekayo 18 secs ago

My laugh was audible across the building

Lamak

@billinkc that's another guy that didn't understand

Zane

I wouldn't say that it's impossible. However it would be overly complicated and I don't know what the point would be.

Lamak

8:03 PM

it would need dynamic SQL. The impossible part would be that he wanted SELECT * and exclude columns

that's not a SELECT *

Zane

oh yeah.

bluefeet

@Zane get it together man!

your punishment is to spend an hour playing Stack Egg

Lamak

did anyone play the google maps pacman?

Zane

I played that for about 27 seconds.

bluefeet

@Lamak i had to stop since I'm at work

Lamak

8:06 PM

where else would you play it?

bluefeet

true

but I've got work to do, I can't play either

Andriy M

Can't play either Pacman or work?

Sorry, just messing

bluefeet

nice

billinkc

I find it amusing that the google maps pacman also shows up in their Ingress game

Zane

8:25 PM

Really?

billinkc

Lamak

nice

Zane

That's awesome.

A few points in Minneapolis were created by me.

dezso

10:11 PM

this smells

Column order is important. This is primarily to ease importing a somewhat large dataset from a web service. And to keep in line with the consistency of other tables, where there's a business practice to stick the record origination and modification fields in the last columns of the record. These fields have proved important to keep with the data instead of in a logging table. I could go into why these are important (and their location important), but suffice to say, they are. — vol7ron 8 mins ago

Transcript for

The Heap™ – Consultancy ©®