@bluefeet snowing in phoenix again?

Yes @swasheck it's snowing sunshine

@bluefeet did you just call me sunshine?

Morning all

@TimStone I love the answers. Use VARCHAR! No, use TEXT!

Morning COTW

@TimStone So, he seems to know how he should approach things "in a best practice manner" and is asking if sticking to best practices for a small and very localised project would be too much.

Nay, if it won't develop into bad habits.

I understand the constant changing concern, but then I can't see how we could offer something new that would matter much in his situation (small project).

@TimStone holy cow

Sorry, but "its a small project" is not a good reason to just ignore the fact that data types exist

Who needs types ...

RickJames' answer seems to have been intended as a reason why not to use VARCHAR for non-character data. I don't think it was interpreted that way...

I'm not sure that "inherent properties of the data type" count as a 'gotcha' either, heh.

@ypercube Someone should have just suggested using a single column table with all values delimiter-separated. Then you don't even have to worry about a schema, let alone planning your data storage appropriately!

@TimStone Excel for the win!

That way every cell can be a varying data type, excellent!

Problem solved.

@TimStone Just put it all into a 1 column 1 row XML field

And put that field into a file on the hard drive instead of in the database

Then unplug your web site and walk into the ocean

Anybody got time for a quick question about fragmentation on a SAN?

Does fragmentation even matter if we are on a SAN? The DB files are on one 'drive' presented to the OS but, obviously, on the SAN, that file can traverse many drives on the SAN. Would having the indexes in contiguous order matter since an index could technically reside on multiple drives?

Fragmentation isn't just about page order and sequential reads. It's also about having lots of page splits and half-empty pages. Hopefully your data is usually in memory anyway, so the I/O shouldn't be a big deal, and you can always throw memory at the problem to solve that. But in turn you need more memory to store fragmented indexes due to the simple math that there are more pages.

@TimStone single-column, single-row !

A few prolific folks suggest that fragmentation isn't important anymore. I vehemently disagree.

Oh, I see @JNK already suggested that!

@AaronBertrand Awesome, I'm just trying to understand how that all works in the context of being SAN-backended. There's no performance issue or anything, just wanted to know how that plays into everything.

@KrisGruttemeyer Not all that much difference. You're still storing more pages than necessary and this has a trickle-down effect on every part of the process that accesses a page. Having the pages scattered across multiple underlying physical disks can be both better and worse for different scenarios.

i do believe that's the first time ive seen expected behavior of a datatype considered a "gotcha."

You could also just trade in your tired old SAN for shiny new hotness

Are you guys talking about NTFS fragmentation, index fragmentation, or both?

@AaronBertrand shhhhhhhhhheeeeet. that's what we did

@JamesLupolt index fragmentation. NTFS fragmentation, who cares?

@JamesLupolt that was going to be my question

@AaronBertrand Very nice!

we really need to get our sql saturday up and running

NTFS fragmentation only affects people who are very careless or sloppy with file size and growth. And even then, I question that its effects come anywhere near the impact of index fragmentation. NTFS fragmentation only affects direct physical I/O. If you're doing that a lot I think you have bigger problems.

denver ... sql sat #420 ...

@KrisGruttemeyer it is very nice. i believe that @JamesLupolt is also using pure

@AaronBertrand support.microsoft.com/kb/2002606/en-gb?wa=wsignin1.0 <-This has popped up for me at the new environment.

@AaronBertrand This seems to be true. There's the well-known CHECKDB error on large, very fragmentation files: blogs.msdn.com/b/psssql/archive/2009/03/04/…

But it's hard to get that error without some pretty silly autogrowth settings.

Exactly my point.

@MikeFal running sql server on windows vista?

@swasheck Server

i know. i know. still just giving you a hard time

I'm not sure about "silly autogrowth" settings, as we don't let our files autogrow. But we do have a pretty crappily designed DW with multiple file groups that all get grown at different rates.

Hey could be worse (XP)

The one place I saw that error was on a database that had grown to around 1 TB, 128 MB at a time.

The DB we ran into it on is 5 TB with ~50 filegroups that all get grown at different rates.

Occasionally we get it with snapshots we create, invalidating the snapshots.

that all get grown at different rates. contributing factor? (serious question, not being a richard)

@swasheck Oh yes. The guys who designed this DW didn't know what the fuck they were doing.

@MikeFal how long do you let your snapshots live?

@MikeFal Or just low growth rates on large files (or huge numbers of files), manual or auto

@MikeFal then don't be offended by qualifiers like "silly" :-)

@JamesLupolt Neither. We manually grow in large chunks.

@MikeFal Interesting. I'm surprised you managed to trigger the error, then?

@MikeFal If you grow in large chunks then there shouldn't be enough of them to trigger this problem.

i'm still not sure that the root cause is ntfs fragmentation.

@AaronBertrand A couple of days at most. I actually don't concern with it much.

@swasheck it's not about the fragmentation per se, but rather the abnormally large number of file handles.

@swasheck As I understand it, it's because there's an attribute list for a file in NTFS that tracks the runs of clusters per file. The attribute list runs out of space

@MikeFal so just an off-the-cuff suggestion, if you snapshot nightly instead of letting them live longer, you're much less likely to hit the issue.

@swasheck Write a program that creates an endless stream of small 20-byte files. At some point NTFS will throw its hands in the air and fall on a knife.

@JamesLupolt @AaronBertrand Well, it triggered. I'm not necessarily offended by the term "silly", but it is a fairly broad generalization. ;) Honestly, I was surprised we triggered it as well. I think if you saw how this db worked, it would make more sense. Anyway, we moved the files to a new set of LUNs, essentially defragging things, and everything is fine.

@AaronBertrand They take snapshots as part of the release process. Snap before deploying code to give them a rollback point. Not really sure why we do it in Prod, as we never rollback code in prod, just apply hotfixes.

But what do I know, I'm just the DBA.

@AaronBertrand @JamesLupolt right. i may not be thinking about it clearly enough, but that seems to be more of a limitation of the file system/process design issue than "ntfs fragmentation."

@swasheck well part of it can be caused by a single file having many fragments.

@swasheck I think it's just far more likely to happen if you have a very high number of fragments.

does it ever happen outside of a utilization of sparse files?

scurries off to find the 30,000 db in express edition question

Yeah, what suckhole is stuck on XP

@swasheck I don't think it is isolated to sparse files, no. That just seems to be a scenario which is far more likely to trigger the problem than having 1 MB autogrow in a 14 petabyte database.

@JamesLupolt @AaronBertrand thanks for the information. i appreciated your patience in educating me.

@swasheck Here's a post about the same issue in Exchange: blogs.technet.com/b/mikelag/archive/2011/02/09/…

@JamesLupolt ok. well i've run into that a lot. i thought it was a function of not enough space on the disk to create a snapshot

@MikeFal I believe you. I'm just surprised it's happening if you grow in large chunks. Is there any chance the database has a large number of fragments from a time before someone changed the growth settings?

@JamesLupolt Possible. This particular database is something of a rogue project that I would rather just wash my hands of.

The DBA team had it unceremoniously dumped in our laps about 8-9 months ago.

@MikeFal I think you can use contig -a from the Sysinternals toolkit to check the number of fragments.

@billinkc Hehe. I found our chat window in there.

mornings like this make me realize how little i know

@swasheck This is why we learn! :)

@billinkc That's gross.

@billinkc Is it a state law in Missourah that you can't use any OS from the current decade?

engadget.com/2015/02/20/settlers-of-catan-movie-tv-show

^^ Yeah that makes sense...

@JNK This client is in Kansas where evolution is just a theory

a scientific theory, which is more than a hunch

@billinkc makes complete sense

"Book of Gates, chapter 12 verse 3: 'No one will need more than 637 kB of memory for a personal computer'". Based on this factual evidence Kansas has decided to outlaw memory addresses above 637 kb.

@JNK I distinctly remember a friend of mine in high school a friend of mine telling me that my gig of ram was overkill and "no one will ever need that much ram in a PC".

To which I replied I said the same thing about hard drives when I was 12.

Yeah I went through it too, when I added 4 MB to my PC to run doom

@JNK still my all time favorite game.

its up there for me

I probably enjoyed quake more just because of the online multiplayer

I spent way too much of my teen years playing online CTF with the original quake

on dialup

@JNK Loved Quake. Team Fortress was my jam. I'm talking 2fort4 TF

@JNK I used to get dominated in DWANGO(I think this was the correct application)

I also remember when Counterstrike was a HalfLife add on

Ultima III and Ultima IV were my games of choice. Oddly enough, I didn't really play computer games in teen years. Enough classic NES though to blister my thumbs

@KrisGruttemeyer Wasn't it a mod?

@KrisGruttemeyer I did that quite a bit too, but spent more time in action quake

@KrisGruttemeyer yeah did that a lot too :)

@Zane Correct, add-on/mod.

Also Allegiance if anyone ever played that

most underrated game evar

@billinkc NES at my moms ID, Blizzard, and Lucas Arts at my dads.

@JNK what about Unreal Tournament? Get into that at all

@Zane As it should be

@KrisGruttemeyer not really, I played it some but the balance was weird to me

I just remember vaguely not liking it very much

@JNK Yeah, I can see that. I remember installing it on the computers at school. Every day, second period was unreal tournament time.

meh. i didnt do much pc gaming until a bit later in life. the only one i really go into was morrowind.

@JNK I was a Quake 3 loyalist.

@KrisGruttemeyer They blocked installs on our school computers but they had remote desktop so we would use that to play Warcraft 3 in class all the time.

@Zane What was the map that was in space and had all the jumps and portals? That map single-handedly cause my anger issue in high school. haha

@KrisGruttemeyer That was one of the CTF maps and I think it's just called CTF 4 or something stupid like that.

s177.photobucket.com/user/SPARTANVI/media/screenshots/…

@Zane That map drove me absolutely mad.

@Zane That would be it, that pain in the ass

That little smasher thing was my favorite.

nerds

I always ended up using the jump at the same time as someone else, hitting each other, and falling to our death

Theres that bfg in there. My favorite thing to do is to shoot the thing that closes it just before they land so instead of getting crushed they just fall to their death. Way more frustrating that way.

Third post college job allowed us to play videogames during lunch on Friday. The IT folks were never absent on a Friday if it could be avoided

I used to be a sniper with the railgun on that one.

@billinkc nice! What did you play?

You knew when it was time because a buddy would turn on Enter Sandman

Variety of shooters, quake, tf2, little bit of half life - whatever there was a crack for and our POS could play

Later on, we got newer machines and played Halo but then management decided that wasn't professional and nixed it

Shortly thereafter, the camaraderie that kept the group together dissolved and people started leaving because otherwise that place was shite.

I'm sure the two events were completely unrelated though

correlation != causation

@billinkc professionalism blows.

I would totally take a pay cut to work at a place that let me do that sort of thing.

Although I do that now when I work from home. Take a break for a quick hearthstone game or something of that nature.

Were I to ever run an organization, I would totally encourage something like that. Attendance is up, morale is up, people took the beatings that place dished out because there was something unique. Established a nice espirit de corps

I asked this yesterday but didn't get any responses, what, if any, things do you do for unit testing in your DBs?

@bluefeet what is this "testing" you speak of?

@bluefeet deploy to prod. wait for screams.

that's what my response is @Zane

We've got none but they want to implement something

@Zane Rackspace fits the description.

They also badly need good SQL Server DBAs. However, I'm not sure if anyone who is in charge of hiring people is aware.

@JamesLupolt pay cuts for intangibles? :)

@swasheck Letting you play video games at work. I wouldn't think you'd have to take a pay cut there unless you've got a good deal elsewhere.

@bluefeet unit testing databases is hard. Steve Jones has a series of posts on using tsqlunit but I haven't tried to use that since 2006?

@billinkc that's what I'm seeing from the posts I've read. It doesn't look like fun and I'm not sure how we are going to be able to implement it for some of our complex logic.

I use TSQLT a lot

like a lot lot

I just spent an hour rewriting a bunch of tests

so @bluefeet if you have Qs on that product let me know

I like it quite a bit

and i thought that the video game conversation was needlessly nerdy

;)

Maybe it was tsqlt. Frack, I don't remember

that's the main one I'm aware of

it's VERY handy and can do most of the stuff I need it to

stuff I can't test is because of bad design we have in our code, like STPs returning 5 result sets

The challenge I always run into is that no one can identify what a minimal, stable set of data would be

@JNK I swear that wasn't me laughing

TSQLT makes that easier with setup procedures

and the setup is used by all tests in the same test class which is really just a schema

@JNK cool, thanks. I'm checking that out.

so for some of mine I have the setup proc that populates like 30 or 40 tables with lookups and transactional data

then all the tests use that same dataset

its made refactoring a LOT less scary

also handy for things like procedures that don't have a spec, but I need to make changes to it

bbc.com/news/uk-31554844

My thing is that we have really complex processes, including a billing process that executes about 100 procs. We want to basically unit test that if we change any one of those procs - everything still bills correctly

so it's easy to compare the results before and after and make sure they match

How does this happen? After you've been bowling how can you possibly be interested in signing up to fight for IS in Syria?

@bluefeet without knowing more it's hard to say, but I think you could isolate it to that single proc first

tsqlt also lets you fake procedures which is nice

@JNK I know that was a terribad description :)

like you can say "for this test this STP will always return 1"

to isolate dependencies

you can also fake views and functions in the same way

@Zane It's a pretty hot topic there. There's great concern over HS age girls being recruited to be soldier wives, married off to people they've never met, etc

@billinkc yeah we've got issue with that here in MSP. We just stopped a kid from bouncing out to join up the other day. I just don't understand how they are able to convince these kids into shipping out to war torn Syria to fight for a clearly bonkers organization.

Seems like it would be a hard sell after you've played Smash Brothers.

Yeah, heard that y'all are trying a different approach with one boy with a reindoctrination approach instead of immediate jail time

Oh my. First, he is using Java, then answers in comment:

I give up. It's Friday

Oopa!

Back to Greece tomorrow, for some oopas ;)

@billinkc take the kid bowling. Have him play a game of air hockey. IS has none of those things.

@ypercube Safe travels

@ypercube have fun.

@Zane Clean Monday on 23rd.

Got a headhunter that emailed me a Sr DBA position, one of the requirements is: "Strong knowledge of DBCC SHRINKFILE and other DBCC commands."

If you specifically name DBCC SHRINKFILE as a requirement, you are probably doing it way too much

@KrisGruttemeyer yikes

@JNK Yeah, I'm gonna go ahead and get on the NOPE train for that one

@KrisGruttemeyer also seems possible that the headhunter asked a non-technical manager to check for some stuff they should know about, and he/she just opened some maintenance script and listed the first thing they saw

They also need to know -- bloody stupid hack

@KrisGruttemeyer Every now and then I like to look at these descriptions and think to myself "I could totally clean that place up. So many easy wins".

@KrisGruttemeyer This means some practice at managing up. Getting people to state their intent: (i.e. "efficient storage administration" rather than "shrinkfile knowledge")

But having said that, I'd half expect a recruiter to say "nope, we're still looking for a shrinkfile guru". Then that's a nope

@JNK That could be, but i don't know. Why name that one specifically?

@MichaelJSwart I think 'efficient storage administration' sounds so much more elegant. At least WAIT to tell me you want me to run DBCC SHRINKFILE like it's hot.

Seems like a prioritization issue to me too - isn't it more expensive to pay a DBA to keep fiddling with file sizes than just to add more space?

If job requirements include a lot of specific stuff like that, chances are that the job doesn't have a lot of autonomy and that's a red flag.

@JNK With storage being as cheap as it is, this is a problem that you CAN and SHOULD just throw hardware at

@MichaelJSwart Unless you want to come in and build it from the ground up

I kind of had to do that where I am now. Started about 9 months ago, previous DBA (the first one they ever hired) had a chance to get things under control but left before he could get all the monitoring and automation that this system needed

He was only here for about a year, I think

and he was a pretty damn good DBA from what i understand but being the one man wrecking crew that he was (and I am), you have to prioritize what you can do.

minus the whole "Use NOLOCKs on every statement" as a blanket "best practice"

the more places I work the more I realize this is a pretty common thought process by non-DB folks

Most of the DB code here was written by web developers

Yeah, and I understand that it was just him and maybe re-engineering some things just wasn't able to get off the ground. Just, as a DBA, I have a difficult time doing thigns like that and just sweeping things under the rug. If you're going to do something, do evertything you can to do it the right way, ya know?

Aaron's bad habits to kick and the reasons behind many of them have made me a bit of an OCD-driven stickler for people doing dumb things or being just lazy.

w00t w00t!

ESPECIALLY spelling out the unit of time in DATEADD

(It's always nice to hear that it has a positive effect. Most people think I'm just ranting.)

especially when they use 'y' and wonder why (no pun intended) they arent using YEARS

it takes, literally, .5 seconds to just spell y-e-a-r

Even better is YYYY. Really, you found that more intuitive?

@KrisGruttemeyer Maybe it's a joke?

/me meekly raises his hand

"Well yeah, I wanted to type just Y, but then I realized I might need to specify that I want four digits."

^ if you thought that hard about it without testing smack

"We need you to export the date as YYYY-MM-DD" and then you just punch that right into DATEPART and magic, it works

or 'w', which is weekday, not 'week of year'. Dev here got bit by that one.

Oh, that MM isn't the MM you thought it was

@billinkc These aren't the MMs you're looking for

And now I want M&Ms. Awesome.

I aim to please

Or misbehave

@KrisGruttemeyer And on some days it can appear to be working just fine. I was going to demo that at my pre-con, using GETDATE(). I had to pick a hard-coded date instead because it was on Friday Feb. 7, which was weekday = 6 and week of year = 6.

@billinkc You could do so by going and getting me the biggest bag of Peanut MMs you can find

I have never purchased a bag of peanut M&Ms

@AaronBertrand Well that would throw a wrench right in there, wouldn't it.

Plain, dark chocolate, peanut butter, yes. Plain peanut, no

@billinkc Peanut butter MMs are amazing, too. I have to agree with you there, yum.

@KrisGruttemeyer Did you know that peanut butter M&Ms are not sold in the EU?

I think it's because they have partially hydrogenated oil in them.

Actually... this seems to no longer be true.

Who cares? You can get Lions and Penguins and Star bars

Or that the EU takes peanut allergies far more seriously than North American retail (and far less seriously than North American school systems)

@JamesLupolt No, and the fact that they don't is sad, they're so delicious

Ugh, I like creative presentations, but I think this one is trying too hard

attendee.gotowebinar.com/register/4109471252943530753

First sentence of abstract: "Committing Crime is the final frontier – where a human starts to degenerate into a renegade, a social waste and capable of causing huge personal and financial loss to individuals, businesses and the government."

I guess I should be less judgmental about them and go in with an open mind.

@mmarie That's awfully dramatic. Sounds like a movie trailer intro sentence.

IN A WORLD WHERE...

I'm also pretty sure I have used that City of Chicago dataset before, and it isn't that "big". So I'd like to see the reason they use HD Insight. Maybe that is what I will get out of it. I admit that I'm a bit of a big data skeptic.

There are also grammatical issues in that intro

I should register bigdatasceptic.com and use it as a clearinghouse for all the false stories

@AaronBertrand Is it wrong that I read that in the movie trailer guy's voice?

@KrisGruttemeyer no that is 100% right

@mmarie The buzzwords that come with it only further my skepticism. "Data-driven actionable insights" made me want to light myself on fire

@mmarie it always amuses me to read about "big data" which is like a million records of something

I was able to invest in the Blue Diamond match company based on the Data-driven actionable insights I gained from this chat room.

"Big data", i can do that. It's called a Cartesian product.

@KrisGruttemeyer Yeah, I guess i'm used to that. I'm pretty sure i have used data driven decision making and actionable insights when talking to customers.

It's 10 degrees F outside. I'm making a data driven decision to not wear shorts and flip flops today

@mmarie Previous employer loved to toss those around. It became part of the culture. Of course, out of the 45 employees, 20 had Chief or Executive in their title. Too many chiefs, not enough indians.

But i meant something by those terms. Data-driven because the company was just guessing with no numbers available before. Actionable insight is usually when I'm talking dashboard design. If I can't look at it and take action or make a decision, i don't want it on my dashboard.

actionable insight is how i get people to admit they don't actually use that report someone has been manually compiling on a weekly basis for the last 5 years.

Spoil sport

@billinkc 100% of the time I wore flip flops in the past it was beautiful weather. Time to test causality!

but i also used to create demos and scripts for marketing Power BI so it's possible I have been contaminated

@JNK You would be crowned king of NE if you were able to make it sunny

@mmarie At last job we just started turning off reports to test that

Well, except for the resultant floods. But after the water subsides...

End users had a couple of reports with like 200k records in them we were almost sure they never even opened

@billinkc My house is on a hill so no worries there

@billinkc sunny here but sun isn't everything

Sun also means "More water for the Ice dams!"

@JNK Amen to that.

We're lucky - only a couple of small ones above our upstairs bathrooms.

@AaronBertrand Your postal code is broken, it's just 2895. Everyone knows the leading zero doesn't count

I got insane ones for the first time this year

Neighbor not so lucky - I will post a pic at some point

Raised ranch and basically the front of his house looks like a frozen waterfall.

Does your neighbor not have a roof scraper?

Need a roof rake

Did that yesterday, cleared about 2 feet off of it. The ensuing avalanches made my husky REALLY happy.

@Zane I guess not, it's not a neighbor I talk to. To be fair, even if I had ice dams that needed tending to and even if I had a roof rake, I'd still be hiring someone else to do it. Better them getting impaled by a 6-foot icicle than me.

Just walk it off.

@Zane I had to google that because I wasn't aware that roof scrapers were a thing. TMYK

Same reason I pay a plow and landscapers - I could spend time doing that stuff myself, but I'd rather make more money working and get my exercise from things I enjoy more, like sports

My house is two stories so, nope, not going to DIY

@mmarie It's kind of a huge pain in the ass.

@AaronBertrand Depends on how good the plow guys are at getting to you in a timely manner. If they take 12-24 hours, it's pointless, you'll still have to shovel/snowblow just to get out of the driveway.

The roof-raking as a service would be a good idea because those icecles will just destroy you

@KrisGruttemeyer they're pretty good, they actually only come for 6"+. (TWSS)

@AaronBertrand is it weird that I think that is cool?

@AaronBertrand HAHA, love it.

Plus we both have all-wheel drive and a downward-sloped driveway. We are never stuck in our garage, it's the streets outside of our control that are the issue.