Instead of working, I blog.

Hey guys... sorry it's been so long since I posted, but life stepped in, and then the holidays were upon us... the good news is I've got a real doozy(sp?) for you this time.

I'd like to talk about job steps and what makes them effective.

When you create a job, it's always best if it's doing some actual work. Of course, we all take that as a given, but it apparently isn't. We just picked up a new site to manage at work a couple weeks ago, and yesterday I got an email saying that the data mart loads were failing. Well, actually they're not failing, they're taking so long, the ops guys just cancel them. I asked how long they'd been like that, and they said "ever since we can remember". So what you're telling me then is that you've not only never had a data mart load finish, you waited 2 weeks after I started here to tell me about it. Ummm... ok, (that's another discussion I guess).

So anyway... I go to the jobs that control these loads and they have several steps, one of which pulls from a linked server. I figured that was the main culprit, but I was wrong. That step finishes in just under a minute. It's the rest of the steps that take so long... and here's why!!

All the other steps do is run a series of very long, very complicated selects. The selects themselves don't use any indexes, and they're running against millions of rows. Remember though, these are automated jobs, who's seeing the results of these selects? Nobody, that's who, but it doesn't stop it from running all these selects, and eating up resources. Oh, and did I tell you, they all have TABX locks hardcoded. So, not only are these useless queries taking up all the resources, they're locking like 11 tables for hours.

After explaining this to the guy, I get another email from him shortly after, and he said, so can you also look at why the reports won't run?
Needless to say, cutting those queries from the job fixed the entire problem. Well, almost, I added something like 20 indexes to the DB and it purrs like a kitten.

Ok, so here's the moral of the story... when you put a step in a job, make sure it's actually going to do something. I liken this incident to the time right after we got our ticketing system and we were telling our users they had to submit tickets to get work done. Across the board, they stopped sending us any email whatsoever. Instead, we were getting tickets for everything. Here is an example of some of the complicated problems we were assigned through the new ticketing system:

-I am going through the documentation so I'll know how the DTS process works.
-Didn't you tell me you weren't going to be here next week?
-Do you want me to send you that SP to look at?
-I got your email, thanks.

I only wish I were exaggerating. I actually flagged those tickets as they came in and pulled them up just now... I just knew they'd come in handy some day.

Anyway, my head's exploding so I'll see you guys next week.