The Heap™ – Consultancy ©®

11:48 AM

Ooo late entry, dark horse coming up on the inside rails...

1

Let's start with the basic scenario. If I want to get some number of rows out of a table, I have two main options: ranking functions; or TOP. First, let's consider the whole set from Production.TransactionHistory for a particular ProductID: SELECT h.TransactionID, h.ProductID, h.TransactionDat...

Lamak

2:53 PM

crickets

mmarie

3:15 PM

@Lamak Hola

I guess everyone is on vacation this week.

Lamak

guess so

and hello

I hate hats

haters gonna hate

true dat

3:45 PM

nice UI!!!

Lamak

nothing says "successful" as getting a bright red message

swasheck

no kidding

that UI puts the suck in success

swasheck

4:18 PM

bbc.com/future/story/20141218-why-do-we-fart-more-on-planes

mmarie

heh

bluefeet

4:45 PM

how do I drop all table? Just drop the DB

-2

A: How to drop all tables from database with one SQL query?

You can try dropping the db: DROP DATABASE database_name CREATE DATABASE database_name

swasheck

5:00 PM

awesome. now please address the constraint/FK issue

Tim Stone

@bluefeet The nuclear option is the only way to be sure!

bluefeet

@TimStone obviously

swasheck

the obvious mistake in that answer is that they left out the line:

`ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;`

Lamak

5:16 PM

So, we fly to the US tomorrow, any tips?

mmarie

@Lamak What city are you going to?

Aaron Bertrand

@Lamak bring a pen on the plane so you can fill out the declaration card before you land

Lamak

new york for christmas and miami for new year's

Aaron Bertrand

(It sucks asking nearby passengers for a pen when they're done, and the FAs won't have one.)

Lamak

@AaronBertrand ah, right, good one

swasheck

5:18 PM

@Lamak hide stuff in the shoes

Lamak

@swasheck like.....food?

swasheck

like water. and sulfur

Aaron Bertrand

Don't bring food

bluefeet

@swasheck we want @Lamak to be safe

Aaron Bertrand

Don't talk about bombs or hijacking or guns

swasheck

5:19 PM

oh. right.

Lamak

@AaronBertrand bummer, I had this whole joke planned

swasheck

tell us how it goes

Lamak

@AaronBertrand my wife wants to bring some cofee and I don't know, things like that....is it allowed?. I know that not food, but, sealed stuff like that?

Aaron Bertrand

Just pay for a checked bag, don't try to jam everything into a carry-on. The money you save isn't worth the hassle. If you don't have status you'll likely be boarding later meaning a big carry-on might not fit.

@Lamak sealed coffee beans should be ok. Put it in checked.

Lamak

@AaronBertrand ok, thanks

Aaron Bertrand

5:21 PM

Definitely not fruits and vegetables

Lamak

@AaronBertrand that's a given

Aaron Bertrand

You'd be amazed how many people try or forget

You won't go to jail or anything but delays are a pain in the ass

Lamak

@AaronBertrand yeah, I've seen this in other trips (not the US, but still)

Also, I'm kind of nervous, this being our first trip over there

Aaron Bertrand

Don't wear clothes that might even have the remote chance of smelling like weed or anything stronger.

swasheck

or gasoline

Lamak

5:23 PM

@AaronBertrand Ah, lucky we don't smoke at all, and I only bathe in gasoline on fridays

Aaron Bertrand

I got strip searched once solely because I left a party and went straight to the airport. I was listed as a "potential smuggler" for about a year

Lamak

oh, jeez

swasheck

@AaronBertrand facebook pics or it didnt happen

Lamak

Well, I go to work tomorrow, then home, then the airport, hopefully no partying

Aaron Bertrand

The dog made a beeline for me and I made a joke with the customs guy like "he must smell my Advil" - they don't like jokes

Lamak

5:24 PM

And also I don't have a visa, so it's gonna be a test of the visa waiver program for me

Aaron Bertrand

Don't pet the dog either if it comes near you

Lamak

@AaronBertrand so I've read

Aaron Bertrand

How long will you be in NYC? Which days?

Lamak

@AaronBertrand from the 24th till 28th

then miami till january 2nd

Aaron Bertrand

Nice. You fly into JFK?

Staying in Manhattan?

Lamak

5:26 PM

@AaronBertrand from santiago to dallas to new york IIRC. And yup, we'll be staying in manhattan

Aaron Bertrand

Long expensive cab ride, if you can try to workout the right train to take to get you to your hotel. Cab is easier but probably $50-60. (I forget if they have flat rate, depends on airport. At this time of year cabs will be slammed too.)

Subways in NYC are really good.

bluefeet

@AaronBertrand I believe it is flat rate to Manhattan

Aaron Bertrand

@bluefeet from all three airports? Last I did it was from Newark, and it was metered, but that was a few years ago now. Usually drive or train to NYC.

bluefeet

@AaronBertrand not sure about Newark. But I know for sure it is from JFK and I'd guess from Laguardia as well

Aaron Bertrand

@bluefeet would be smart for NYC to enforce that, so you're probably right. Still pretty pricey though I think.

Lamak

5:30 PM

@AaronBertrand ok, thanks for the info

bluefeet

@AaronBertrand I think the last time I did it was about $50 flat rate from JFK to the city.

flat fair of $52 from JFK

bluefeet

6:08 PM

the fact that this syntax might work in mysql is just sad

0

Q: So many ANDs mysql

I need a "super ultra multiple tables-fields select!" and i do this last day and works pretty fine for me, (in localhost 0.002sec) but . . . exist a better way to do this? i think theres so many ANDs SELECT name1.* ,name2.field1 ...

mysql

Aaron Bertrand

Oh my word

Zane

6:19 PM

@bluefeet is this a joke?

Kermit

@Zane StackOverflow is no joke.

Mike Fal

> StackOverflow is a joke.

(FTFY)

mmarie

@MikeFal you killed the joke

3

Anyone else here work from home?

Zane

6:34 PM

I am at the moment.

mmarie

heh, well i meant normally work from home

Zane

Oh. No I would hate that.

mmarie

I mostly like it.

bluefeet

@mmarie I wish

mmarie

I'm learning things about working from home as I get further into this job. Some things are just the random things i have to do as a virtual employee that is far away from our office or other employees. Like I had to take my I-9 form to UPS to get it verified since the lawyers say it must be done by an actual person and not over Lync.

This week

The thing appears to be that I need to have a chat with my parents

They think since I'm at home they can call me and talk to me when I'm trying to work. Because they are retired and bored. I can't ignore them because my dad has health problems and I want to answer the phone in an emergency. But I am not going to sit on the phone for 30 mins with my mom while she tells me what TV show she watched. I'm going to have to set work hours with them where they can expect me to be busy.

Kermit

6:41 PM

@mmarie i'm permanent wfh

@mmarie i also had to go in person to an office to do that

@mmarie that's a pretty reasonable request

mmarie

@Kermit Have you read any good blogs with tips for people who work from home? Or is there something you've figured out that you now can't live without?

Kermit

@mmarie i don't read any blogs about wfh. i don't have constant work being a consultant

mmarie

ok, just curious.

Kermit

most of the time i just need to attend meetings and reply to e-mails at my leisure

Aaron Bertrand

brentozar.com/archive/2009/05/…

mmarie

6:47 PM

@AaronBertrand Thanks!

swasheck

DoDevIoCtlOut() DeviceIoControl() : Operating system error 234(More data is available.) encountered.

thanks guys, thanks

James

@swasheck Hi

swasheck

hi

James

@swasheck Nice error message

You guys are running SQLSentry with a fairly large number of instances, right?

For some value of 'large'

swasheck

where large=25

James

6:52 PM

We're within an order of magnitude of that many

@swasheck Are you seeing any annoying Windows networking errors related to failed WMI connections or similar?

Aaron Bertrand

We have customers with 300+ monitored servers. WMI-related errors are unlikely to be correlated to deployment size but rather configuration. Did we help with your deployment? To what extent?

We definitely use WMI for some of our metrics either because (a) it has the lowest overhead among the methods possible or (b) it's the only method possible.

James

@AaronBertrand To some extent, but I wasn't around then. Scott and the sales VP guy (Nick?) came by for a day a months ago to look at it.

Aaron Bertrand

And they didn't solve the issue?

Or this is a new issue?

James

@AaronBertrand I don't think it was a problem then. They were quite helpful about other stuff, though.

@AaronBertrand It's a new issue, we get lots of failed WMI connections that only resolve with a reboot. I really doubt it's a problem with SQLSentry, more likely something in Windows.

Aaron Bertrand

I'm sure support would help look at it. While the problem is unlikely to be our fault, we aren't helping, because we are using those connections.

James

6:59 PM

I can actually see Windows complaining about the connections failing if I view a trace in Network Monitor, but I can't work out why they're failing.

Aaron Bertrand

Is it only on certain servers? Any information missing from the dashboard on those servers?

Two things I could think of are (a) WMI connections always fail on certain servers because of config, permissions, or firewall or (b) intermittent failures due to bottlenecks

James

@AaronBertrand Yep, I might send them a message once our commercial people pay our support bill. : )

Aaron Bertrand

Oh, yeah, being up on maintenance is a prerequisite. :-)

James

@AaronBertrand I'm pretty sure it's b) -- it fails across a seemingly random but large subset of server. And yep, we get missing metrics in the dashboard. I think (but could be wrong) that usually only the stuff collected via WMI is missing.

Usually the WMI connections are only failing part of the time

swasheck

@James sorry. got pulled away. i, like @AaronBertrand dont believe this is an issue of scale. did you recently add new instances? did you change anything about the monitoring services? what about a DC change, of some sort?

James

7:04 PM

Anyhow, was mostly interested in whether @swasheck or anyone else here was running into the admittedly vague issue I described for now. I wondered if support.microsoft.com/kb/2897602/en-gb might be related, but the servers are running a newer verison of tcpip.sys.

It's good to know that some people are monitoring more servers than we are and not seeing this.

swasheck

for me, all of my wmi errors have been perms from the monitoring service to the monitored instance

Aaron Bertrand

We have many customers in the 100-200+ range as well. This is not a common problem but it's usually either something simple or completely flaky network.

swasheck

crapped out kerb creds

thanks Robert!!! you're so helpful!!!

@swasheck Did you look in the system event log? #sqlhelp

Tweeted by SQLSoldier on December 22, 2014 at 7:04 PM in reply to swasheck

Aaron Bertrand

The simple is usually permissions or port, but those issues would be pervasive, not intermittent

swasheck

FIRE

bluefeet

7:08 PM

@AaronBertrand good thing we are all room owners and can see it still

Lamak

@swasheck well, did you?, uh???

swasheck

that's where i found it, bobbie. HHHHHHHHHUUUUUUUURRRRRRRRRRRRRRRRRRR

Aaron Bertrand

It could still be permissions if your DC is unreliable, especially if you use Kerberos

swasheck

@AaronBertrand that's what i'm thinking too. esp if it's solved with a bounce

Aaron Bertrand

Yeah it is weird that a reboot fixes it

That's kind of exactly the opposite of how networking is supposed to work.

swasheck

7:12 PM

but kerb is sensitive to that sort of thing, right?

James

The weirder part (to me) is that the errors start happening on all 3 of the SQLSentry app servers at the same time, and only go away with a reboot.

Aaron Bertrand

Can you send me a sample of the exact error message? I can cross-reference support incidents in the past to narrow down things for you to check. Networking/Auth is not my area but we've obviously dealt with the full gamut as a company.

@swasheck kerb is more sensitive to all kinds of things. Don't sneeze within 20 yards of the server, that sort of thing.

swasheck

bahahahahaha.

Aaron Bertrand

And definitely do not taunt happy fun kerberos

James

@AaronBertrand Sure. I'm actually just looking at the errors that pop up in the SQLSentry client. Are these persisted to a table or file somewhere?

swasheck

7:19 PM

DROP DATABASE SQLSentry should get you what you need. GIVE IT A TRY!

make sure you back it up first ... i've gotten best performance with BACKUP DATABASE SQLSentry to DISK=N'NUL'

James

@swasheck That's actually a pretty good quick way to stress test a vendor's array

BACKUP MyBigDB TO DISK = 'NUL' WITH BUFFERCOUNT=500, MAXTRANSFERSIZE=4194304

swasheck

yeah

seems i've exhausted robert's body of snark

Aaron Bertrand

@James I mean the ones you find in Windows - they will likely have better info.

James

@AaronBertrand Sure. I'll send you a screenshot. Thanks for offering to check.

A screenshot and the text of the errors, I mean.

swasheck

@James you can't ctrl-f a screenshot

Aaron Bertrand

7:38 PM

You can e-mail me at abertrand @ sqlsentry.com

swasheck

8:29 PM

eh?

> The trace flag –T1118 makes SQL Server allocate complete extents to each database object,
affecting all the databases in the instance. This trace flag will relieve the SGAM contention. The Trace
Flag can be set using the SQL Server Configuration Manager, as shown below.
This Trace Flag should be set only if tempdb is heavily used. SQL server must be restarted for the
flag to take effect, and the setting is more effective for TPC-H than for TPC-C. Also, the user
databases must be rebuilt.

James

8:40 PM

@swasheck Seems OK to me except the "This Trace Flag should be set only if tempdb is heavily used" part. No idea about TPC-C vs TPC-H, but happy to take someone's word for it

swasheck

i'd never heard that you need to rebuild user databases

James

I guess the part about "must be rebuilt" is unclear

I think it means that they must be rebuilt if you want to get rid of the mixed extents

swasheck

rebuild meaning "restore from backup?"

James

I thik like

rebuild index

But it's not written clearly I guess... someone might think anything

swasheck