SQL Quiz, Part 2: Toughest Challenges

Chris Shaw posted a new SQL Quiz yesterday, asking “What are the largest challenges that you have faced in your career and how did you overcome those?” Brent Ozar tagged me when he responded to Chris’ challenge.

Can I super-size my SQL database?

When I left my position at a small logistics company in Indianapolis, I went from a couple of relatively small databases to a pretty large environment. I’m talking 10mm row tables to billion row tables, with comparable increases in transactional volumes. I quickly found that developing in a small environment can be very different from developing in a large one.

To improve my skills, I hit the internet. I discovered the value of community resources like sqlservercentral.com and the joys of reading technical blogs. I also became intimately acquainted with Books Online. Perhaps most helpful of all, I discovered how knowledgeable and talented some of my new colleagues were; if I didn’t know the answer, I sought out their advice.

It’s like a promotion but with longer hours and no pay increase.

A few years back, I was given responsibility for a pricey software deployment. I knew I was not qualified to lead the project, and even mentioned it to my boss, but there wasn’t really anyone else to do it. There was quite a bit of work to be done, and not a lot of time to do it. In addition to leading the project, learning the new software, and designing custom modules, I also needed to learn about SAP integration. And it didn’t help that all of the column names were in German!

In the past, I had always been a self-reliant, one-(wo)man army, but I knew I needed help. I asked for a team and was allowed to hand-pick individuals from different groups to form a deployment team. There is no doubt in my mind that I could not have completed the project without this team. They were knowledgeable, resourceful, hard working, and fun to be around. The last part might not sound like a big deal, but when you start working 80-100 hours a week, you really don’t want to be around someone obnoxious.

The project certainly had its ups and downs and its rough spots. Some were the result of inexperience, and some of the issues were completely outside of our control. Nonetheless, the project was a success, and I certainly learned a lot.

Tag! You’re it.

I’m tagging…

0saves
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged . Bookmark the permalink.

6 Responses to SQL Quiz, Part 2: Toughest Challenges

  1. Brent Ozar says:

    You’re right about the move up in size: going from 10mm to even just 200-300mm row tables requires a big readjustment. Developers get so frustrated when their code flies on a 10mm row system and slows down in production, and it’s always “our” fault as the DBAs. Doh! Education is so important as a part of DBA life: we have to keep helping and training everybody if we’re going to be successful.

  2. I’m with you and Brent about the database size issue. Until a month ago, I was developing against 10% of the total data and my queries were running in tiny fractions of a second, I didn’t pay any attention to performance tuning. Now that I have all of the data in place, I’m revisiting all of my queries, optimizing the SQL, adding indexes, and generally doing all of the things that I’ve ignored since August. The change in scope is phenomenal and there are different techniques that I’ve had to pick up on very quickly in order to stay on top of my game.

  3. SQLDenis says:

    This is a common scenario where people develop on a test/qa/dev system without any capacity planning and a subset of the data. Once they go live…oops…blocking galore, snail paced queries, timeouts etc etc

    Also most people don’t update stats or defragment tables/indexes and over time it slows down too!!

  4. Phil Factor says:

    My great education was when I moved to a fast-growing Telecommunications company to take on the retail billing system. It was a culture shock, and a lot of relearning. A million rows sounds like nothing, but that was the daily feed of calls that went into the system. (SQL Server 7) I can’t remember the size of the table with the calls in (well over the billion) but ever since then, I’ve had a deep respect for SQL Server, and indexing strategies ever since.
    It is sooo important to develop on a database the same size as the production server, if you can. The characteristics of a billion row table are quite different from the outgrown excel spreadsheets that people generally develop on. If I could only choose one utility to have, as a developer, it would be SQL Data Generator. Ten million rows, sir? right away!

  5. David Stein says:

    Jeff Atwood has a good article on your first point. Everything is fast for small n.

    http://www.codinghorror.com/blog/archives/000957.html

    Just an FYI. :)

  6. Pingback: SQL Quiz: Toughest challenges | Ian Kirk - SQL DBA

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>