Tag Archives: Training

T-SQL Tuesday #014 – Resolutions

OK, it’s time for TSQLTuesday again and Jen’s making me write something since we’re hosting this month.  So the topic is resolutions, and that in itself isn’t a topic that’s near and dear to me because frankly I just don’t believe in them.  I don’t think you have to wait until a new year begins to resolve to do something you’ve been meaning to do.  In fact, that pretty much dooms you to not completing it because it takes more than the turning of a calendar page and a romantic notion to accomplish something.  If it were really that easy, you would have done it already so it wouldn’t be a big deal. 

Your new year can start anytime really.  Hell, doing a new year’s resolution doesn’t even line up with my review period at work, so if I relied on the new year to start something new I’d lose 3mos making good on what I’m supposed to accomplish for work.  People in IT quite often put personal goals in their yearly goals at work.  Things like getting certified, or perfecting a process, or taking management classes, etc are all things that are commonly found in your yearly goals at work.  So if you’re going to make some kind of resolution to do something, or to stop doing something, why not put it where it actually makes more sense… in your work goals.  Your bonus quite often relies on you completing your goals so it’s really the perfect place.  And it gives you a better excuse to have the resolution to begin with because you can use the bonus as motivation.

So even if you’re going to make a resolution at work, try to make it something you can actually do.  One of the biggest reasons for failure is someone will set a goal that’s completely ridiculous for them and when the goal starts slipping they get discouraged and just give up.  I’d like to get my MCM this year, but I don’t even have any of the lower certs yet.  Well, chances are you’re not going to make it dude.

With all that said here are my resolutions:

  1. Try to have more patience with customers and vendors.
  2. Get better with XML.
  3. Study Oracle more.
  4. Get more serious about learning Chinese.
  5. Find a better balance between work and family.

 

I’ve also heard that a few of you have vowed to finally learn powershell this year.  That’s a fine goal and there really is no better time because there are plenty of people giving all kinds of free resources.  One of the things I’ve done is to go out of my way to create some good video resources for those who know nothing about it and want to learn from the ground up.  Here are a few direct links so you can get started without any further ado.

http://midnightdba.itbookworm.com/VidPages/PowerShellOverview/PowerShellOverview.aspx

http://midnightdba.itbookworm.com/VidPages/PowershellBeginningForDBAs1/PowershellBeginningForDBAs1.aspx

http://midnightdba.itbookworm.com/VidPages/PowershellBeginningForDBAs2/PowershellBeginningForDBAs2.aspx

http://midnightdba.itbookworm.com/VidPages/PowershellPSDrive/PowershellPSDrive.aspx

http://midnightdba.itbookworm.com/VidPages/PowershellProfiles/PowershellProfiles.aspx

OK, that’s all I’ve got.  Good times.

Keynote or Breakout Session?

From time to time you run across something that demonstrates perfectly exactly what the industry needs.  As I sit here at the PASS day-3 keynote I’m watching Dr. DeWitt talk about how the query optimizer finds query plans.  This is a very complicated topic under the covers and he’s covering some of the really complicated aspects.  He’s already explained the different types of histograms and how they work (I’m sure only at a high level), and he got into selectivity, etc.  And the thing is, he’s explaining this stuff in such simple terms and making it so easy to understand, it just hit me like a ton of bricks;  why isn’t this material being taught everywhere? 

This is one of the things I’m always talking about when I say there’s no really good training out there.  There are plenty of people out there teaching queries and tuning techniques, but nobody is bothering to break this stuff down so that people actually understand the terms and what they really mean.  Most of the time what they do is just define some terms briefly and then move on and expect everyone to be able to go back home and apply this stuff.  But without this background of how this stuff actually works and how the terms really fit together and how you get bad plans, etc, then people aren’t going to be nearly as successful as they could be.  Dr. DeWitt is clearly concerned with teaching people how to think instead of just what to think.

This is always the type of training I move towards because I find I never commit anything to memory unless I understand how it all works and fits together.  So what I’d like to see happen is for someone to put something like this together in a video series, or in a pre-con… something.  That’s a pre-con I’d actually pay for out of my own pocket.  A whole day of someone dedicated to making sure I understand how this stuff works… Hell Yeah!  But seriously, where does someone go to learn stuff like this?  Because this info isn’t out there in any human readable form.  And it’s apparently not just me either, cause he’s been brought back by overwhelming request, so I’d say the better part of the room is also screaming for this type of info.

The stupid have fallen

This is a followup to my previous post here.

Wow, what a day.  Ok, so I just got off the phone with the tech lead for that company.  It was enlightening to say the least.  It was really a longer call than I expected or that I even wanted, but my blood pressure’s gone down a bit now.  I obviously can’t recount the entire conversation for you, but here are the highlights:

V = Vendor

M = Me

V: Hi Sean, this is Josh(not his real name) with X and I was told you wanted to talk to me.

M: No, the last thing in the world I wanna do is talk to you, but I feel in order to get our issue resolved I have no choice.

V: Yeah, Cornhole(not his real name) told me you got upset with him when he was just trying to explain to you what your issue was and then you insisted on speaking with me.

M: This is just incredible.  No I didn’t get upset with him for explaining the issue to me.  I got upset with him when I told him his explanation was flatout wrong and then he called me an idiot.  He didn’t even hint at it.  He called me an idiot outright.

V: Really?  Cause that’s not the story I got at all.  I heard that he was calmly explaining that your backups were the issue and you just exploded on him.

M: No, that’s NOT what happened at all.  That little turd actually called me stupid for not buying his assinine explanation for our issue and then I only exploded after he did that a couple times.

V:  Well what don’t you believe about it then?  Because we’ve seen that many times where backups will cause proceses to block.

M: No you haven’t.  Listen, I realize that everyone can’t spend their days studying SQL, but one simple fact is that backups don’t block processes.  Period.  I’ve been doing nothing but DBs in SQL Server for over 15yrs against TBs of data and nowhere have I ever seen a backup block a process.  The problem is that you’ve got like 3 tables with TABLOCK in that sp and that’s what causing your blocking.  Don’t you think it’s funny that it only clears up AFTER you kill that process?

V: Where did you get the code?  Those sps are encrypted.

M: Encryption only stops amateurs.  And while we’re at it, what’s this script I hear about that you’ve got to unconfuse SQL to give you the real spid for the process?

V: Where did you hear that backups don’t block processes?  It’s well-known here that it’s one of our biggest problems.

M: No, your biggest problem is your TABLOCKS.  And I heard it from the truth. 

V: What is a tablock?

M: A table-level lock.  You’re locking the entire table just to read data.

V: I don’t know about that, I’ve never seen the code because it’s encrypted.

M: Well I do and that’s your problem.  Now what about this script I kept hearing about from Cornhole?

M: Did you get my email?

V: Yeah.

M: And you can see the place in the code where you’re locking the whole table?

V: Yeah.

M: So how is it that you lock the entire table and you claim that it’s the backup blocking everything?  Haven’t you noticed that the backup only decides to block stuff when this process is running?

V: That does sound a little suspicious when you put it that way.  What can we do?

M: Let me take those TABLOCKs out of there so I can query these tables without locking everything up.

V: I just saw your email signature and I’ve never heard of an MVP before, but I just looked it up.  Are you really one of those guys?

M: Yeah.

V: Wow, so I guess you really know what you’re doing then.

M: I’ve hung a few DBs in my day, sure.

V: Do you mind if I write you again if I have any questions?

M: I suppose that depends on whether you’ll let me change that stupid sp or not.

V: Yeah, go ahead.  I’ll have to have a talk with our devs tomorrow and try to convince them to put this in our next build.  Honestly, I’m only a tech lead here because I’ve been here a few months longer than anyone else on the support team.  We get all this stuff from the devs and they won’t tell us anything.

M: So what about this script I keep hearing about.  The one that tells you how to unconfuse SQL and give you the right spid for a process?

V: That’s a script the devs have and they won’t show it to me either.  I just tell my guys something else so they won’t know I haven’t seen it.

M: Wow, that sucks man.  You do know though that a script like that doesn’t exist, right?  It’s completely bullshit.

V: Yeah, I hate this place.  I’ve been suspecting for a while now they they were lying to me, but what can I do?  I need this job.  I’m just lucky to be working.  I have no idea what I’m doing here.

M: I don’t know what to say to that.  You’re right, you are lucky to be working these days.  But pick up a couple books on your own and learn SQL.  Don’t rely on them.  There are websites, blogs, etc.  I myself run a site.

So in short, I really started to feel for this guy.  He ended the call apologizing 100x for the grief they’ve caused me and he’s going to go out right away and start watching my vids and trying to learn more.  He asked if he could write me with questions, and I said, of course dude, anytime.  That’s what I do.

And y, he sounded about 22 as well.  Turns out I was right.  I asked and he was 21 and this was only his 2nd job and they brought him in from nothing and “trained” him.  I’d say he’s got some work to do.  But I personally consider this exchange a success.

Dealing with superstition

To be a DBA for any real length of time takes a certain type of personality.  For starters it helps if you’re fairly strong-willed and confident.  In order to deal with a lot of the customers you find yourself coming in contact with it really helps if you can stand your ground.

This is the exact type of guy that we’ve had as the past 3 DBAs before I got here.  The problem is that they were apparently pretty dumb so they spread so much misinformation about SQL and about DBs in general it’s a constant battle for me to undo it all.  Like I said, these guys were dumb, not malicious so I really don’t think they meant to  spread that much bad information… I think they just didn’t know how to pick up a book.

So this situation causes a special problem for me because I’m trying really hard to do things right here but a lot of my customers and vendors just won’t let me.  “Well, the last guy told us this is how it works”.  Yeah, I bet he did, but that’s not right.  And I can tell them it’s not right 100x, but they’ve heard the same tired story for years now so they’re not likely to believe me even after I prove it to them.  And we all know that the guy who says it the loudest is typically believed.  And I’ve heard that our previous DBAs were all quite passionate that things be done a certain way and they really went to bat for what they wanted.  What’s funny is they went to bat for ridiculous things, and then let a lot of really big things slide… but that’s another post isn’t it?

The only thing I can hope for at this point is to stick around longer than the other DBAs did and eventually my rhetoric will start to sink its way into their heads.  I’m thinking one way to do it is to just flatout lie.  Just tell them, yeah, that’s the way it used to be but on this new version they’ve fixed all that.  Something tells me that’ll work out much better than just telling them the last guys were idiots.  Because if I keep doing that, then I might as well be trying to talk the Vatican out of religion.

Read my mind

Man, talk about your all time screw-ups!!

OK, here’s what happened.  I got a ticket the other day to truncate a table in a prod DB.  Simple enough, right?  So I wrote the guy and said, are you sure you want me to truncate this table?  He said, yeah, it’s taking up over half of our space.  I said ok, just give me a couple mins.  So I connected and truncated the table.

Well, then I get a call like 10mins later and the guy was frantic.  What happened to my data?  Well, you told me to truncate the table.  NOT ALL OF IT!!! So this is a valuable lesson isn’t it?  Always be sure what you’re asking for and never assume.  I of course didn’t assume anything.  We get tickets to truncate entire DBs even all the time so it’s not that big of a deal for us.  Anyway, just be aware that many users have a completely different database vocabulary than you do.

Is this industry fun or what?

The hidden update

I had a cool situation today that didn’t come to me right away so I thought I’d share it with all of you.  We’re going to be talking about the lovely deadlock today.  I can’t remember if I’ve ever talked about it before, but I am now so there…

OK, here’s the situation.  I was called over by our very sexy web team lead (George), who told me that they were having deadlock issues on the web portal DB.  Fine, I’ll put a server-side trace on it and see what comes up.  For this I used the Locks\DeadlockGraph.  Once I got my info back, I noticed that we had table1 and table 2 and table3 in the mix. Table1 was a delete against itself.  Then another session ran an update against Table2 joined to table3.  The problem is that the update was deadlocking with the delete and the delete was losing every time.  And also, why was the deadlock on table1?  The update doesn’t even touch table1. 

For starters, all the tables have an update trigger that pulls the inputbuffer and session info for the spid that ran the update.  It then puts this info in a log table.  I don’t know why.  Unfortunately that wasn’t the problem.  I checked and none of the tables turned out to be views either so that avenue was dead.  The problem was just a tiny bit buried, but I eventually found it.  There was another table in the mix… table4.  Table3 had an update cascade set on its FK to table4 and table4 had an FK back to table1.  AH-HA… there’s your connection.  Now, as well, there’s wasn’t an index on the FK col in table1, so it was doing a scan.  Nice huh? 

So my recommended fix was as follows:

1.  Get rid of the auditing update triggers.  If you really want to log the action then put that code in an SP and call it after your update is done, but not as part of the main transaction.  Yes, I’m aware of the very minute risks in this, but they’re far out-weighed by completing your transaction so much faster.

2.  Put an index on the table1 FK column.  This is probably going to give you the biggest bang for your buck.  If it’s not doing a table scan, then it’ll get in and out faster so there’ll be less chance of deadlocking with the delete.  I believe the delete is also searching on the same col so it would really be worthwhile.

3.  Use updlock on the update query. 

My whole plan here is to get these transactions as short as possible.  It’s not enough to have efficient queries because if you’ve got a bunch of other stuff in the transaction then you might as well be doing a table scan every time.  And I know that reading table1/table2/table3 and all that isn’t easy to follow, but hey, we’ve gotta scrub these things for the internet, right?  Really the whole point is that you have to dig sometimes to find the source of something.  I knew the deadlock was on that other table and I could see the index scan in the execution plan, but that table wasn’t listed in the query or in the trigger.  So it had to be coming from somewhere.  So the basic point of this is to remind you of some of the things that are possible so you can remember to check these types of things too.

Should you be in Simple or Full Recovery mode?

Lately I’ve been bitching about vendors in my Marklar blog, but I thought I’d switch things up a bit this time and do some complaining here instead.  So this is a rough conversation I had with a vendor like 2 days ago.

Me:  The prod DB is twice the size of the archive DB.

Vendor:  That’s because the prod DB is in simple mode.

Me:  I don’t understand.

Vendor:  Well, DBs have 2 modes you can use to control when the log files get truncated.  Simple mode is… (interrupted)

Me:  Yeah, I know what the recovery models are.  I’m a DBA.  What I want to know is how the recovery model being in simple means that the prod DB is larger than the archive DB.

Vendor:  Well that’s what I was trying to explain to you.  When your DB is in full mode,  you can actually control when your log is truncated and you’ve got finer control over the size so you could go in there right now and truncate it.

Me:  But it’s in simple mode now so it truncates whenever it checkpoints.

Vendor:  EXACTLY.  So you have no control over that and that’s why prod is bigger.

Me:  That makes no sense.  Simple mode keeps the log truncated so you don’t have to control it.  It’s always truncating it.  So how does that make the DB bigger?

Vendor:  You don’t understand what’s going on here.

Me:  No, actually I don’t.  Why don’t you educate me.

Vendor:  Well like you said, you’re a DBA, so you should already know this stuff, but I’ll be happy to go over it again.

Me:  (laughs a little) Yeah, I’d love for you to do that.  I didn’t get it all written down last time.  Go ahead, I’m taking notes.

Vendor:  When you’re in full mode, you can take log backups and that will truncate your logs.  So you can control how big your logs get, and therefore your DB, by taking log backups at certain times.  You don’t have that option with simple mode.  You can’t take log backups, so you can’t tell it to truncate the logs.

Me:  I see… so if you’re in simple mode how does the log get truncated?

Vendor:  It truncates it automatically.

Me:  Ok, so if it truncates it automatically, when does it do it?  Is it on a schedule or what?

Vendor:  I really don’t know, I just know you can’t tell it to truncate it.

Me:  It truncates it whenever the DB writes a checkpoint.  So basically after every transaction.

Vendor:  Ok.

Me:  So since it’s almost always truncating, how does that make my prod DB bigger than the archive?

Vendor:  You still don’t get it?

Me:  Nope.

Vendor:  Well, we’ve got to get through this, maybe we’ll have time to get into it later.  For now, I’d like you to go ahead and have you switch it to full mode so we can get the size of the prod DB down.

Me:  No, I’m not going to do that.  That’s just stupid and it makes no sense.

Vendor:  Well, we can’t go any further until you do. 

Me:  Why not?

Vendor:  Because the prod DB is too big and this operation will take a long time. 

Me:  And how are the updates we’re doing on these tables effected by the size of the log.

Vendor:  Because it’s a lot more data to move.

Me:  But we’re not moving any data.  We’re only updating data.

Vendor:  Well, like I said, we don’t have time to get into all of this right now.  I don’t have time to teach you how all this works.  Just go ahead and switch to full mode and you’ll see what I mean.

Me:  No I won’t see what you mean because it’s stupid and it only shows you have no idea what you’re doing.  So you let me manage the space and my backups, and you worry about the updates.  And this is only a 4GB database anyway so it’s not like anything we do will take hours.  We’re only talking about the difference between a few seconds.  And there’s no way how much data is in the log will have any effect on how fast updates are.  So you just do your thing and leave me to do my thing.

OK, so that’s what I did the other night.  And it seems like I’m having these types of talks more and more with vendors.  It’s incredible isn’t it? 

Anyway, I hope you guys enjoyed this.  And for the record, simple mode truncates the log whenever the DB checkpoints so it’s always truncated.  And full mode needs a log backup to be able to truncate.  In case some of you were confused as to why this was a dumb conversation.  It’s actually dumb on a number of levels.

You’re the center of my universe

It will never cease to amaze me how people can be so narrow in focus.  It never even crosses their minds most of the time that someone who supports them may not be working right beside them all day long and may not be involved in their day to day activities enough to pick up on all their slang and terms.

So when I get a group of users like that I always have to laugh a little as I set them straight.  Yes, I’m the DBA for this company.  Yes I do backups for the servers in my control.  And yes I’m the one you need to call whenever you need anything at all SQL related.

However…

NO — I don’t have all your servernames memorized.

NO — I don’t know how big your DB is off the top of my head.

NO — I can’t tell you what rights everyone in your group has.

NO — Sending me a ticket that says “export my favorite tables” will not get you what you want.

NO — I don’t remember that conversation you had with the vendor about what we need to do to upgrade your DB.

NO — I didn’t realize that your app was renamed 2yrs before I got here and half of your group still refers to it as the old one.

So when you guys out there (and you know who you are) send requests to your DBA team,  know that they’re here to help you but you have to know that many times they’ve got several hundred servers to monitor and they can’t always keep your info on the top of their heads.  So at the very least whenever you send them a request, include the ServerName, DBName, etc with it.  They need to know what you’re talking about.

When is a ticket not a ticket?

I get this from time to time where a user will open a helpdesk ticket and assign it to me or my team and it has nothing to do with anything. Apparently it’s rather difficult to get users to understand what a ticketing system is all about.

Let’s take the ticket I got this morning. Here’s the exact wording pasted directly from the ticket itself:

I have a question about SQL.

Typically whenever I get a ticket like this I close the ticket immediately with the response:
OK.

For some reason today though I was feeling more mellow than usual so I wrote the user to inform him what ticketing systems are for and why that wasn’t really an appropriate ticket. Then I asked him what his question was and that if he really felt the need to put something like that in a ticket, to at least print the question with it. Of course I’ve heard nothing back from him. I did close the ticket however.

Like I said above… I get this from time to time at my various gigs and it’s really hard for someone like me to not be a complete smartass about it. I got one at my last gig that said:
I looked up what you were telling me about indexes and you were right.

Now, while I suppose I have no real problem with you creating a ticket to officially document that I was right about something, don’t assign it to me. Assign it to my boss.
I just wanted to officially document that Sean was right about one of the basic principles of indexes that he discussed with me. He should not only get a raise, but should have his pick of your daughters.

So now that I’ve ranted a little, here’s how to correct the situation. And this is to you users out there… tickets are for verbs… stuff you actually want us to do for you. You’re free to come by or send a regular email if you want to praise us for being right, or to ask a question because we’ve always been right in the past. But unless you have something for us to actually do, keep it out of the helpdesk system.

Experts are Sharp

You know I was thinking just this morning about the last round of interviewing I did to find a new DBA at work.  And that of course got me thinking about some of the interviews I’ve done in the past.  There are a few that really stick out.  The ones that are sticking out right away are the ones who didn’t know anything and claimed that they had so much experience and were so good that they didn’t have to be bothered with memorizing every little thing anymore.

This astounds me because all the experts I know are really sharp and on top of their game.  So what these guys are telling me is that they’re so good they don’t have to demonstrate even the most basic knowledge of SQL because they’ve transcended above that?  If that’s the case then my mother’s 100x the DBA any of us will ever be because she doesn’t know the first thing about it.

I remember this one guy especially.  He claimed both on his resume and in person to be an expert in query tuning.  He said, I’ve never found anyone who’s my equal at tuning queries.  So armed with that bit of knowledge I set about quizzing him with the basics.  I mean after all, you have to just get the basics out of the way, right?  I asked him if he had ever worked with exec plans.  He said of course, you don’t tune queries without them.  I said, that’s what I think, but i just wanted to make sure we were on the same page.  And I then asked him how expert his knowledge was of exec plans.  He said he was a very deep expert and didn’t know anyone with his knowledge.  Wow, now I’m getting a little nervous, right?

So I started with the basics.  What’s the difference between an index scan and an index seek?  Well, I’m not sure the exact difference, but I know you want to get rid of one of them.  OK, which one?  I can’t remember.  Um, ok.

So what’s a bookmark lookup (this was back when SQL2K was stull ubiquitous)?  I’ve seen it before, but I’m not sure what it does.

We went back and forth like that a couple more times and I finally broke down and told him that there was no evidence that he had ever tuned a query because he didn’t even have basic knowledge of exec plans.  I asked him what he was basing his claim of being an expert on.  That’s when he let me have it.  Look, I’m an enterprise DBA and I don’t have to know every piddling definition you dig up out of BOL.  Maybe someday when you’re at the level I am you’ll understand.

Um… ok, I’d say we’re done, huh? 

So like I said, I was thinking about that this morning and while I can’t keep up with everything, and nobody can, I like to think that I’ve got a lot of the basics covered.  And the real experts certainly know their stuff.  Go ahead and see how many people would follow her if you asked Kalen how big a SQL page is and she couldn’t answer.  And how many people do you think would follow Paul Tripp if he couldn’t tell you what DBCC CheckDB() was for? 

It just doesn’t hold water.  So for those of you out there thinking you’re all the Pooh, go test yourself and see how much knowledge you really have.  You may find out you’re not as hot as you thought.