This is the first Darwin award I’ve handed out and boy is it a good one.

This post will have 3 parts: The award, the background, the article.

AWARD:

My first Darwin award goes to Tonya Brenneman, President of InfoIntegration in Dallas. Not only did Tonya fail to gather requirements for her system, but she ignored the advice of her DBAs and Microsoft. Read the article below and you’ll see what I mean.

BACKGROUND:

Back a couple years ago my wife got a job at InfoIntegration as a DBA on the project mentioned in the news article below. She soon started coming home with reports of how poor the design was and how things were going to fall apart. I took a look at a couple things with her to give it a 2nd pair of eyes, and she was not only right, the problem was worse than even she thought. On my own time I benchmarked a couple of the problems with the projected usage for the system. My benchmarks showed that once it went live, the system would suffer from such severe performance issues that it would be all but completely unusable in under a day. This would put the system having maintenance on it all day long just to keep up. I don’t know about you guys, but I don’t know any production system that can have defrags run against it 24/7. Anyway, when she went to her bosses with our findings, they not only ignored her, they actually blew her off and said that not only did they know what they were doing, but even if she were right, that portion of the system had already been written and it would be too much trouble to do it again. She kept making waves and was soon fired. She’s not the only one who left that job for a similar reason.

This should go to prove to you guys that taking database issues lightly isn’t a good idea. When your DBAs tell you something, listen. They’re warning you of danger for a reason, and if you’re so freaking smart then why do you even have a DBA? Trust your people and they’ll do good by you.

OK, with that said, here’s the newspaper article that came out just today on this. Trust me… read it… it’s a great article, and not only is it written well, but it really goes to show how important your databases can be.

ARTICLE:

Computer firm to let jail contract expire
Dallas County: In-house employees to take over inmate tracking system

05:45 AM CST on Wednesday, March 22, 2006
By KEVIN KRAUSE / The Dallas Morning News

The company that built the computer system for Dallas County that caused chaos in the courts and left some inmates languishing in jail for too long will not renew its contract when it expires at the end of July.

The county plans to hire its own staff to run the troubled system, which officials say will save money.

InfoIntegration’s president, Tonya Brenneman, told county officials in a letter that her company would help with the transition. She could not be reached for comment Tuesday.

A multitude of problems surfaced after the January 2005 launch of InfoIntegration’s $9 million Adult Information System, or AIS, which tracks inmates from booking to final case disposition.

“They felt it was in their best interest to sever this relationship and develop business in outside venues,” said Robert Clines, the county’s technology chief.

The county had been planning to rebid the AIS contract in October, he said.

“I don’t think it was a secret that we were looking at other options for support of the system,” he said.

Commissioner Mike Cantrell said InfoIntegration officials thought it was a good time to step aside, “given the history of what’s taken place and the publicity.”

“In my opinion, they did a good job.”

Mr. Clines said he will ask county commissioners for money to create five to seven positions to take over operation of the AIS system, which he said has overcome earlier problems.

The county still plans to contract for help in fixing minor problems, he said. But handling system operations in-house will be cheaper for the county, he said.

The county had been paying InfoIntegration about $460,000 for renewable six-month contracts, he said. Mr. Clines said he is trying to determine with the budget office how much the proposed new positions will cost.

“We will not be spending near that much money,” Mr. Clines said.

He wants the new employees hired at least 30 to 45 days before the end of InfoIntegration’s contract so they can be trained on the system. Mr. Clines estimates that 120 hours of training will be needed.

The county awarded the AIS contract to InfoIntegration in 2003. At the time, Ms. Brenneman had just formed the company after leaving the firm that handled the computer system for the juvenile department.

The new system failed to transfer information to the county’s old mainframe system that the courts use, causing some inmates to remain in jail for weeks or months after they posted bail or their cases were dismissed.

Microsoft was hired to evaluate what went wrong and issued a scathing report in October that detailed a list of mistakes by InfoIntegration and the county, including failing to determine what kind of system was needed and how it would function.

Mr. Clines said most of the more serious problems, such as security issues, have been fixed. He said a governance committee staffed by judges and representatives of the district attorney’s office and sheriff’s office was formed to review changes.

“All of the glaring problems, we have addressed,” he said. “These are the nuisance problems.”

In February 2005, the help desk was averaging 1,000 calls a month. It’s now down to between 300 and 350 monthly calls, Mr. Clines said.

He estimated it will take up to four months to iron out remaining problems.

Since 1992, all computer support in the county has been outsourced, Mr. Clines said. He called the new arrangement a hybrid, with some services to be contracted by outside firms and others being handled in-house.

Being a DBA is certainly interesting to say the least. And I can honestly say that one of the things I like the most about it is all the diversified topics we get to cover… esp in data modeling. I’ve modeled DBs for marketing, scanning, healthcare, killing chickens, restaurant inventory, political contributions, shipping drugs/medical supplies, and I’m currently working on a tax return DB.

You can’t help but really pick up some interesting information on these projects. For example, not only can ckickens live a long time after you cut off their heads, large processing operations can kill over 600,000 chickens a day. Just think about how many chickens that is, and since they’re only about 6 weeks old at the time, they’re easy to replace quickly. I have also learned that there’s an acceptable amount of rat feces in canned chili, and the rate at which hospitals give you infections due to mishandling and then charge you for the treatment is alarming.

However, probably my most bizarre encounter with a schema to date happened a few years ago. I only mention it now because I happened across my notes and thought it’d make a cool post. A friend who was in vet school at the time asked me to write him a small DB to keep track of phychological disturbances in animals. At first I was prepared to turn him down because I just didn’t have the patience for that kind of nonsense, but my curiousity got the better of me.

Among the things I learned during that project were:
–Why cats suck or chew on wool.
–Why dogs chase their tails.
–Why calves cross-suckle (that’s when they suck on each other’s different parts instead of their mother’s milky part).
–Why horses shake their heads.

I’m really curious… what are some of the more interesting projects you guys have come across in your modeling travels? And what are some of the more interesting things you’ve picked up along the way?

About a month ago, we finished our last round of audits and I wanted to share a little bit with you about how to talk to auditors, or better yet, how not to talk to them.

Here’s a fine example of how you should not talk to an auditor.
One of our guys, I’m sorry to say a DBA, walked into a room where the bosses were holding a meeting and announced that none of our backups across the board were working, and had been failing for a couple days. We’re completely unprotected!!

Of course, you guessed it, they were meeting with the auditor from D&T.; In his defense, he said he had no idea that was an auditor and he never would have said that if he did. OK guys, let’s put this rule on the table right now. Don’t go announcing things like that at all. If there’s someone in the room you don’t recognize, keep your mouth shut, send an email, pull them out of the room, whatever, but don’t just announce that you’re shop is falling apart.

Most companies will tell you when the auditors are going to be there, and will tell you to refrain from discussing sensitive business outside of your immediate area. This is an excellent tactic, and we do that too, so why this incident happened, I’ll never know.

So how should you talk to an auditor then? There are 2 areas you need to worry about.
The first is before and after the interview. Auditors like to come up to your desk or pin you in the hall and ask you questions about your environment. That’s fine for them, but you need to get with your managers and decide how you’re going to handle this situation… remember, anything you say can and will be used against you in a court of audit. What we do is we refer all questions back to our boss. If an auditor asks me a question outside of the interview, I say, send your question to my boss. He then asks me the question, and I in turn send it back to him. This way, the auditor can’t trip you up on the spot, and you won’t accidentally say something you’ll regret. And it gets to go through the filter of someone else. Even if you know the answer, Don’t say anything. Make them go through channels. Now you may not choose to do it this way in your shop, but it’s worked very well for quite a few places I’ve been in.

Second is during the actual interview. Auditors will quite often call you in to ask specific questions. Quite often, you have someone else in your dept sitting in with you to make sure everything goes well… just kind of a witness. When the auditor asks you questions here, you may answer them, but use as few words as possible. Never say 20 words when a yes will do. Treat this just like testifying in court. Answer the question asked, no more, no less. It’s tempting sometimes to want to explain yourself or your reasoning for why something’s done, but it’s not relevant here. In the case from above, I would only hope the the DBA wouldn’t answer like this:
Q: Do you backup the DBs every night?
A: Yes… but we quite often go several without our backups working, and we never test restores, and it doesn’t matter anyway, because the drive we keep them on is old and slow and will probably die any day now, and since they’re not pushed to tape we’d be in real trouble if that happened.

The clear answer is simply yes. Then SHUT UP!!!

Also, don’t let them rope you into answering questions that are outside your area. Anything not having to do strictly with DBs is none of your concern. Some sample questions are…

Q: How many users inside Solomon have elevated rights to create accounts?
A: I’m not responsible for Solomon. The Solomon admin would have to field that question.

Q: What method do users use to authenticate to your intranet?
A: You’ll have to ask that question to the intranet admin.

Q: How many users have db_owner in the ADP database?
A: At this point I could only guess, but send me that question in email and I’ll get you an anwser.

Notice that last question was in your range and it still didn’t get answered? Auditors will write down whatever you tell them on the spot, and move on. Don’t guess at anything. If you’re not sure of an answer, say so, and ask them to submit it in email and you’ll verify the answer and send it to them. This is crucial because you won’t get a 2nd chance to answer that once they’ve written something down. You’ve got a few dozen servers to look after, and nobody expects you to have all the answers off the top of your head.

Ok, that’s all I’ve got… happy auditing!!

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.

OK, I realize that this can’t be a perfect world, but why MS can’t make a simple uninstaller that actually does what it’s supposed to is beyond me. I have uninstalled Yukon Sept CTP on several boxes and always in the exact same order. Sometimes it works, sometimes it doesn’t. Whenever it doesn’t work, it leaves you unable to install RTM.
The usual fix for this is to reinstall the CTP, and uninstall it again. This for some reason manages to uninstall all the pieces that were left behind before. And of course, RTM doesn’t provide an uninstall wizard so you can’t use that.

One problem I recently ran across was that I wasn’t able to install RTM, or reinstall CTP. The install for CTP said it had incompatible components and couldn’t install. But the uninstall wizard said that there were no components to clean up. So now there’s nothing left to do but uninstall manually.

But how do you know what to uninstall? The answer came to me from Donald Farmer at MS.

When you get this situation, check the log file under C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files
What you want is the *_Core.log. They should all have pretty much the same info in them as the same components are probably failing all the time.

Reading the log, you will find a reference to the CLSID (a guid) of the component that setup “thinks” is installed.

Here’s a clip from mine.
Product “{982DB00A-9C4E-436B-8707-18E113BAA44C}” versioned 9.00.951 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1399.06
The Product Name is “”
Product “{90032DD0-ABEE-4424-AC1E-B076BDD4E350}” versioned 9.00.852 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1399.06
The Product Name is “”
Loaded DLL:C:\WINDOWS\system32\msi.dll Version:3.1.4000.2435
Error: Action “PerformSCCAction2” threw an exception during execution.
Return Code: 70032

Copy that guid to clipboard, search for it in the registry and purge the registry of every reference to it. Just highlight that long GUID inside the curly brackets, and you should be ok. I included the brackets in mine, but I haven’t tested without it… though again, it should be ok.

I’ve seen one instance where this didn’t clear it up either. It left a bunch of .manifest files in there I think. I’d have to check to be sure, but just delete everything in that same log folder and try again. That should work for you this time.

In the future, I sincerely hope that instead of just doing a check for incompatible components, they actually delete the entries as well. That would be really helpful.

This week I want to talk about what’s been happening recently on SSC. As probably most of you know, I wrote the first in a series of articles on interviewing. In case you haven’t heard of it, you can read it here.

During the discussion on this article, some of you said it must have been a joke, while others reverted back to the dark ages and performed a modern version of the Inquisition. I’ll get to how I feel about that in a minute, but first I want to explain what the article was all about.

I wanted to say this so many times during the discussion, but I really wanted to see how far it would go… and originally, I was going to come clean in the 2nd installment, but I just don’t think it can wait.
Let me say first off that this was a very carefully crafted piece. Didn’t any of you catch onto the fact that the article was called “How to Mess Up an Interview”… GET IT?? I messed up the interview… (actually, one guy did get it… thanks, Paul, and while I think genius may be stretching it, I appreciate the vote of confidence.)

I’ve always had a flare for the dramatic, and when I conceived this series I had one thought in mind and that was to really drive the point home. So what I decided to do in this first installment was to give the readers just a little something to show them the kinds of things that can start out innocently enough but then completely blow an interview. The published draft of the piece was probably my 7th rewrite. Believe it or not, I actually got the idea from one of the worst interviews I ever had the pleasure to conduct. This guy went off on how dumb his current employer was, and how he was the only one there who knew anything at all. In the course of answering a question about the biggest disaster he had ever been a part of, he had a few choice things to say about the people he worked with including referring to the NOC guy as a stupid N-word… 3 TIMES!!!
When I asked him about his team and how they work together, he referred to his fellow DBAs as Jesus freaks who would just assume hold hands around the server and sing Kumbaya than to actually learn anything to actually fix the problem. It amazes me sometimes the lengths people will go to keep you from hiring them.

So anyway, I wanted to write this first installment in a way that would completely fail an interview. I started with that guy’s interview as a model, but I had to be more subtle. There’s no way I could possibly say the things he said, so I wrote one draft after another trying to tone it down to not only something believable, but something that would get my point across. In other words, I wanted to be that guy you wouldn’t hire because he’s just a little too… off, I guess.

The point of that first piece was to show that regardless of what you have to say, if you say it wrong, nobody will listen. There was some good solid advice in that first installment, but many of you refused to even see it because of the way it was presented.

Some of you weren’t offended at all. You are my main audience. Others just didn’t listen because they didn’t like the way it was presented, but they kept their professionalism and just stated that they disapproved and went on about their business. You are also my audience. However, the hardcore zealots out there who not only denounced me and my writing, but also SSC as a whole, well, you are not my audience, and frankly, I’m ashamed to be in the same community with you. You embarrassed yourselves, and you embarrassed us all with your childish and petty behavior. Imagine professional people going on a public site and making a spectacle of yourselves like you did… and even cursing. You were so self-righteous, and smug, and proper, that you forgot how to conduct yourselves in public.

And for those of you who had Steve remove you from the list because of it… I say good riddance. If you’re going to be that way we don’t want you anyway, and you’re only hurting yourselves. Personally, I enjoy getting my SSC newsletter every day, and there have been a lot of good articles. If you guys want to screw yourselves out of the knowledge you get from SSC because of one carefully-crafted piece, then you deserve what you get.

Well, let me just say that I’ve proven my point.

I did get a lot of support privately though. Many of you wrote me to express your support. I want to thank each and every one of you who did that. And those of you who supported me publicly, I thank you too. I didn’t think anything I said was offensive either, but apparently, that’s the kind of thing you can’t dictate.

That’s fine though… you guys turned a simple article into some kind of holy war and gay-bashing party.

And by the way… that gay thing really wasn’t a slam on homosexuals. It’s just an idiom that some people use. You know, like those other literal idioms like ‘pulling your leg’, ‘heart of gold’, ‘broken heart’, ‘be an a-hole’, ‘look a gift horse in the mouth’, etc. And the point of putting in the piece was to show you in the next installment that you have to re-train yourself to not use common phrases that could possibly offend. I got called down at work once for using the phrase ‘partners in crime’. You just never know what will set somebody off (evidently).

So now it’s up to you guys… do you want to see the rest of the series or not???
I await your response.

This rant is something very close to my heart. One of the things that really gets to me is the hours production DBAs are expected to work without any of the benefits that come with it. We’re constantly being asked to get up in the middle of the night working on problems, and on those nights we do get to sleep we’ve been up very late rewriting code, or reviewing indexes, etc. On average I would say that most real production DBAs get about 4-5hrs of sleep a night. And by ‘real production DBAs’ I mean those in really busy shops, it has nothing to do with skill level.

So with all this outside work what do we have to show for it? We get to be in the office 9-6 every day as well. In almost every job I’ve had I’ve been expected to keep full office hours as well as work almost a full shift outside as well as be on-call 24/7. That includes holidays too because more often than not, the users aren’t going to be working on the holidays so the company expects that to be the perfect time for me to come in and perform an upgrade without having to pull extra downtime. And of course, I can’t do it from home over VPN, I have to actually come into the office and leave my family to sit in the server room.

What really gets to me is that these are companies that have full VPN access. There’s no excuse for me to have to come into the office to do something I can do just as easily from home. I’ve had it out with so many IT managers and they just can’t get around the old American mindset that the only work that can be done has to be done in the office. Technology should be freeing us from such archaic thinking, but it hasn’t effected us as of yet. There are still plenty of companies out there who think you have to be in the office to be effective, when the exact opposite is true. If you actually pay attention to how many people come up to your desk to interrupt your work, and how many times you stand around and talk to someone about complete B.S. for 20mins at a shot, you’ll find that spending a little quality time alone with your work at home will be a welcome change.

The whole thing is really a huge contradiction anyway. My last boss used to work from home when he wanted to really buckle down because the office was too distracting. When we requested the same thing he said there was no reason for us to be out of the office to do our jobs. He actually said and I quote: “People work 9-6 in America.” and “VPN is designed to be used after hours so you can work more than your 8hrs, and so that you don’t have to come in in the middle of the night when there are problems.” What a serious load of CRAP!! Another reason they like to give is that people in other departments will want to start working from home if they let us, and they can’t get that kind of thing started. My answer is usually the same thing… well, the users in payroll don’t get called in the mniddle of the night and have to get up and look at systems for hours at a time. And the next time one of them complains that they should get to work from home because I do, just tell them that would be fine, but next time I get a call in the mniddle of the night, I’ll call you and make you get out of bed too… because if I have to be up, you have to be up. Sure, other departments have to come into the office all the time, but quite often they’ve got jobs that either can’t be done from home, or their job stops after business hours. They get to go home and forget work.

The life of a production DBA is different. Being always on call forces us to do things differently. We can never go to a baseball game or a movie or even a family gathering where they can be out of reach. We can get called away from our plans on a moments notice. I don’t know the last time I saw an HR emergency that called the benefits girl out of a movie or woke her up in the middle of the night. It just doesn’t happen.

As a reward for this level of service, we should be allowed to pretty much come and go as we please. If we need to work from home for a couple days that should be ok. If we need to take off early most days, or take a half day, or whatever. I had to pay an extra $300/mo for childcare because my boss wouldn’t let me get off in time to get my little girl after school… because people in America work until 6… period.

This is the kind of ridiculous thinking that’s holding us back. It increases traffic, drives up your blood pressure, and wastes gas. It even costs the company money because they have to have space for everyone when they could use fewer cubes if they let people work from home a couple days a week someone else could be in your spot while you’re not there.

My current job isn’t like that at all. We’ve got so many remote sites and we do everything over the wire anyway, so being on the wire from home isn’t any different than being on the wire in the office. As long as we do our work we’ll be able to work wherever we like. I personally would never abuse my privilege because I don’t want to lose it, but I also know the second I do, the party will be over and I’ll be in the office every day without fail.

What do you guys think? What are your experiences and thoughts on this topic?

I don’t know if any of you remember last week’s post where I talked about some of the bad code I’ve seen, and the reasons why many developers don’t seem to ever learn their lessons. Well, the most amazing thing has happened that may just explain the whole mystery.

I was looking through my wife’s copy of Redbook this week and ran across an article about autism. To make a long story short, the article states that autistic people quite often go into programming careers. That really explains a lot doesn’t it? As it turns out, programmers are genetically predisposed to buying their underwear at K-Mart. OK-OK, I’m just giving you guys a hard time. I’m just kidding… but Redbook really did say that autistic people tend to go into programming. If you don’t believe me check it out. It’s in the Sept 2005 issue on pg 197.

Ok, onto some serious business. I wasn’t going to write on this topic again, but there was such an overwhelming response last week, I thought some of you needed more time to get it out of your system. And while some of you had some good arguments, others of you went so far as to insult me personally, while still others of you left me with some really interesting grammar lessons. I really don’t mind the insults though. I kind of expect it actually after a posting like that.

Now, I think some of you are confusing a rant blog for real life. Just because I come on here and rant about something doesn’t mean that I don’t have a teamwork attitude at work. In fact, I’m always the first one to turn on People’s Court(yeah, yeah, time for Wapner) when our developers need help. I teach SQL classes to show them the right ways to write code, and even give them DOs and DON’Ts. It never makes any difference though. When they turn their code in it always looks the exact same way it did before. Go figure. But of course you have to have a teamwork attitude with everyone. How else would you ever get your job done?

One of you said that it was a training issue. This is quite true. Training is the key. The problem is in getting them to get the training. You can make them sit in a classroom but you can’t make them care. And that doesn’t mean that I think all devs are bad either. There are exceptions, and I’ve even met a couple.

I really don’t expect everyone to know everything, or even most of everything. And I certainly don’t know everything, nor do I claim to. I’m sure that the answers I give to a complicated problem are not only different from what someone like Kalen Delaney or Kim Tripp would give, they’re different from the answers I’ll be giving 5yrs from now. Just as they’re different from the answers I gave 5yrs ago. The good news is that the SQL superstars go through the same thing. Their answers changed based off of their current level of experience.
What I do expect though is for some of the basics to rub off after a certain amount of time. I expect that devs would know not to hardcode index hints, or put a clustered index on description varchar(200). I would also expect that you wouldn’t put extra indexes to speed up inserts, or put 4 indexes on a 2 column table. Yet these are all things I’ve seen numerous times.

Maybe we should all swallow our pride and concentrate more on getting the job done and less on pointing fingers. I think I have a solution though. Why not base a bonus structure on the quality of the code when it hits production? So, you’ve got a 3 month window. You start out with $500 as a bonus whenever you push a major release into production. Then, for every problem caused by something you coded, you lose a portion of your $500. So, the DBAs have to fix a horrible query, you lose $100. They have to take out an index hint, $50, etc. Now answer me honestly… how many developers do you think would learn to think in sets then? How many times do you think our advice would go ignored? How many of the lazy ‘select *’ queries do you think we’d see? Anyway, just a thought.

And the same goes for DBAs too. How about the same type of structure for the systems. How many times do you want to lose your bonus because you didn’t back up a DB properly, or didn’t test the restore? How much money do you want to lose because you did something stupid and brought the system down, or left the where clause off of a delete and killed the whole table?

The problem we have at my office is that the devs don’t write good code, and the users have become to expect that SQL runs very slowly. A very good example comes from a conversation I had with one of the production managers last year. She called and said they were having trouble with the reports. They’re taking a long time to come up. I said, ok, which report. She told me which one, and said that it was taking about 35mins to come up. I said, and how long does it usually take? She said… it usually comes up pretty fast. The normal time is about 25mins. After I fixed the immediate problem I looked into the queries themselves. Once I finished with them, they came up in less than a second. It’s very easy for users to get used to DBs performing poorly. If it’s never been a fast system they have nothing to compare it to. It’s up to all of us to set their expectations. We had a problem last week with another bit of code in which they were complaining that it had gone from 4secs to over 2mins. One of my DBAs took the ticket, and told the customer that the performance was unacceptable. The customer said, yeah, I know, that’s why I’m calling. It used to take 4secs. My DBA said, no I mean the 4secs is unacceptable. Let’s get that time down.

OK, maybe next week I can finally talk about batch deletes like I promised last week. Until then, remember… K-Mart sucks.

One more thing…

Don’t forget Rahul Sharma is a cheat and a fraud.
So nobody buy his book… ever, Ever, EVER!
His book is: Microsoft SQL Server™ 2000: A Guide to Enhancements and New Features

This topic has been on my mind a lot lately. Exactly what is it about developers that makes them write such crappy code? I mean seriously guys… would it kill you to put just a little thought behind the code you write?
I’ll get to a couple examples in a minute, but first I want to explore the reasons why there’s such a difference in mentality.

I think part of the reason is because developers are quite often under such strict deadlines they just don’t have time to explore a lot of options. More often than not, if the query works, then it’s good enough. I can’t tell you how many times I’ve heard a developer say “well, it worked just fine on my workstation”. This is after having 50 instances of their query running in production at the same time, and each one blocking everything it touches.

One of the biggest reasons I feel though, is lack of education. Even under strict deadlines it doesn’t take any more time to write something correctly than it does to write it so it’ll bring the system down. I give training sessions at work sometimes for the developers downstairs, and it’s amazing to me how many of these top-notch .Net guys don’t know the simplest things about SQL.

A quick example:
We have a table that holds just a single value at a time. It changes every week or so, but at any given time it only holds 1 value. 1 row, 1 column.
Here’s the query brainchild chose to use.

Select Top 1 * from tblCurrentBatch b1 where ID in (select ID from tblCurrentBatch b2 where b1.ID = b2.ID)

I really can’t stress this enough… there’s ONE value and ONE column. the DDL is thus:

Create Table tblCurrentBatch
(
ID bigint
)

Dude, you may be under deadline, but you can’t tell me that’s the reason for this gross lack of knowledge.

Probably one of the better reasons is that they simply don’t care. The above example is a clear example of this as well I think. A fair amount of developers I’ve talked to see a clear distinction between them and DBAs. It’s their job to make the query run, and my job to make it run well. Even when I teach them how to write better code they quite often do it their way anyway. They just don’t care. Then, they go out of their way to compile the query inside the code instead of making SPs so we can optimize it later. It’s their way of fighting back. This way you have to come to them, explain the problem, and then get it fit into their busy schedule to be changed… and that’s only if they agree. Their going to keep their code in production as long as possible, and they’ll be damned if any DBA’s going to tell them how to program.
I’ve written on this topic before, and I received a comment from someone that just doesn’t make sense to me because I’m a DBA, but I think it sums up the developer’s mind quite well.

This email is completely unedited and exactly as I received it:

You are a fucking idiot. Everything has its place – and having a dba tell me I can’t use something I will respond with, YOU ARE FIRED – cause the database isn’t the product. The business logic is what is sold, and performance is a factor, not the driver.

Frankly, this is just absurd. I’ll tell you what’s going to happen in this scenario. He’s going to release his product and the customer will love it in the demo. Then he’s going to put it into production with 100 users hitting it and it’s going to crash and die. Then all his business logic will be out the window and he’ll be running to us BEGGING AND PLEADING to fix his code.
The truth is we simply live in different worlds. We live in a sandbox where everybody has to get along, and they live in a closed world controlled by strict deadlines.

OK, so what kinds of things do we usually see from these crack developers? Well, I’ve seen the following…

Problem:
Delete all records past a certain date.

Developer Solution:
Open a cursor against 75 million rows and compare each row to the given date and delete it if it passes.
Runtime: 18 hrs.

DBA Solution:
Delete from table where date < compareDate
Runtime: 30mins, depending on how much data was deleted.
*Make sure to use batch deletes… I’ll write about that next week.

———————————————————

Problem:
Select a specific batch number from a table.

Developer Solution:
Select * from BatchNumbers where BatchID like ‘%12345%’
Runtime: 45secs.

DBA Solution:
Select * from BatchNumbers where BatchID = ‘12345’
Runtime: < 1sec.
* I know, it’s varchar… what can I do?
———————————————————

Problem:
Get a list of customers with a sum of all their orders.

Developer Solution:
A complicated cursor solution that kept track of different variables and temp tables. Very limited in usefullness. I believe it was about 3 pages of code.
Runtime: 3hrs.

DBA Solution:
Select Customer, count(*) as ‘Total Orders’ from Orders group by Customer
Runtime: < 10secs.
*This went against a couple hundred million rows.

I’d love to hear about your similar experiences.

And remember… Friends don’t let developers write SQL code.

One more thing…

Don’t forget Rahul Sharma is a cheat and a fraud.
So nobody buy his book… ever, Ever, EVER!
His book is: Microsoft SQL Server™ 2000: A Guide to Enhancements and New Features

There has been some buzz lately that everyone should know about. There is an author out there who has written several articles on SQL Server and even a book. This author is a complete fraud. He is a plagiarist who has ripped off the hard work of many others and I could only hope that Microsoft sues his ass. Unfortunately it’ll probably his publisher who will suffer.

As an author, I am outraged by this fraud who profits from others’ work and research. I will do whatever I can to make sure that everyone knows that we won’t tolerate this type of behavior. I work hard on all my articles and even the sarcasm in them. I borrow from BOL or other sources sometimes, but I always cite the source.

Steve Jones at SQLServerCentral.com wrote a blog on this topic as well, and he’s already contacted MS and some publishers to let them know this has been going on.
Kevin Kline has also blogged on this.

The fraud’s name is Rahul Sharma.
So nobody buy his book… ever, Ever, EVER!
His book is: Microsoft SQL Server™ 2000: A Guide to Enhancements and New Features

Check out “his” sample chapter here:

http://www.awprofessional.com/articles/article.asp?p=28787&rl;=1

Compare that to the BOL replication stuff if you’re bored.

Rahul my friend, you haven’t heard the last of me. I’m not going to let this go. You’ve been faking your way through for a long time now, and I’m going to make it my personal mission to make sure everyone knows it.