SQL Quiz: Toughest Challenges

Blast those pingbacks; I lament feeling left out of Chris Shaw’s quiz, and the next thing you know, he tags me.

Here’s the questions:

What are the largest challenges that you have faced in your career and how did you overcome those?

First answer, a technical one:

One of my biggest challenges was designing a database that would handle millions of rows of syslog data; it was a SQL 2000 box, and the budget for hardware was tight. We started off with a dual-core machine, with only 4GIGs of RAM, and yet we had to analyze 100,000 rows per minute, looking for patterns. We also needed to report trends to the customer on a monthly basis, so I did some reading on data warehousing (Kimball, obviously), and started building fact tables. It became easily visible that we were going to need to partition the data so that the server wouldn’t need to slog through 90 days worth of data when looking for a minute’s worth; so I started playing with partitioned views.

One problem: partitioned views wouldn’t use parameters to activate partition exclusioning. I searched high and low for the answer, got into several arguments over execution plans with people on the newsgroups about why they thought it was working (they were clearly wrong), and I was just about to give up when I realized that dynamic SQL could save the day. I rewrote all of my procs to use dynamic SQL to activate the partitions, and I as off to the races. I ended up using something that most DBA’s would agree could easily be misused, and it solved my problem. It really drove home the fact that a good database developer will have a toolbox full of stuff; you never know when a left-handed screwdriver will come in handy 🙂

My second answer is ethical in nature; as most of you are aware, DBA’s are guardians of some of the most precious assets in a company: their data. I had just started working for a company as their all around DBA/report writer/developer/printer support person when they had a change in senior management. Shortly after the change, this company was being audited by their parent company; the auditor came on site, and at one point, asked me to run some numbers for him from our Enterprise Resource Planning (ERP) system. I agreed, but asked for more time, since I was arm deep in a printer at that point (I was serious about the printer support above).

Between the time that I finished repairing the printer and could get back to my desk, I was approached by the head accountant who asked me to “run the numbers for the auditor, give them to the Chief Operating Officer, make my computer look like it was doing something and go home for the rest of the day”. In other words, hand off the data and get the heck out of Dodge; the accountant would “make sure” the auditor saw it.

I wish I could say I took the high road, and say that I told the accountant “no, I’ll deliver it myself”, but I didn’t. I went back to my desk generated the data file, saved it to a disk, started a long-running query on my machine, handed the file to the accountant and left. I went home, and started working on my resume. The next day, the auditor was gone, the COO was trumpeting our success, and I told my manager about how icky I felt. Two months later, I was working at a new job, and I got a phone call from my former manager; apparently, the company was being audited again, and he invited me to share my experience with the new auditor. I did so, and shortly after that, most of the upper management (COO, CEO, CFO, and the accountant) were gone.

Is there a lesson in this? I’m not sure; I clearly didn’t do the right thing the first time, but things still worked out OK in the end. The only apparent victim was my sense of morality, but still, I walked away wishing I had handled the situation differently.

think I’ll tag SQL Sister and Tim Benninghoff now.