The Heap™ – Consultancy ©®

billinkc

03:20

I'll just leave this here

►

swasheck

03:34

ARGH! this is why i'm a dba. i suck at data analysis

billinkc

Trying to make heads or tails of your refs numbers?

swasheck

yeah

billinkc

I'd offer to help as helping's in my DNA but me and the maths never got along

swasheck

so many permutations

billinkc

Which is a pity as I'd like to pursue projects that are data driven

swasheck

03:40

128 potential charts

oh well

i suppose this would be good as SSAS or something

billinkc

Ummm, maybe?

swasheck

but i dont even know how to do that. meh. cherry-pickin' time

ConcernedOfTunbridgeWells

10:17

Morning all

Marian

morning

Markus

11:40

Hey hey!

Markus

11:50

can I make tables in one schema invisible to a certain user in the same database?

Andriy M

12:18

@Markus You mean, not just prevent the user from accessing the tables but also prevent him/her from seeing their names

Markus

yep, I think I solved it by denying all the securables

I'm setting up a sync operation, and I want to hide the "_tracking" tables for the ordinary user

when setting up the provisioning that user must have CREATE TABLE and some other rights, so I made the "sync" user a db_ddladmin for that database, is that the "correct way"?

JNK

12:48

morning folks

Markus

hmm, ok so that wasn't such a good idea in the end (to deny all securables)

morning

JNK

would denying view_definition keep them from seeing the table exists?

Markus

I'll try it.

Yes, that did the trick! thanks

JNK

no problem

view_definition affects metadata visibility

wasn't sure if it would prevent seeing the actual object or just the columns etc

ConcernedOfTunbridgeWells

13:04

From the mines-bigger-than-yours department:

www8.hp.com/us/en/campaigns/workstations/z840.html

Up to 36 cores, 2TB of RAM.

I feel slightly inadequate.

Markus

13:41

What, it has no floppy drive?! :P

bluefeet

14:24

It's really sad when you've been working a week to fix an issue expecting to see a large improvement in something, only to discover that it was only about a 3% improvement :(

JNK

@bluefeet sometimes 3% is a lot

also I've done that and had it get worse

in which case I quietly undo my changes and back away without telling anyone

bluefeet

thankfully it didn't get worse. But this is supposed to be a improvement in a billing system and 3% is way less than we were expecting

Lamak

@JNK and sometimes people start saying that now it works great

JNK

14:41

@Lamak true

I joked with my boss one time that we should put waits in some of our code when we first release something

so it runs unnecessarily slow

Then a few weeks later we remove them to "optimize" it and look like heroes

Lamak

genius

Kermit

15:04

@ConcernedOfTunbridgeWells you should run sql-server on there.

@JNK @bluefeet especially if that 3% is executed 1 million times

Emilio Gort

hello, does sql server 2012 or 2014 support json, or something like OPENXML?

Kermit

@EmilioGort did you google it?

@JNK "joked"

Emilio Gort

yes but I couldn't find anything

google.com/…

Kermit

What does the first result say?

Emilio Gort

it's a request I think

Kermit

15:06

A connect item that says "won't fix" .. meaning the feature was requested and Microsoft won't add the functionality

bluefeet

@Kermit snark much?

Kermit

@bluefeet just a gentle push to do research themselves

Lamak

you are a gentlefrog

ConcernedOfTunbridgeWells

@Kermit It would be a great box to do D&P work on.

Kermit

@ConcernedOfTunbridgeWells D&P ??

@Lamak ribbit

ConcernedOfTunbridgeWells

15:11

@Kermit Discovery and profiling. Basically loading the data into a database to poke around it and see what breaks.

Kermit

@ConcernedOfTunbridgeWells okay had a different DP in mind

ConcernedOfTunbridgeWells

@Kermit Is this something I shouldn't look up on Urban dictionary from a work computer?

Lamak

@Kermit now you are not a gentlefrog

Kermit

@ConcernedOfTunbridgeWells i use my work computer for mostly everything.

ConcernedOfTunbridgeWells

@Kermit Remind me to bring my own keyboard.

Kermit

15:13

@ConcernedOfTunbridgeWells i work from home :)

ConcernedOfTunbridgeWells

@Kermit Still stands.

Kermit

@ConcernedOfTunbridgeWells let me know if you want to borrow my keyboard

ConcernedOfTunbridgeWells

@Kermit I'll be sure to.

Markus

has anyone here worked with sync fw 2.0?

billinkc

Worked no, saw a demo and said I should look at that, yes

swasheck

15:20

@EmilioGort eh? what are you tryin to accomplish

billinkc

The story I heard about JSON and SQL Server is to use CLR for parsing

Markus

my problem now is that i get a

The current operation could not be completed because the database is not provisioned for sync or you not have permissions to the sync configuration tables.

And I don't think that it's permissions, since I have tried with sa

Andriy M

"Not provisioned" then, if I'm following the logic.

2

swasheck

^^^ lulz

billinkc

Seen msdn.microsoft.com/en-us/library/azure/hh667328.aspx ?

Emilio Gort

15:36

@swasheck I wanted to pass a json as a parameter to a store procesdure instead of xml

because I have the json, now I have to convert the json to xml

Markus

well the thing is that I have gotten the provisioning to work and all, and then I decided that I didn't want all the _tracked tables in my schema, so I created a new schema sync added the c# line serverProvision.ObjectSchema = "sync" (as described on many pages) but then when trying to provision the client db "from" the server I get this message

swasheck

old school like old english in that brown paper bag

Markus

I have also noticed that when I don't have the ObjectSchema the schema_info, scope_config and scope_info gets placed in the dbo schema (even though that's not the schema for the other tables, nor is it the default schema for that user...

Markus

15:56

hmm I'm not solving this now.... have a nice weekend

billinkc

16:10

I'm waiting for the next round of testing, but it appears that adding an explicit tablock to my insert statement reduces the time by about 2 minutes (13 vs 15) 64M row insert into a 231M row target table

JNK

@billinkc have you considered batching as well?

billinkc

Of course I have nt

JNK

lol

Maybe try that as well

Really reduces log and tempdb pressure

I can help if you need a template

I've used it a ton for ETL type stuff between tables

billinkc

Please do. I think the challenge will be identifying something useful for marking as loaded

JNK

Do you get EVERYTHING from the source table?

Like do you have some sample code you can share?

billinkc

16:14

Yup

JNK

ok so you want every single record

Is there a CI on the target table?

billinkc

Yeah, it's an accounting review table that they can diddle with until they push the big red button

JNK

the target is clustered though?

billinkc

It is

swasheck

@JNK we're interviewing data warehouse developers in the city, now

JNK

16:15

@swasheck in NYC?

@billinkc ok sample code coming up

swasheck

yes

JNK

DECLARE @BatchSize int = 100000
DECLARE @LastPK int

WHILE 1=1
BEGIN

INSERT INTO
dbo.TargetTable WITH (TABLOCKX)
SELECT TOP (@BatchSize)
*
FROM
dbo.SourceTable s
WHERE
s.PrimaryKey NOT IN (SELECT PrimaryKey FROM dbo.TargetTable)

IF @@ROWCOUNT < @BatchSize BREAK;

RAISERROR ('Did another batch!', 0, 0) WITH NOWAIT

END

@swasheck do you know where the office is?

NYC is approx a 2.5-3 hour commute for me

if it's downtownish

@billinkc if you have an incrementing PK you can use that too

much easier

swasheck

@JNK same place it's always been ;) ... manhattan ... just a quick train ride in, amiright?

JNK

@swasheck Well its 35 mins for me to get to the train station

billinkc

@JNK I at least have sequential guids

JNK

16:19

and that train station is the end of the harlem line

@billinkc oh if its a guid no dice on comparing to a variable

billinkc

Have you thought about moving to Harlem

JNK

I don't think you can use <> with a guid

swasheck

@billinkc Angel of Harlem

JNK

so from that station to grand central is like an hour 20ish

then to get downtown is another 30-40 by train

And yeah I have considered moving to westchester

Would make my current commute a whole lot easier

Right now for me to get to my job I drive 80 miles round trip

swasheck

@JNK holy crap!!!!!!!!!!!!!!!!!!!!!! that unsane

JNK

16:21

@swasheck And where I work is a ways out of the city

I have a coworker who lives in harlem and he has an hourish train ride

So like I told the nice folks at Xero, I could do like 1 or 2 days max in the city

and then WFH

billinkc

In theory, if I was on public transportation, I could work during the commute but that presumes I'd have a seat. I've seen enough NYC based teevee shows to know that it's butt to butt on the transit systems

JNK

@billinkc I could actually do that on the long train ride

Since the southeast station is the end of the line, the train starts empty

swasheck

anyway - it's a good company, they pay well and the NYC office is nice. the people are a mixed bag, but just wanted to keep you in the loop of how things are progressing. we're losing an idiot to teradata so it's a small loss and i thought i'd float your name if you wanted it. i dont think they have a WFH policy that goes more than 1, maybe 2 days

JNK

When we take the kids into the city we go from there too

@swasheck TBH I'm not unhappy where I am, The DBA™ aside

billinkc

Child labor laws not apply?

JNK

16:23

@billinkc Well when we go on the weekends

billinkc

Makin' 'em work 7 days a week? Good on ya ;)

JNK

When you have a 2yo a 4yo backpacks and strollers its a LOT easier if you can get a set of seats together

swasheck

@JNK i know you're not. i'm unhappy with our devs and it'd be killer to get to work with/learn from you. so it's purely self-serving

JNK

@swasheck well see what the WFH situation is

deleting in 3...2...1

Lamak

@billinkc didn't read the whole post but this question has some interesting points about batch inserting with sequential GUIDs

JNK

16:25

I think all the regulars are owners anyways

Lamak

@JNK you are not allowed to leave The Heap

JNK

@billinkc try that code it may help, I tend to tweak the batch size and see what happens

@Lamak Not planning on it :)

you learn a lot of stuff in here

It also helps to settle arguments when you can say "Well I asked Paul White and he said..."

poor Paul, did you guys see his pics?

swasheck

that's lost on our devs, though. they're more theoretical DW ftards.

@JNK yeah. that sucks

Lamak

@JNK I saw one, it seemed like he hit himself vary hard

JNK

Time and ice have not improved the look! http://t.co/gVYs5BCdsw

Tweeted by SQL_Kiwi on November 13, 2014 at 11:04 PM

Lamak

16:28

and one of the tweets he got said that he would need a bubble for the next trip....

JNK

ugh come on twitter

Kermit

@JNK what is that

JNK

@Kermit had an issue with oneboxing

Kermit

@JNK i don't follow

Lamak

@Kermit that is Paul White

Kermit

16:33

@Lamak i gathered that much.

JNK

he woke up and hit his face on a table in his hotel

Kermit

sure he did. we all know it was a kangaroo

JNK

well he was in Las Vegas at the time

Mike Fal

They don't have kangaroos in NZ

JNK

so possibly

Kermit

16:33

@MikeFal no evidence

@JNK my thoughts exactly

Mike Fal

@PaulWhite is in Vegas? Bummer. I only managed to run into @AaronBertrand

Kermit

@MikeFal maybe @AaronBertrand "bumped" into @PaulWhite

JNK

@MikeFal He gave some talks at SQL Intersections

I think on parallelism

Both sessions were at the same time too, it was very meta

Mike Fal

Ommmm

The zen of parallelization

I'm so ready to go home at this point

JNK

So what all did you do, @MikeFal

PASS, you do intersections too?

Gonsalu

16:51

@JNK If you use TF610 you can get minimally logged new page inserts into a CI

Assuming simple or bulk-logged recovery model

billinkc

Full recovery model so that won't be applicable in this case but thank you Gonsalu

JNK

oh misread

Gonsalu

@billinkc No problemo

chrisadkin.org/2014/10/27/…

3

Found this post really interesting

billinkc

We have the mighty 2008 R2 here

Gonsalu

Really need to start messing around with WPA

In case anyone is interested in getting started with WPA as well, I really liked seeing these introduction videos from Bruce Dawson: randomascii.wordpress.com/2014/08/19/…

billinkc

17:03

JNK, would you expect a plan like that with your batch load pattern? Testing two different approaches for the load, one from heap, one from table

Marian

@Gonsalu ha, that's really nice

billinkc

@Marian Having lunch with my Romanian coworker, any fun things I can say to mess with him?

Marian

@Gonsalu the problem is those .etl files are so big when using verbose :) it can get to few gigs in size really fast

@billinkc heh, not funny things, but sad ones. You can ask about the presidential elections that will happen this weekend

Gonsalu

@Marian WTL for Windows 8 / Server 2012 introduced .etl compression which reduces its size a lot

@billinkc sqlblog.com/blogs/paul_white/archive/2013/02/01/…

billinkc

I assume the corrupt, fake degree or plagiaristic person will remain in power?

Gonsalu

17:11

Like relevant xkcd post, it seems there's always a relevant Paul White post, lol

5

billinkc

And off to lunch

Marian

17:29

@billinkc I really hope not. But, as a group, we're not that smart.

@Gonsalu when talking about perf tuning? of course :). He wrote the book.

Gonsalu

@Marian true :)

Mike Fal

17:44

@JNK Mostly meetings with AWS reps. Lots of NDA stuff.

Saw the presentation on Aurora, also a couple other sessions here and there. Pretty good conference. Apparently Skrillex DJ'd the party last night.

swasheck

@MikeFal so did you just come home for the weekend and then head back out?

hello. haters. damnn y'all mad

JNK

@billinkc I'm a little confused about all the TOP operators in there

Mike Fal

18:12

@swasheck yeah

swasheck

@MikeFal that's nuts

Mike Fal

At least I got to go home. @AaronBertrand went straight to Vegas after Summit.

swasheck

yeah. when did you head out?

Mike Fal

Monday PM

Go home tonight

swasheck

yowsers

Max Vernon

18:37

can anyone tell me why a linked server would have the "Data Access" property set to false? What would be the point of having a linked server that can't access data?

JNK

@MaxVernon Can you still run stored procs?

Max Vernon

@JNK hmmm... I'll assume that's the deal. I don't have the ability to really test it on the instance in question.

mostly just curious

JNK

I thought data access was required for actually running DML statements directly against the objects

but I'm not a DBA I just play one on the innertubes

Well you can set up a linked server pointing back to your local instance and test it that way too

I am fairly certain you can have a linked server pointing back at itself

@MikeFal you still around?

Have a PS question

Kermit

@JNK playstation?

JNK

@Kermit Yep

I'm trying to see if there's a technique or tool I can use to identify powershell functions that refer to variables outside their scope

billinkc

19:10

@JNK yeah, that was the plan when I had left the maxdop = 1 in there....

Still not so good though, either heap or table source

Oh well, my give a damn is on zero so I'm calling it good enough

Zane

20:01

I know I'm behind on this conversation as I haven't made it in here today but it appears you are scanning to much.

billinkc

Moving all data from Source to Target

Mike Fal

@JNK Hey man, sorry. I'm here

Sorry, moving in and around the convention

@JNK I'm not sure of a way to identify what you're asking (functions refering to variables outside their scope). The only thing I can think to do is to post a question on powershell.org.

(forums, so primitive)

JNK

@MikeFal OK I may try that

I have a 2k line library script I've been tasked with cleaning up for scoping issues

Mike Fal

Ouch.

What's your ISE? Any tools within that?

JNK

powergui

and not really for this kind of thing

or maybe...I may check

Mike Fal

20:14

I need to check out the Sapien tools. I keep hearing good things about them.

JNK

yeah I have too

Mike Fal

Ok, time to wrap up here and head to the airport. I'll see you guys Monday.

Max Vernon

anyone know how to query a system dmv (or something else) to see if the 'sa' account is locked? someone ran a process against ALL of our sql servers that locked the sa account on some of them. it seems sys.server_principals doesn't have a column that describes the account being locked. It has 'is_disabled' but that doesn't seem to apply.

billinkc

The sa account is just a sql construct, isn't it?

Or would it be the account that the service is running under that is locked out? That's a bit in AD

JNK

@MikeFal have a good trip home

@MaxVernon is it an actual sql login named 'sa' ?

swasheck

20:28

@MaxVernon select LOGINPROPERTY('sa','islocked')

Max Vernon

@JNK the real 'sa' login that is installed when you choose to have mixed authentication

@swasheck ahhhhh.... looks promising

JNK

so principal_id 1

Max Vernon

@JNK yep

@swasheck thanks very much - that works.

swasheck

flabulous

gcaptain.com/u-s-navy-deploys-first-laser-weapon-persian-gulf

JNK

I'd been reading about the development of those lasers

it's pretty awesome

swasheck

20:37

seems neat

JNK

20:56

Ships are a good use for those because they have excess power normally

and the lasers suck a lot of juice

They tried mounting one similar on a jet

like a 747 type plane

most of the cabin was chemical energy storage

billinkc

I want a non selfdestructing rail gun

swasheck

how's it feel to want?

billinkc

It leaves me wanting

JNK

those are pretty cool too

related interesting reading - rocket sleds

they used to test high speed ejection seats in those with live pilots

going like mach 3 on a sled on railroad tracks with a rocket strapped to the back

one of the main test pilots has all kinds of retina damage

billinkc

I was just about to comment about tearing your eyeballs out

JNK

21:03

John Stapp

John Paul Stapp, M.D., Ph.D., Colonel, USAF (Ret.) (11 July 1910 – 13 November 1999) was a career U.S. Air Force officer, USAF flight surgeon and pioneer in studying the effects of acceleration and deceleration forces on humans. He was a colleague and contemporary of Chuck Yeager, and became known as "the fastest man on earth". == Early years == John Paul Stapp was born in Bahia, Brazil, a son of Reverend and Mrs. Charles F. Stapp, American missionaries. He studied at Brownwood High School, Brownwood, Texas, and San Marcos Baptist Academy in San Marcos, Texas. In 1931, he received a bachelor's...

he withstood up to 45g

on a rocket sled

I may have confused the mach3 thing though

However he did go 632mph on a sled

fastest unmanned rocket sled was 6k mph (mach 8+)

swasheck

ugh. i am so dumb

thanks, @billinkc

billinkc

21:19

ANYONE COULD HAVE DONE THAT

swasheck

mmhmm

billinkc

It was probably tim stone

swasheck

Kermit

@swasheck why is that guy holding a case of urine

swasheck

great question

JNK

21:31

KidneyStone Light

swasheck

22:28

anyone still here?

billinkc

Will you accept anyone?

swasheck

anyone

just stumbled across this ... looks 'shopped

dailytelegraph.com.au/news/…

billinkc

Releasing it on the eve of the summit, warships "testing" their operational range. Seems totally legit and not a stage play. At all

swasheck

none whatsoever

billinkc

I mean, I'm sure they just happened to have their satellite pointing right there at the time and they were shooting. Those things are cheap and so they just have them mapping the entirety of the world all the time

I'd be interested in hearing Horner's analysis of it

swasheck

22:35

well send it along to him. posting to twitter is blocked here

Transcript for

The Heap™ – Consultancy ©®