I am a huge fan of SQL Server. Don't get me wrong, there's nothing wrong with Oracle per se, other than it's tough to get it to run right and efficiently, and not for the amateur. If I was considering cross-platform work, Oracle would be the choice for me. For everything else, there's SQL Server, and when the next version comes out, look out!

But one “downfall” of SQL Server is that it becomes very easy to believe you can administer a production database with only Access skills, or worse, none at all. Some stories come to mind from my experiences.

First, I was doing a project for a large state agency, and at the time no one but the DBA could touch the SQL Server, or directly run script against the production server. I needed a very simple update script run, so I wrote it out, and gave it to the “DBA” to execute. He returns in about 5 minutes and tells me it doesn't run, and asked if I would mind taking a look at it. I did, and I had accidentally pasted two “set” keywords in a row. Even a Jr. Developer with a minutia of SQL experience should have seen this and been able to fix it. So, I fix it, and I asked him why he didn't just erase the extra “set”. is reply: I don't know SQL. Please keep it quiet.

Mind you, this guy was touted as a Sr. DBA, and responsible for about 16 databases which control some very important data. Unfortunately, this wasn't the worst of it.

One day I was using the restroom (that's important to the story), and he comes in and begins using the stall next to me. We begin some general chit-chat, and he's excited he can tell me something about SQL I might not know. His epiphany? “I went to a class, and we learned about indexes in it! I am so excited to start trying them out!” Well, since we had never been able to directly access the database, I had just solved some of our performance problems. I also got access to the databases from that day forward..

Another performance story. I was doing a project for an Internet startup, and I overheard they were having some SQL Server performance problems. The sign-ups for their site was killing their SQL Server with all the activity. I offered to take a look at it, but there was a definite anti-Microsoft spin at the company, and they were going to switch to Oracle because the owner had been told it was simply because Microsoft can't keep up with Oracle. I was working on a VB application for them, so they assumed wrongly that I didn't know databases very well.

So, they hire a team of Oracle (weren't employees of the Oracle Corp, just for reference) developers, and in 4 months and almost $300,000 later, it still wasn't up and running. The owner was bleeding cash, and was pressing the Oracle team to find out when the database conversion would be done and the bleeding would stop. They then informed him it would be about another month (and $100K), but even then they'd need to keep one of them one to maintain the database, or hire a full time Oracle DBA.

So I re-enter the SQL Server discussion with the owner. I ask him to let me look at the SQL Server configuration, and maybe I can buy him some time until the Oracle system is ready to go. Immediately, I see there are no indexes on the tables, and the stored procedures were very inefficient. By adding indexes alone, the performance gain alone went from ~98% resources used on the server (processor and memory) to less than 5% peak usage. The Oracle team was gone the next day (sorry guys), and the SQL Server portion ran flawlessly from that day on. But something so simple cost over $300K and wasted four months, and probably cost the company a lot of lost business.

My point is this: SQL Server is simple to use, but you have to educate yourself on how to use it best, because in some ways it makes it way too easy to think you are doing the right thing, when in fact it can have a huge impact on your performance. Next thing you know, you think the server can't handle it and you're out $20K on a new server which you don't need.

Case in point: I was hired by a bank to help a team of new developer's get up to speed in VB.Net and ASP.Net (I tried to get them to go C#, but that's another story). Their application was simple, but every day the resources on the server(s) were getting pegged and the entire system was unusable until the next reboot.

I decided looking into this would need to be a top priority. I checked the indexes, and they all looked good, but we did some tweaking there, and the problem was still occurring. I finally pull out the SQL Performance Analyzer, and quickly nail down there is a serious disk I/O problem. For example, I pull out a small table of about 200 rows, and do a select *, and it turns out it take 3000 disk I/O's to get it. The indexes were fine, and the record size was small, so there was something else going on.

On a hunch, I check to see if the disk drive is fragmented. Holy crap! It looked as if it had never been defragged, and come to find out, it hadn't. The network admin responsible for this production machine advised me the way he builds the machines means that they never have to be defragged. So I show him an image of the fragmentation on the drives, and he thinks I rigged it. End of conversation with him.

So I fix the problem, then ask for $200 for a program to schedule the de-fragmentation automatically. I don't get it, so I now have to manually defrag it once per week. Cost? Well, for me to track it down, and the time spent every week over a year and a half? Likely over $5000. Go figure.

I tried to get the employees there to take responsibility for doing it, but it never seemed to be a priority for them. So when I left to being a new project, I reminded them someone needed to do it. Well, I can assume for a while someone did. But I am back there, and yesterday there were complaints of poor performance. And guess what I found when I checked? The drives were very fragmented. One is so full and so bad, I think we're going to have to drop all the indexes, defragment, then rebuild them. That's gonna cost a ton of time and money. The $200 is going to be budgeted this time.

The tip: Make sure you keep your SQL Server data and index drives defragmented. Failure to do so can have a huge impact on your SQL Performance, since in the end, SQL Server relies on quick disk I/O to perform well.

-- Daryl