A Wandering Mind

It’s time for T-SQL Tuesday again, that great community event started by Adam Machanic (Blog | Twitter) where bloggers all over write about the wonders of SQL. This month’s topic: Crap code, and boy do I have a lot to choose from. It was hard to pick one example (my early days as a programmer were riddled with bad decisions), but I finally decided on writing about my first ever software release.

A long time ago, in a building far far away…

…there was a young man who had just joined the ranks of an operations group at a financial firm. He had quit his job as a retail store manager (a job he only took because nothing was available in his chosen field of social services) and taken a temp assignment as a means to pay the bills. His job was simple: run reports from various systems (a mainframe green-screen system, a SQL database), carry some checks around, and various other menial tasks. But still, it was nice to be out of the 12 hour workdays of retail.

Soon after beginning work, the man noticed that a great deal of his work amounted to punching in a bunch of repeat keystrokes and waiting for things to print. As a result, he quickly learned the ways of ExtraBASIC, and wrote macros to tab around the terminal screens. He also learned how to use the query designer in Access, so that he could quickly get data out of a database when requested by various teams. Within a few months, word had spread of the reporting wizardry, and he was offered a permanent job within the group.

The First Fail

While not officially a programmer, he was given several tasks around building utilities for the group he worked with. Abandoning Access for greener pastures, he downloaded Visual Basic Express and began whipping together tools. Requirements shifted on a daily basis; after all, the ops groups weren’t used to having their own private coder at their beck and call. The young man enjoyed his work, if only because it kept him out of the daily grind of operational processing. And what with the simple drag-and-drop designer, the actual coding was minimal. A click here, a quick line or two of code there, and voila, a functioning application. Naturally Access / Jet was still used as the back end database, since that was what was available.

When it came time to roll out the application, the team was gathered and a demo given. Everything worked fine, and the decision was made to start using the new app right away. Everything was in place, and the go-ahead given. But soon after the use started, problems began appearing. Data wasn’t being consistantly updated, operations took a long time to complete, results weren’t as expected. After several hours, the decision was made to go back to the previous method of operation.

When at first you don’t succeed, try, and fail, again…

The young man was discouraged by this early setback, but he learned from his mistakes. At least until the next release, which included such wonderful upgrades as using MySQL as the back-end (on a dusty desktop box) and optimized in-line SQL (as opposed to GUI generated code). It fared much better, lasting several days before major issues were uncovered.

Within several months, he was offered a job as a full time programmer. He quickly churned out code, and his teammates loved his work ethic. What they didn’t love was his propensity for writing overly complex, convoluted code, which also conveniently was bereft of any comments. He also had a knack for finding and leveraging new technologies, such as an open source job scheduler, SQL Server Express (installed on that same dusty little box), and random .NET classes pulled off the internet. This was great when it came to functionality, and the business units were pleased. That is, until he was on vacation and something went bump, and they were forced into manual processing while the man’s teammates frantically dug through .vb files.

Since then…

I’ve learned quite a bit about writing better code since that first debacle, with many, many more missteps along the way. I’ve written code that was far, far too complex for what it needed to be (think a whole class for a simple calculation), scaled terribly (a linked server as a source for an 8+ hour ETL process that involved three layers of nested cursors), and was completely undocumented. But I’ve also grown tremendously as a programmer, learning lessons such as:

1. Write simple, reusable code. Don’t try and be clever; just write what is needed and nothing more.
2. Don’t rely on GUI generated code. Use it as a basis, but understand what it does and tweak it to better suit your needs. I yell at developers constantly because they try and do something via the Management Studio interface instead of writing the code. Writing encourages understanding, which in turn encourages better code.
3. Have solid test plans in place, and be methodical about following them.
4. Code for future scale, not current. I still refer people to the paper written by the Google founders years ago, where they explicitly acknowledge and plan for greatly increasing activity.
5. Document your code to the teeth. Some day in the future, long after you’ve moved on and requirements documents are long lost, the code will break, or need to be updated, and your comments will be the only information available about why things were done the way they were. I don’t know about you but I’d rather have people singing my praises after I’m gone, as opposed to throwing strings of four letter words (as well as various hard objects) at my picture.

I still cringe when I look back on those early days. I’m actually good friends with the guy who was my first development manager, and every time he mentions the name of some application I wrote, I scowl and apologize for having given birth to such junk. Everyone writes code that makes their skin curl years later, but what’s really important is that we strive to learn and improve our skills on a continuous basis.

It’s T-SQL Tuesday time again! This month is being hosted by Amit Banerjee (Blog | Twitter), and the topic is “Best Practices.”

I could talk all day about the various best practices I require in my environments, but I thought a more novel topic might be to write about why people don’t follow them. I’ve only been in the business a little over two years (four in IT), and I’ve already heard enough to fill a small volume. But, we’re all busy people so I’ve decided just to post what I’d consider the top two most frequent whines (and they definitely make up the lion’s share).

Without further ado…

We Don’t Have The <Time, Money, Manpower>

I’m a firm believer in the triangle of project management; you can have something cheap and fast (but poorly written), you can have it good (as in high quality) and fast (at a high cost), or you can have something good and cheap (but it will take a long time). All too often the people holding the cash choose the first one: fast and cheap. I would bet this is especially true in startups or new business ventures, where being first to market at 80% quality is better than being fourth to market at 100%.

Honestly, this is one that we, as SQL professionals, usually can’t do much about. There have been many cases in my career where I reviewed some SQL code, immediately flagged it as bad for various reasons, only to be told “We don’t have time to fix it before it goes live,” or “It’s not in our scope to make the changes you’ve requested.” Earlier on I would kick and scream, telling my boss there was no way I’d let this code in to my systems. Almost inevitably I was overruled and the code was released, and my tantrums only served to increase the bad blood between my group and the development teams. Some times the code would blow up and we’d be forced to fix it ourselves (because we’re the only ones who knew how, more on that later), or release a hastily constructed patch that probably only made things worse half the time. It is incredibly frustrating to say the least. Eventually I’ve just learned that this is largely out of my control, and all I can do is explicitly document my findings and make it known that if the code is released, it is done in a limited support, if-it-breaks-you’re-SOL manner. We’ve also tried to engage our groups earlier in the process, before the project scope is set, so that we can ensure enough time / money / manpower is thrown at the problem to solve it correctly.

We Don’t Know How To Do That

I’ve often heard the excuse of “We don’t know how to follow your standard,” or “We don’t know how to follow that best practice.” Many times when I ask why not, the answer is “We’re not SQL developers.”

Yes, you read correctly. They’re not SQL developers, so they don’t know how to write SQL properly. I’m sure you can guess my next question: “So then why are you writing T-SQL code? You wouldn’t expect me to write C# multi-threaded applications, would you? So why should you write T-SQL when you don’t know the difference between an index scan and a seek?”

Inevitably this is met with some level of blank stare, followed by more excuses of the “We can’t afford a SQL developer” or “SQL developers are hard to come by” variety.

To be honest I don’t know why this is the case, but it’s been my experience (and from what I’ve read I’m not alone) that SQL Server knowledge is often a huge gap on most development teams. The assumption is that someone who is a good .NET programmer and happens to have written a few queries can suddenly become an enterprise data architect, designing a scalable, secure, and high-performing system to process hundreds of thousands of transactions an hour. One would think that the database layer would be held sacred, since it is responsible for ensuring the enterprise’s data is kept safe; instead it often seems to end up more like an experimental lab, with structures designed on Etch-A-Sketches and napkins, not ER modeling tools.

So what’s a DBA to do? In our case, we’re attacking this on three levels:

1. Bring in one or more true SQL Server developers, and have them handle the really complex stuff.
   
   Can’t afford to have a SQL guru on every group? At least bring in one or two and let them handle the real low level tasks up front, such as designing the database structure and complex retrieval patterns. Maybe the script-kiddie in the next cubicle can handle some really simple CRUD procedures, but leave the fancy stuff to the pros.
2. Have regular sessions where staff are invited to get free in-house training on SQL Server.
   
   We have bi-weekly Lunch ‘n Learns, where members of development teams are invited to come and participate in sessions that include watching webinars, conference lectures, presentations by in-house SQL gurus, or round-table discussions. The participation waxes and wanes, but overall the response has been positive and at the very least, we can begin to see who really wants to learn.
3. Grade developers on their code, and make the results public.
   
   We’ve begun collecting detailed grades on submitted T-SQL code, on dimensions such as security, efficiency, readability (because who wants to read a single line 5000 word query – yes, I’ve seen it), and documentation. These grades are collected in (of all things) a data warehouse, where they are reviewed and published to the development managers. This helps us in two ways: 1) to spot diamonds in the rough – those folks who, given some encouragement and guidance, could develop into good SQL developers; 2) finding repeat offenders, who, despite assistance, are not improving and are costing the company money and time in fixing their poor code. The system’s only been in place for a brief amount of time, but we’re excited about the transparency and intelligence this will offer.

There are many others…

Certainly there are many other reasons teams don’t follow standards. I’d love to hear what others think are the top reasons why standards don’t get followed in their company (or in the SQL world in general)!

It’s that time again, when SQL bloggers from all over join forces to blog for a common theme. That’s right: T-SQL Tuesday!

Background: T-SQL Tuesday is the brainchild of Adam Machanic (Blog|Twitter) and is described as “the SQL Server blogosphere’s first recurring, revolving blog party. The idea is simple: Each month a blog will host the party, and about a week before the second Tuesday of the month a theme will be posted. Any blogger that wishes to participate is invited to write a post on the chosen topic.”

This month’s theme: “Beach Time”, hosted by Jason Brimhall (blog | twitter). Basically, we’re being asked to write about “What do you do prior to ‘Beach Time’ to ensure that the beach time will not involve work.”

This is actually a good topic for me, as last month I took the longest consecutive vacation of my working life at two full weeks. It was challenging to get everything in order before leaving, and I did get a phone call once (ironically on the first day off), but all in all my team did great work and kept all my projects in good order while I was gone (with one minor exception, but that was at least partially my fault as you’ll see).

So how did I pull this extended absence off?

I documented the status of all my projects, including what was outstanding from others and what needed to be done while I was gone.

We have a wonderful SharePoint site where we keep all our projects listed and have detailed status notes on all of them. This means that if any one of us ever had to suddenly be out for an extended period of time, someone else could just glance over their list and pick up where they left off.

This reminds me strongly of one of my GTD habits: keep things in lists and not in your head. By forcing us (and yes, I do sometimes neglect it a little, hence my first (but only) phone call) to keep this updated, we don’t have to rely on our memory to keep track of what’s on our plates.

I made sure my runbooks were up to date.

Just before I left, I closed out a major phase of a project to build a massive data warehouse containing PerfMon data for all the SQL servers we support. It’s a complex system with multiple inputs and moving pieces. Because of this, I made sure the write up a thorough document detailing the ins and outs of the system, including architecture and troubleshooting steps. No way was I being called when I was away because this thing broke.

Now as it turns out, the system did fail while I was gone, but was unnoticed! And while my teammates sheepishly admitted that they missed the alerts and should have at least dug into them a little further, I also took responsibility for not ensuring they (the alerts) were a) urgent enough to be noticed, b) clear enough to tell the reader where to get more information.

Once I was gone, I made damn sure I was very hard to get in touch with.

While I am the first to admit I have a pretty bad addiction to my Crackberry, when I’m on paid vacation I strictly enforce the “no e-mail rule”. I go so far as to actually turn off the mail synchronization on my phone and make it clear in my out of office that I will not be checking my mail at any time during my absence. Only my teammates know my phone number and they are told they can call me if absolutely necessary, but that I may not immediately respond. While I do tend to take my work laptop, that’s only because it’s also my playground for testing SQL related things, and I do not connect to the office.

Being selfish? Maybe a little, but hey, it’s my vacation time and I’m damn well going to enjoy it and not waste it working. That being said, on this last vacation I did end up working for about 3 hours, but that was wholly my fault for not properly documenting something before I left.

I have a great, strong team around me.

While this may not have much directly to do with me, I consider it a crucial factor in vacation success. If your team can’t hold down the fort, even given good documentation, then you’re pretty much sunk. I’ve been in those situations before, and let’s be honest: it sucks. There’s few things in the world more maddening than getting called on vacation because “Mr. So and So is here and he says you told him X, but that doesn’t make sense?”, especially when your conversations with Mr. So and So are explicitly detailed in writing, and readily accessible to your teammates. That’s a real example by the way, from my time as a retail store manager. Let’s just say that individual got politely redirected to where I kept a log of all my conversations, along with a gentle reminder that I was not to be disturbed. Thankfully, this is not the case where I am now, and I am eternally grateful for the hard work and dedication of my fellow DBAs.