The Heap™ – Consultancy ©® - 2014-05-01 (page 1 of 2)

Aaron Bertrand

12:07 AM

@ypercube WiFi is something the establishment can (and has) turned off.

Mike Fal

12:21 AM

@AaronBertrand Any good ways to watch NHL online?

swasheck

7:00 AM

interesting stat. after the late games ... the home team in game 7 of the nhl playoffs actually has a losing record

JNK

12:21 PM

@AaronBertrand that explains it

I'll re-register with work email

JNK

12:42 PM

woo I am getting SQL Sentry

(trial)

Aaron Bertrand

@MikeFal sorry missed this. I only know of things like NHL GameCenter Live and NBC Sports Network has something similar. Not sure how much they cost beyond minimal access, and not sure they work where you are (like Hulu, Netflix etc). I've come to rely on my Slingbox when traveling - works very well.

@JNK sorry about that. Fooled me too. We're going to fix it.

JNK

@AaronBertrand it makes total sense as a restriction

Since you're here, does it matter where I install the monitoring service?

Aaron Bertrand

I don't know. How do we keep track of the list of "free" e-mail domains?

JNK

I'm presuming NOT on one of the SQL Servers is the standard

Aaron Bertrand

@JNK No, just not on one of the monitored servers. How many servers do you intend to monitor?

JNK

12:51 PM

2

we have an application server I'm planning to put it on

is the monitoring service resource intensive?

Aaron Bertrand

It takes minimal memory / CPU. So if you're monitoring < say 20 servers it can probably live on the same machine as the database.

JNK

oh ok

well I'll put it here anyways

Aaron Bertrand

@JNK very low resource. You can see the charts in my overhead study

JNK

and is there an issue with where I put the database?

i.e. on a monitored instance?

Aaron Bertrand

It's a little different now because we've added some functionality, and I plan to re-invest in an updated study this year

JNK

12:53 PM

cool

Aaron Bertrand

Most importantly: the database needs to be on a server that is fairly resilient to network blips etc. between it and the monitoring service.

JNK

ok

Our dev SQL server is on the same VM host

so I'll put it there

Aaron Bertrand

It can be on a monitored instance, but if you are collecting a high number of databases / database files, have low thresholds for top SQL, or have a ton of indexes and have frag man turned on, it can influence the status of that server.

JNK

yeah I'll put it separate

about how big does the sql sentry db get?

We only have like 7 or 8 DBs im going to monitor

a couple of those are in the 80 GB range

@AaronBertrand can I ask you another Q on this? Sorry it's annoying :)

Aaron Bertrand

Size of your database doesn't matter, it's about how much data you're collecting - influenced by a variety of things (mainly the factors I just mentioned).

JNK

12:57 PM

ok

Is there any issue with cross-domain stuff here

i.e. my monitored instances are on a diff domain than where the monitoring service runs and the database lives

Aaron Bertrand

You should expect to collect about 2 GB long term for each server you monitor, but that can bend either way, again depending. The space used ramps up quickly but plateaus, because we start aggregating and discarding granularity over time. You can customize that rate.

JNK

@AaronBertrand oh that's fine that's not too much

wanted to make sure I didn't need to set aside like 40 GB for this on our dev server

Aaron Bertrand

You'll need to set up the monitoring service to run with a user that has access to both (think domain trusts). Most people who have issues here run the monitoring service in the domain where the servers run, and have a tunnel for it to communicate to the database in their DMZ or office.

Or they just put the database in the data center too.

JNK

ok cool

I just found an article on the website about cross-domain security

I actually think we have a non-domain account on both instances that can be used

Aaron Bertrand

Support can help you set up anything non-standard if you have issues, too.

JNK

1:00 PM

cool

Aaron Bertrand

@JNK you mean SQL auth? You'll need OS access too (think WMI).

JNK

hopefully we will end up dropping rackspace for hosting at some point

@AaronBertrand I mean windows

we have a windows account on each machine that our IT team uses thats non-domain

Aaron Bertrand

SIDs won't match, so I'm not quite sure if that will be foolproof

JNK

mainly because we have several domains and it allows us to standardize scripts and stuff

oh the windows account needs to have a linked sql server account?

Aaron Bertrand

The monitoring service can only run as a single account.

JNK

1:02 PM

ahhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh

Aaron Bertrand

No, it doesn't, I thought that's what you meant by "non-domain"

JNK

sorry I mean local windows account

we have identical local accounts set up for some of this stuff

so same creds will work on multiple servers

I'll play with it

I have a 30 day trial

Aaron Bertrand

You can try that, but I don't know if it will be good enough for everything

JNK

but it can use separate windows credentials and sql credentials

correct?

thanks for all the info and sorry to bother you so much so early

Aaron Bertrand

@JNK the service itself has to run as a Windows account (domain or workstation). You can connect to SQL Server using SQL auth, but the service still needs to connect to the OS for some of the data (that's where the Windows account comes in).

JNK

1:53 PM

god help me I should never have told The DBA™ I was looking at sql sentry

Aaron Bertrand

Let me guess, he has a hard-on for Idera

Or home-grown monitoring

JNK

probably home grown

he says "we tested it and it was a perf hog"

But at this point I believe exactly 0 things he tells me

Paul White

Home grown, and he provides his own fertilizer

JNK

he also doesn't want me to put it on production servers which is kind of the point of monitoring software

Aaron Bertrand

No monitoring is free - you can't query information and not cause some impact.

JNK

1:57 PM

yeah I know

Aaron Bertrand

It's all a trade-off. Also you can remind him (since he should know), there is nothing to "put on" the production servers - it's agentless and only requires access, nothing gets installed on monitored servers.

JNK

I am guessing when they tested it they put everything on the prod box together

Aaron Bertrand

Probably

And then how do you really know what was the problem

JNK

you don't

Surjit Sidhu

Is there any good tools for workload testing on mysql server ?

JNK

1:58 PM

I'm very much over his taking a dump on everything

especially when I have been proven correct so often

he was 100% sure we couldn't swap log ship primary and secondaries for the DR this weekend

"I tried it it never works!"

I did it in a test environment with no problems then did it during failover and failback with no issues

sorry had to vent there

Paul White

We've all met people like that. It's good to vent. Prevents much workplace violence, I'm sure.

JNK

@PaulWhite yes this exactly

I haven't worked with anyone so frustrating in a very long time

since I was in restaurants, and that's where the people who can't function in society end up normally

Paul White

You have my empathy but I have no practical advice for you, sorry. It sucks.

JNK

@PaulWhite its fine, I think the powers that be are starting to see it too

He argued HARD about some of this failover stuff in a meeting that all the managers were in, and the CIO made a point yesterday of verifying that it actually worked correctly

@AaronBertrand can you link me to your article about the overhead of SS?

Paul White

He needs to stop feeling threatened and start learning instead. It's a tough transition.

JNK

2:04 PM

@PaulWhite he's approaching the end of his career I think and has a lot of insecurity

i.e. he backs into corners about things that he doesn't know about but feels like he should talk about

I have never heard him express ignorance of any aspect of SQL Server

Paul White

Yes. I know what you mean. Quite sad.

Aaron Bertrand

@JNK Overhead Analysis here: sqlsentry.com/products/online-help

JNK

@AaronBertrand sweet thanks

swasheck

2:37 PM

@JNK we have 20-ish licenses for it. a few notable instances: we run it on our investment data warehouse (10TB DSS system) and the instance that backs our real-time trading platform (you know, the one where milliseconds matter?), and our instance that serves our college investment program (customer-facing, real time demand). the extremely negligible overhead from performance advisor is worth what it's provided for us.

JNK

@swasheck awesome

The DBA™ is claiming that they tested it last summer like a week before I started

incredibly there is no documentation or evidence of this anywhere

swasheck

@JNK i'd be happy to have a conversation with this scheißkopf if you'd like :)

JNK

no its fine

but good grief I am weary of everything being a battle

swasheck

yeah. i get that.

you guys should be a team and, from what i can tell, you're not an ignorant moron (quite the opposite) so i'm not sure why this guy poohpoohs so many things.

but cheer up. he'll retire soon, right? or be retired?

JNK

something will be giving in this situation soon I'm sure

Kermit

2:55 PM

so i just discovered there's a tumblr reaction site for almost all fields

swasheck

@JNK are you taking it home with you?

Kermit

designerreactions.tumblr.com

securityreactions.tumblr.com

qareacts.tumblr.com

devopsreactions.tumblr.com

JNK

@swasheck yeah some

swasheck

@Kermit you never let me down with you hilarious links

@JNK that sucks. it's easy to say "don't let it" but i've been there too.

JNK

3:12 PM

@swasheck My wife hears about it a lot

I love pretty much every other aspect of this job

swasheck

@JNK i tend to not talk to my wife about it (mostly due to the fact that i dont like talking to people about things that they're not interested in)... i just come home and have a double and she knows: bad day. :)

Aaron Bertrand

answers.sqlperformance.com/storage/attachments/…

Wow

JNK

@AaronBertrand I don't see why that would be an issue

Aaron Bertrand

I think I count at least 40 NOLOCKs in there

JNK

I mean they use nolock so it should be fast

Aaron Bertrand

3:15 PM

(Why don't people just use SET?)

Kermit

@AaronBertrand i would quit

swasheck

@AaronBertrand that's a lot of work to generate a report to use against your service desk employees come review time

billinkc

We run this report every 5 minutes

Kermit

@AaronBertrand are you able to delete questions asked on sqlperformance

Aaron Bertrand

yes

Kermit

3:20 PM

@AaronBertrand could i kindly ask that you delete this one? it's still associated with my name on a google search

Aaron Bertrand

Are you paranoid about your e-mail being posted somewhere again?

Kermit

you know me so well

swasheck

@Zane ... hope you got a raise because it looks like you've got more games at the bar!

Aaron Bertrand

@Kermit Done. Please create fake accounts everywhere.

Kermit

@AaronBertrand oh you know i will

Aaron Bertrand

3:22 PM

What are you protecting, exactly?

bluefeet

@AaronBertrand mods from spamming him

Kermit

@AaronBertrand things i can't control

and have unrealistic fears of

billinkc

bald white guys in a kilt visiting you?

Kermit

@billinkc i can pencil you in anytime

bluefeet

@Kermit are you going to disconnect from the world and become a hermit living off the land?

swasheck

3:27 PM

@bluefeet a Kermit living off the land

bluefeet

@swasheck i realized that after I typed it :)

Kermit

@bluefeet i wish

Aaron Bertrand

SOMEONE GET KERMIT OFF THE GRID. STAT

Kermit

as long as i can still access the heap

Paul White

@AaronBertrand Tell me about it.

I love that the top-level SELECT has a DISTINCT. On a 16-way join.

Why do people always insist on writing one enormous query. Sigh.

The biggest table there has 33 million rows. The query takes hours. Quite an achievement to produce something that inefficient.

@AaronBertrand Are you writing an answer for that question?

Aaron Bertrand

3:50 PM

Bwahahaha no not today I'm afraid

:-)

Paul White

Ok. I didn't want to duplicate. I'll write something.

Aaron Bertrand

I don't even know where to start, to be honest. It's like he's trying to get to San Francisco on a tricycle, and I passed him on my bike, and I'm going in the other direction.

@PaulWhite thanks, appreciated. I don't think this is a plan problem though, deep down. This is just terribly inefficient query and/or table design.

Paul White

Ha, that made me smile.

Yes I'm not going to fix the query or design in my answer, which will be relatively brief.

JNK

@PaulWhite In my mind I always equate questions/problems like that one with "Why won't this car that my 10 year old built in their yard do 70 mph like other cars?"

Aaron Bertrand

Only 100,000 string concatenations

0

Q: Stack Overflow occurring when parsing a lengthy query on SQL Server 2008 R2 with SSMS query editor

I have a lengthy T-SQL Script. When SSMS attempts to parse the script, it raises this error: Msg 565, Level 18, State 1, Line 106620 A stack overflow occurred in the server while compiling the query. Please simplify the query. The only thing I can see to simplify my query is to remove portion...

sql-server-2008-r2

What are people doing???

billinkc

4:04 PM

> faster than transaction log backups can shrink it

Are they saying they're shrinking their log or just talking about marking it as backed up?

ypercube

Ha. I got a weird error from the sqlfiddle site.

> ERROR: duplicate key value violates unique constraint "queries_pkey" Detail: Key (id, schema_def_id)=(36604, 205) already exists.

ypercube

4:25 PM

@PaulWhite Wow. I counted 27 JOIN in that horror.

Paul White

@ypercube I didn't count, I just looked at the Plan Explorer join diagram (which only shows unique relationships). It is horrible.

ypercube

@PaulWhite Yes, some of them are in derived tables.

swasheck

wow. watching what internal processes do has taught me a few things already

dont laugh, but i didnt consider that you could OPTION (HASH JOIN , MERGE JOIN) ... and then the whole TABLESAMPLE SYSTEM

JNK

@swasheck so The DBA™ has just remembered the issues they had were with Ignite, not SQL Sentry cc @AaronBertrand

Aaron Bertrand

JNK

4:35 PM

yeah

glad we argued about it for 45 minutes first though

Aaron Bertrand

"I will not buy a Toyota! I once had a Civic and it got a flat tire!"

JNK

yeah it's great trying to argue with mis-remembered anecdotal evidence

Aaron Bertrand

Sorry man. But I know all too well how frustrating it can be...

JNK

lol

there's a great quote I heard that was in reference to something about science that applies here

> You can't reason someone out of a position they didn't reason themselves into.

Paul White

@AaronBertrand Being people™

swasheck

4:48 PM

@JNK wow. the problem is that you're arguing from a position of intelligence but not influence. hopefully all of this is eroding his influence and increasing yours.

@AaronBertrand wasn't me, was it?

Aaron Bertrand

What is a "kill-n-fill" operation?

JNK

@AaronBertrand Taxidermy?

Paul White

@AaronBertrand I assume it means delete everything then insert new data. It seems his current approach is to determine a delta.

JNK

He's trying to put 100 pounds of SQL into a 5 pound bag

Aaron Bertrand

I'm surprised the stack overflow comes from SQL Server. I would expect SSMS to barf on that first.

Paul White

5:01 PM

@AaronBertrand Yes, the returned message comes from SQL Server not SSMS. Big string concatenations and huge UNION ALL clauses are about the only two ways I know of to get an error from parsing/compilation.

Aaron Bertrand

@PaulWhite I had fun with this when I was migrating MySQL. Because MySQL's "backups" - at least in the WordPress world - are text files full of thousands of INSERT INTO statements. Fun times.

Paul White

MySQL is just so ... agricultural.

JNK

@AaronBertrand classy

woohoo I'm adding a SQL Sentry database

Kermit

5:22 PM

-1 W3schools is not a good source. The official documentation should be referenced. — Kermit 2 mins ago

Aaron Bertrand

5:57 PM

Ugh, WordPress y u so stupid

Perhaps it is plan guide time

JNK

@AaronBertrand can I bug you again about SS?

having install issues

Aaron Bertrand

Certainly

JNK

So I'm just doing a test install on our local dev machines

I have an application server and a SQL Server instance both on same domain

I'm using my domain account that I have confirmed is Sysadmin on the SQL instance and administrator on both boxes (SQL and application)

during initial setup w/wizard it times out on "Verify Monitoring Service Connectivity"

I tried a couple of other accounts and no luck

Also tried just doing performance monitor and not event monitor, no luck

is there a hidden log or something so I can see where it's hanging?

Aaron Bertrand

Won't matter, it's the connection that's timing out, not the data collection

JNK

ok

so its timing out connecting to the SQL instance?

Aaron Bertrand

6:01 PM

Not sure

I'd imagine so, yes

JNK

firewall settings?

Aaron Bertrand

What ports are open?

Yes, quite possible.

answers.sqlsentry.net/questions/38/…

JNK

ah perfect

Aaron Bertrand

Unfortunately it's not a simple "just open port x and y"

JNK

yeah I see

I'll check all those and see what happens

Aaron Bertrand

6:04 PM

You can change logging to verbose in SQLSentryServer.exe.config but that should be reserved for being really stuck. Support will be a better resource than me if you get to that point.

JNK

yeah understood

thanks again, was trying not to call support just yet :)

Aaron Bertrand

No worries, I can be a good sounding board for simple stuff, but they've truly seen it all

swasheck

i can vouch for SS support's excellence

swasheck

6:17 PM

@Kermit thinking of you ....

time.com/83200/privacy-internet-big-data-opt-out

Kermit

@swasheck read that a few days ago. very interesting

swasheck

gosh ... i'm so behind the times

Kermit

it's okay

JNK

6:36 PM

woohoo I got it

My login didn't have service credentials so the service wasn't even starting

whups

Aaron Bertrand

Oops

JNK

at least it was something dumb

I can run the client from my machine right

Aaron Bertrand

The client only needs to be able to access the SQL Sentry database, not the rest of it, and you can install as many clients on as many machines as you like. The only time you need to access the monitored server is if you do something like kill some session or try to change a job or what have you

JNK

ok

suhweet

bluefeet

we should try to recreate this bug -- meta.stackexchange.com/questions/230860/…

billinkc

6:53 PM

View A uses table 1. View B also uses T1. View C uses Views A and B and finally View D uses C. The data type in T1 changed from char to varchar and metadata in View D is stale.

Does sys.sp_refreshsqlmodule against View D cascade to all views that comprise it or does my friend need to run this per module? Documentation doesn't specify

JNK

@billinkc I think you need to run from inside out

A, B, C, D in that order

should be easy to test though

billinkc

Yeah, writing the query against sys.views to build the command

JNK

holy crap this is awesome

(Perf monitor)

the time zoom on history mode on dashboard is wicked

swasheck

@JNK i always thought that event manager was a neat idea but then i had to use it and suddenly it was an indispensable feature

JNK

yeah

what did you use it for?

swasheck

7:04 PM

which of our monitored "Top SQL" queries were running during an overnight batch cycle. some consultant in india fired off a cognos query from hell and it collided with the overnight cycle. blame the dba. dba holds up a mirror and says, "look more closely"

JNK

nice

so it's a CYA tool :)

@AaronBertrand who wrote the SQL in the sps for this thing though? I looked at some of it and thought you would be unhappy

no real issues with perf but just coding style

swasheck

@JNK it's more than that, but that's an appropriate use case

JNK

@swasheck no I'm seeing that

I wanted it for monitoring SSRS reports too

we have a TON of them that run as guid named jobs

swasheck

seems legit. in the monitored instances expandable thingy you can see failed SSRS schedules

JNK

and our remote office schedules them etc

does it show errors in jobs too?

oh wow it highlights jobs that went long too

and gives you stats on how long they normally run

swasheck

7:07 PM

@JNK it reads the error log

@JNK yep.

JNK

sweeeeeeeeeeeeeeeeeeeet

swasheck

and you can set up notifications for a crapload of events

JNK

I am seeing that

yeah this is gonna make my life easier

swasheck

it could also make your dba's life easier/hell because you can send him pages when things go sideways

and he may have to address them :)

JNK

well we have some event monitoring set up now

Aaron Bertrand

7:09 PM

@JNK I'm not crazy about coding style, much of it pre-dates me, and I've only been involved in improvement of a couple of stored procedures. A very large portion is "works fine, leave it alone" and I'm ok with that.

JNK

but not with this granularity

@AaronBertrand yeah ours is the same way

swasheck

like right now ... i have a long blocking sql on that data warehouse instance. TIME TO GO KILL A SPID!!!!!!!!!!

JNK

yours is better at least

Aaron Bertrand

@JNK runtime reports are very powerful for that. You can instantly see which instances took longer

JNK

now I need to find that runtime report

Aaron Bertrand

7:17 PM

@JNK go to Top SQL, for example. Right-click any statement and choose jump to > runtime graphs

Or on the calendar, right-click any job or other event, jump to > runtime graphs

swasheck

@MikeFal how goes it?

Mike Fal

@swasheck Pretty good. Having a nice time out here in NZ, still quite haven't adjusted to the time shift

bluefeet

@MikeFal When are you heading back to the US?

swasheck

(kidding of course)

Mike Fal

@bluefeet Next Friday (a week from today for me)

bluefeet

7:19 PM

@swasheck can I test that bug now?

swasheck

@bluefeet which bug?

bluefeet

@MikeFal long trip

@swasheck this one -- meta.stackexchange.com/questions/230860/…

swasheck

@bluefeet go for it

JNK

@AaronBertrand oh man this gets better and better

bluefeet

@swasheck I'll skip it considering the content :)

swasheck

7:20 PM

►

@bluefeet i just deleted it for mike's sake

Aaron Bertrand

@JNK if you really want your mind blown, it will be much more efficient to get a one hour demo from Sales Engineering. They will point you to all the important things and explain why/how etc. Stumbling upon things is fun, but...

JNK

@AaronBertrand well I'll watch some of the demos online

Then I'll do a soft sell on management

Aaron Bertrand

Tell sfallen@sqlsentry.com that I promised you the moon and he'll deliver.

JNK

then if I do a demo with sales I'll get them to sit in

Aaron Bertrand

@JNK I promise you Scott is not a salesperson. I'm not even sure why we call the department "sales engineering" - he's technical and gives you the no-bullshit, technical run-down.

JNK

7:22 PM

@AaronBertrand oh cool

The boss just told me to request an official quote

Aaron Bertrand

nharshbarger@sqlsentry.com - you can tell him I sent you, too. He is a sales guy. :-)

JNK

great

swasheck

@JNK scott's a good dude. nick's an interesting cat.

@JNK well that escalated quickly

Aaron Bertrand

For two servers though, really, it's going to be what's listed on the site. He'll probably try to get you into a 5-pack, because it's basically five for the price of three.

Not like pressure you into it, just offer that as one of the options.

JNK

yeah that's my thought too

well we have 2 other servers that we have for just DR

Aaron Bertrand

7:24 PM

Negotiations start in the 20-30+ server range IIRC.

JNK

plus our dev instance so in real life we have 5 sql instances

yeah that was my thought too

Aaron Bertrand

And even if you don't need to monitor them all the time, what some customers do is have an extra license or two that can float between different servers for different things.

swasheck

@AaronBertrand that's what we do

Aaron Bertrand

It's very easy to change what servers are monitored - under the covers it's just an UPDATE.

JNK

@AaronBertrand good idea

we won't ever need to monitor those two AND the main instances simultaneously

if DR is up the other stuff is down

Aaron Bertrand

7:26 PM

Makes sense

swasheck

@JNK we monitor a few high profile dev/qa instances because we'll get the inevitable "but it performed well in dev and qa"

JNK

@swasheck yeah that's a decision for others to make

swasheck

fair enough

Aaron Bertrand

8:04 PM

Ugh, I hate PDO / PHP / MySQL. Trying to optimize this junk. Why does PDO send this as prepared and cache a plan for each parameter? Stupid stupid stupid...

Mike Fal

@bluefeet Yeah, long trip. But good. I'm sure I'll be glad to be home, though.

bluefeet

8:43 PM

any dba experts want to comment on this?

0

Q: Should [tag:database-schema] be a synonym for [tag:database-design], if not what's the difference?

Ok. Somebody help me out here. I can't figure out the difference between database-design and database-schema. Can someone explain the difference to me? I could possibly see database schema referring to the "physical" object in a database, but the wiki doesn't seem to define it that way.