The Heap™ – Consultancy ©® - 2013-09-11 (page 2 of 2)

7:00 PM

@AaronBertrand yeah ... i had all NICs perfmon'd and they show throughput during the blank timeframes

there's something going on on the server that's crushing it

@swasheck Any number of things can cause a high CPU spike, but it means the same thing: Your server is doing a lot of work. What you need to do is find out what is working at that point. If it is SQL, do what Aaron said: Look at your Top SQL for that time frame.

FreshPrinceOfSO

@billinkc know a 5 minute crash course in setting up a data warehouse?

swasheck

yeah. at times i feel very confident in my findings but then i get pushback and i start second-guessing myself

thanks @MikeFal and @AaronBertrand

i appreciate it

Mike Fal

My current line of thinking would be to identify what statement is working its ass off in that time frame and review the query plans. You might have a bad plan. But I don't want to assume to much in the absence of detailed info.

swasheck

good plan, bad plan ... i'm the guy with the DBCC FREEPROCCACHE

Mike Fal

7:03 PM

Do not use the howitzer, use the scalpel. sp_recompile

Lamak

@FreshPrinceOfSO attach the database, load data, profit

FreshPrinceOfSO

@Lamak do you have a 2 minute crash course in attaching a database?

Aaron Bertrand

@MikeFal well you can also pass a specific plan_handle to FREEPROCCACHE, remember that sp_recompile affects entire objects while it may just be a single statement you want to eradicate

swasheck

@AaronBertrand yeah

Lamak

@FreshPrinceOfSO right-click on databases->attach->Add->choose your database file

swasheck

7:06 PM

what's really irritating is that the top CPU query during this timeframe is part of a tempdb process

i'm trying to convince the guy to use a computed column

because he pulls ALL ROWS into a temp table

runs a calculation

and then upserts back to the source table

Mike Fal

@AaronBertrand True. I tend to use sp_recompile, though, because I'm to lazy to dig out plan handles. :)

Aaron Bertrand

@swasheck we can't solve politics issues, but some advice: refactor, then show him how much better yours works.

swasheck

(unless that's a bad use case for computed column)

@AaronBertrand right ... i know that's my plan but i have to convince him that he's the problem :)

Mike Fal

@swasheck Can you build a comparative test in a test system? It's hard to argue with numbers, so if you can show how using a computed column will increase performance via logical reads, it will make a pretty strong case.

Aaron Bertrand

@swasheck how many times do you think you'll have to repeat this exercise before someone gets that? hint: involve other people in the discussion. One on one "you should do it this way" doesn't work well, but if you have a persuasive argument, one additional brain can dramatically improve your success rate.

swasheck

7:09 PM

@MikeFal yes ... but his computation logic is difficult to follow so i have to sort through it ... meanwhile we're being pressed for answers and "suboptimal code" isn't cutting it :) ... at any rate

i made inroads with his manager

manager wants to blame storage

storage says "not us"

i told him ... it could look like storage to you because these queries are doing billions of reads and writes in the span of this job

so anyway ... thanks for the help ... i'm just a bit gunshy because i'm working with some strong NY personalities and i'm the new guy here

@AaronBertrand you're saying i should go parallel with my recommendation :)

sorry ... i'm not trying to make excuses. i'm glad you guys are making recommendations and i appreciate it. it's helpful because it's confirmed many of the things i've already said so now i can go back and say, "hey ... i've done my homework."

also ... i kinda want to post the plan to answers.sp just for fun

FreshPrinceOfSO

@swasheck hey, you have the backing of internet people. that's some powerful shit

swasheck

@FreshPrinceOfSO i see you're back to your normal cantankerous self

FreshPrinceOfSO

@swasheck sorry... LEN returns greater than 10 don't understand

Lamak

@swasheck had to google that

*bad-tempered, argumentative, and uncooperative.*
"a crusty, cantankerous old man"

swasheck

@FreshPrinceOfSO it's your primitive optimizer

FreshPrinceOfSO

7:17 PM

@Lamak we'll im definitely not crusty

swasheck

answers.sqlperformance.com/questions/1151/…

Aaron Bertrand

WUT?

You cannot retore backups from Shared Locations over network. You need to copy it on you local drive. — Amit Ranjan 17 mins ago

swasheck

yoink

Aaron Bertrand

7:35 PM

@swasheck wow:

swasheck

@AaronBertrand blocked

Aaron Bertrand

OMG you have to deal with these queries and imgur is blocked?

swasheck

what are you highlighting?

estimated vs. actual?

UDF

Aaron Bertrand

Duration , CPU, reads, writes on the statement tree tab

swasheck

yes

Aaron Bertrand

7:37 PM

Like 300,000,000 reads - why is performance slow? <facepalm>

swasheck

i was not kidding on the billions

but the problem is not the query ... it's the subsystem :)

@AaronBertrand the response is "why did this suddenly happen and why doesn't it happen over the weekend?"

but i dont have the time to go through and reconstruct their job timeline and they're not willing to do it

Aaron Bertrand

@swasheck did you capture an actual plan over the weekend? Is this just a report or do those sub-procedures actually perform writes? If you run it again within a few minutes does it do the same thing?

(In other words, does running it on Friday change the way it might run on Saturday, because it is changing the underlying data.)

swasheck

@AaronBertrand i'm searching for it during the weekend run

Aaron Bertrand

It's funny how month-to-date reports get slower by the 30th

swasheck

@AaronBertrand this is a pull everything into a temp table, run some calcs, and then write the changes back to the source table

not a report

it's a "batch process"

Aaron Bertrand

7:41 PM

ok but is there a reason why maybe on one day it would affect more rows than on another day?

bluefeet

is this a keeper on DBA? or should it be kicked to SO

0

Q: SQL output formatting

I want this: "ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID" "abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos" but I get this using the following SQL: '"'||"ACCOUNT_ID"||'"'||','||'"'||"MAJOR_VERSION"||'"'||','||'"'||"...

swasheck

@AaronBertrand ah ... i've not taken anything as an accusation :) ... he said that there's no difference

Aaron Bertrand

that's all I was getting at - I wasn't accusing the query of being a report

bluefeet

I vote to SO

Aaron Bertrand

@bluefeet I don't think we should punt it to SO in that shape

bluefeet

7:42 PM

@AaronBertrand vtc to close?

Aaron Bertrand

It's not a keeper IMHO, but I'm still not sure what we should be doing with simple query questions.

It should be edited to at least make visual sense.

bluefeet

I don't even know where to start editing that thing

Aaron Bertrand

It's mostly just a formatting thing, I edited it

Now, why he's using all that '||","||' junk in his query is another question altogether.

I presume he's trying to concatenate but has no clue how to do it.

FreshPrinceOfSO

@swasheck jadito.us/wp-content/uploads/2013/09/rVl7n.png

is what @AaronBertrand posted

swasheck

@FreshPrinceOfSO fang q

Aaron Bertrand

7:46 PM

Has there been a meta post about imgur being blocked?

15

Q: Hosting options for people that have Imgur blocked by their ISP

Imgur is blocked by two of the private ISPs we use. Is there a workaround without involving tunnelling? Bear in mind that Filmot (an Imgur mirror) and Flickr are also blocked.

support imgur

bluefeet

@AaronBertrand are you having issues?

Aaron Bertrand

@bluefeet no, I posted an image for swasheck and forgot that imgur is blocked for him

bluefeet

@AaronBertrand see it would have been helpful if I was paying attention :|

Lamak

@AaronBertrand It's blocked for me too. I stopped using a proxy to circumvent the restrictions here

Aaron Bertrand

@swasheck how about now?

Balls

swasheck

7:51 PM

well if you posted a pic of balls then i want that to be blocked

Aaron Bertrand

Don't lie

Zane

@AaronBertrand Sounds like the answer is TS.

swasheck

()&(@*#HD(#@)(*&#@$(#@&()J*#)(#XN*(X)#@(*&$)()#*&#)(@NX&*#@^)$&*)DN@XC

Weekday:

EXECUTE Performance.dbo.p_evaluate_performance @whether_persist_returns=1, @evaluation_frequency=D, @what_do_the_dates_represent=asofdate, @evaluation_order_lower=2, @portfolio_selection_criteria=2, @modification_start_date=@date

weekend:

EXECUTE Performance.dbo.p_evaluate_performance @whether_persist_returns=1, @evaluation_frequency=M, @what_do_the_dates_represent=cre_date_mod_date, @evaluation_order_lower=2

Seth: "Hey dev ... are you SURE there's nothing different????
Dev: "Nope!"
SQL Sentry: "I beg to differ"

bluefeet

@AaronBertrand I guessed

0

A: SQL output formatting

I am going to go out on a limb here and try and answer even though you haven't provided a lot of details. It looks like you want to create a comma-separated list of the columns and then the values below it but in sqlplus you are getting the column header as the actual SQL string. If I were to ...

Lamak

@swasheck seems like there are grounds for firing someone

Aaron Bertrand

8:03 PM

@swasheck another <facepalm>

swasheck

@Lamak not sure if it's parameter sniffing.

because it seems like a new plan is generated

Aaron Bertrand

Gee, I wonder why performance is different - oh, I know, because they're DIFFERENT QUERIES

swasheck

i'll have to confirm that with the SQL Sentry plan cap

Aaron Bertrand

@swasheck or a different plan is used - without seeing the body it's hard for us to guess which

swasheck

sad trombone

Lamak

8:05 PM

@swasheck you should charge him for the hours lost figuring out that he was running different queries

swasheck

> A query plan was not collected at the time of statement execution

Lamak

what does this mean?

Just noticed that, seems like a caveat to include rather than a change to be made. — Goat CO 5 mins ago

oh, nevermind, I got it now

@AaronBertrand are you writing your comments in cursive on purpose?

Aaron Bertrand

@Lamak yeah I was - trying to differentiate when I am complaining about something that is peripheral to the technical accuracy of the answer. Not working?

Anyone have 2005 books online installed on their machine?

Lamak

@AaronBertrand working, just wanted to know for sure

what is a "negative 0"?

@Lamak negative 0s become positive. — dirtyw0lf 7 mins ago

swasheck

i was wrong

different instance of the query

but there is same exact batch

Lamak

8:13 PM

@swasheck oh. Well, it was fun to rant about your dev anyway

swasheck

yeah

but he uses the same proc

for multiple things

the weekday plan is pulling many many many more rows

Aaron Bertrand

So nobody has 2005 BOL? :-)

FreshPrinceOfSO

i haz more problems

-1

Q: first data row skipped in import

I'm using a XML format file to import a CSV file, and the first data row is getting skipped. I can't figure out why. Format file <?xml version="1.0"?> <BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <RECORD>

sql-server csv import

swasheck

@AaronBertrand ?

Aaron Bertrand

Wow, a down-vote before it's a minute old

FreshPrinceOfSO

8:15 PM

@AaronBertrand apparently it's a bad question

Lamak

@AaronBertrand nope

swasheck

@AaronBertrand wasnt me

Aaron Bertrand

4 mins ago, by Aaron Bertrand

Anyone have 2005 books online installed on their machine?

swasheck

@AaronBertrand didnt see it

and nope

bluefeet

@AaronBertrand i do not

swasheck

8:16 PM

i always thought that the SQL 2000 BOL was funny because i'd only ever used it as an offline .CHM

bluefeet

you would think I would considering we are way behind the times tech wise, I mean we still are running xp machines

people must not like you @FreshPrinceOfSO

Lamak

@FreshPrinceOfSO this will sound stupid and all, but what if you change FIRSTROW = 2 to FIRSTROW = 1?

FreshPrinceOfSO

@bluefeet apparently not.

Lamak

this made my day:

Okay, so he's a little terse but unless I'm being incredibly stupid (and I know little of SQL Server's internals so it's possible) I have no idea why this is being downvoted so much. It's a valid question with more than enough information to test and reproduce the problem. (We even agree on the tags which is highly unusual) — Ben 1 min ago

FreshPrinceOfSO

@Lamak

COLUMN1                COLUMN2
---------------------  ----------
COLUMN1,COLUMN2  "ABC  ABC123456"
"TNT                   TNT123456"

Lamak

8:20 PM

@FreshPrinceOfSO weird

FreshPrinceOfSO

doc states

Specifies the number of the first row to load. The default is the first row in the specified data file. FIRSTROW is 1-based.

Aaron Bertrand

Seems like a terminator issue. Make sure the first row actually has a \n, it could be something else.

swasheck

no. THIS is a terminator issue

2

Lamak

yeah, it does seems like a teminator issue

maybe is because you are using '","'

@FreshPrinceOfSO do you need to include the column names if you are defining them on the XML?

and if so, what happens if you change the first row of your csv with "COLUMN1","COLUMN2?

FreshPrinceOfSO

As @AaronBertrand suggested, I removed the header row

and changed FIRSTROW to 1 and it loads fine

Aaron Bertrand

8:27 PM

@Jaxidian How can anyone say "without making sure everybody was aware of this" - do you want a postcard and a phone call in addition to the list of deprecated features that have been announced since 2008? I believe this was listed in the 2005 documentation as well, but that documentation set has been retired. People have also blogged about it. — Aaron Bertrand 1 min ago

FreshPrinceOfSO

8:37 PM

alright, too much sql server for one day

swasheck

so which is preferred?

Aaron Bertrand

@swasheck which what?

swasheck

@AaronBertrand haha ... yeah

typing that

sp_recompile N'schema.proc';
exec schema.proc;

or

billinkc

SQL Saturday in Connecticut - is that like even a real place?

swasheck

exec schema.proc with recompile;

(just to eliminate parameter sniffing ... not as a longterm solution)

Zane

8:52 PM

@billinkc It's a rich suburb of Boston from what I've been able to tell.

bluefeet

question is do I delete my answer after this comment?

Thank you bluefeet. It's very similar to what I ended up with. Can you view my code and expand on it possibly? I want to know now how to change the NULLS to either empty or some other text to make the results easier to read. Right now I have MANY NULLS and it's cluttering the results. — user2766661 13 mins ago

@billinkc hey I grew up there so yes it is real

billinkc

@bluefeet But you are not real. You are a machine within the matrix designed to answer pivot and unpivot questions

bluefeet

@billinkc but I am real, I really am

Aaron Bertrand

@swasheck why not - as a short term solution - alter the procedure to have OPTION (RECOMPILE) on the bad statements? Isn't that easier (and less of a blunt hammer) than changing all the calls to the procedure?

swasheck

@AaronBertrand good point.

Aaron Bertrand

8:56 PM

See I earn my money

swasheck

i was just curious about this

> Furthermore, the method does not work on SQL 2008 and later, where the implementation of OPTION (RECOMPILE) is more resaonble: SQL Server compiles the query as if all variable values are constants. (Confusingly, this does not apply to all builds of SQL 2008; for a while Microsoft had to revert back to the old behaviour because of a critical bug.)

from The Sommarskog Article

was that reverted in R2?

bluefeet

@billinkc If I'm not real then @JNK isn't real either since he says he lives in CT

JNK

my gravatar is a sentient robot

billinkc

Indeed. Classic mistakes in AI programming

JNK

of course I'm not real

Aaron Bertrand

8:58 PM

@swasheck I've thwarted parameter sniffing this way, so whether it works the way it did in 2005 or the way Erland says, it still worked, I kind of disagree with "does not work" in that sentence.

@swasheck at most I would say "does not work the same way" - it can still certainly be effective.

swasheck

@AaronBertrand fair enough. your employer is sending me 'round a racecar track so i'm going to trust you :)

Aaron Bertrand

@swasheck you're going to need to test it, regardless of what I or Erland or Paul White or anyone else says...

JNK

@bluefeet Nice try, robot!

swasheck

@AaronBertrand blind faith doesnt work in IT?

JNK

@bluefeet I actually did have that experience just after the shooting last year

I foolishly responded to someone who tweeted some garbage about there not being funerals so it was fake

Dr. White

hello and goodbye

goes home

bluefeet

9:00 PM

can I run away screaming now? I guess my answer isn't good enough I have to translate their code for them

Thank you bluefeet but I'm a little confused on how to translate your code into mine. Can you look at my code and give me an example from that on how to use coalesce. I've been looking at many examples besides yours and I still can't get it to work with my code. See my code below where I answered my initial question. Thanks again! — user2766661 1 min ago

JNK

SQL Kiwi has a PHD in my heart

Zane

@JNK ??

swasheck

@JNK did you deleted it?

Aaron Bertrand

Are people for real?

3

Q: 4000 character limit in LIKE statement

I have been getting an error in a previously working stored procedure called by an SSRS report and I have traced it down to a LIKE statement in a scalar function that is called by the stored procedure, in combination with a 7000+ NVARCHAR(MAX) string. It is something similar to: Msg 8152, Leve...

sql sql-server ssrs-2008

Ok, gotta go, enough insanity

Zane

9:16 PM

@AaronBertrand Who the eff is upvoting this question.

bluefeet

@Zane was your earlier tweet in response to something in particular?

Zane

Yeah. Some article I read about some 16 year old getting shit on by her fellow students about being a girl in a programing class. Also the massive volume of no girls on the internet/ girls don't play games has to be fake comments I see.

swasheck

@Zane you forgot

#GFY

Aaron Bertrand

@JNK you deleted Akash/NA.'s comment about wasting his time? Now it looks like I'm being a jerk unprovoked. :-)

Zane

@swasheck I didn't I just thought I had enough cursing in the post as it is.

As much as I don't like getting on flame wars on the internet I've decided to start berating every instance I see of that bs.

swasheck

9:28 PM

blaze of glory

bluefeet

sigh

I'm using an internal database within my company. — user2770412 1 min ago

swasheck

it's been a long week for a wednesday

BOOYAH!!! Me too!!! TWINSIES!!! @user2770412 — swasheck 47 secs ago

Zane

@bluefeet Brilliant.

bluefeet

um what?

0

Q: how to shrink database into 4G chunks?

Hello how to shrink database without open any port just only open one way connection to catch Wifi chunks? Im tring to open one port without load data buffer , only use stored procedure to reduce loads ... Thank u

mysql sql sql-server

Zane

@bluefeet You don't really know what you're talking about do you fella? You're just mashing on your keyboard and hoping it makes sense. lol

@bluefeet I want that question to be a SO poster.

@swasheck run a deep slant route and catch this wifi chunk. — Zane 9 secs ago

swasheck

9:48 PM

@Zane we're having a nice personal conversation at the expense of this poor question

Zane

Poor question is the key phrase.

swasheck

aaaaaaaaaaaaaaaaaaaaand there's an answer

billinkc

Is that answer even valid or did they just spew some gibberish?

Zane

@billinkc That's the all time best answer I've ever seen.

Mike Fal

@swasheck You should have made a comment on the color of Julia Robert's underwear

swasheck

9:53 PM

@MikeFal you just shoulded all over me

hm

Mike Fal

Indeed

swasheck

what're the odds ... no windows updates for server 2012 r2

;)

Mike Fal

So it's raining pretty significantly here.....and I'm supposed to play a brass quintet gig at a picnic tonight.

Methinks this won't actually happen.

swasheck

@MikeFal s'posed to rain through sunday, apparently

Mike Fal

Welcome to the rainy season in Colorado. It lasts about a week.

swasheck

10:00 PM

@MikeFal yup

hot->blazingly hot->fall->indian summer->winter

in as many weeks

swasheck

10:14 PM

ReFS here i come

bluefeet

Since the other answer was bad mouthing PIVOT, I had to answer

0

A: Need to select table data into distinct columns based on a date in a row

Just because I love pivots, I will show you how this can be done using the PIVOT function. In order to get the result with the PIVOT function you will first want to UNPIVOT your multiple columns score, notes and createdate. The unpivot process will convert the multiple columns into multiple row...

Mike Fal

10:28 PM

Well shit. Look who's in my time slot at the Summit currently. Paul Randall in one room, SQL CAT in another.

billinkc

they're published?

James

WHERE YEAR(sometablealias.SOME_DATETIME_COLUMN)*100+MONTH(sometablealias.SOME_DATETIME_‌COLUMN) = YEAR(GETDATE())*100+MONTH(GETDATE())

WHY??

billinkc

Some folks just want to watch the database burn

3

@MikeFal Spare a man a link?

ypercube

10:57 PM

This looks like a bug in the FIRST_VALUE() or OVER implementation:

1

Q: Unexpected results when using FIRST_VALUE() in SQL Server 2012

When I use FIRST_VALUE on a data set that I construct by hand I get one result, and when I use it on a data set that results from a left join, I get a different result - even though the data sets appear to me to contain the exact same data values. I've reproduced the issue with a simple data set ...

sql-server tsql sql-server-2012 aggregate-functions window-functions

bluefeet

hey all I know this is off-topic but do any of your use a time tracking software? If so, which one?

Tim Stone

We apparently started using Harvest, but I can't tell you how it is since I haven't bothered to log in to it.

Mike Fal

sqlpass.org/summit/2013/Sessions/Schedule.aspx

Oh, this is so good and so true.....

RT @AngryPets: Developers. They can keep up with 30 different JavaScript frameworks a week - but can't be bothered to learn how Transaction…

Tweeted by Mike_Fal on September 11, 2013 at 11:06 PM

bluefeet

@TimStone thanks but I think that might get too costly. we have a user base of 700 people and their unlimited is for 10 users @ $99 a month with an additional $10 per user

Phil

11:49 PM

@bluefeet I'm always* right!

*Terms and conditions may apply

4

bluefeet

@Phil I knew I shouldn't have tried to answer an oracle question. thanks for the correction.

Phil

@bluefeet You answered it ok :)

Transcript for

The Heap™ – Consultancy ©®