T-SQL Tuesday #83 – Dealing With the Same Problems

tsql2sday-300x300

T-SQL Tuesday #83

  • In the <N> years I have been a database professional, we’re still dealing with <some problem>
  • In the <N> years I have been using SQL Server, we’re still dealing with <some problem>

This month’s T-SQL Tuesday topic is hosted by my friend and former colleague Andy Mallon (blog|twitter). I’ve completed one of his fill-in-the-blank prompts to read like so: In the four years I have been a database professional, we’re still dealing with lack of documentation.

When I use the term documentation I’m being intentionally vague because the problem is systemic and doesn’t only apply to database-land. We see the problem in the business requirements (if we’re lucky enough to have any written specs at all). We see the problem in the architecture. We see the problem in the code.

Why Documentation is Important

  • You, being the only person who wrote, reviewed, and deployed your code, may tomorrow be hit by a bus, leaving the rest of your team stricken with grief and utterly confused as to what your code actually does (this bus-factor of one should be avoided at all costs).
  • You wrote the code so long ago that even you don’t remember what it does anymore, and have to spend extra hours working backwards to reverse engineer it.
  • Your team may be expanding and growing, and as new people join they will need to be brought up to speed on your processes and procedures. Having sufficient documentation in place can expedite this process.
  • When something inevitably blows up in production, you can sift through a well-documented change log / code repository to decipher whether a recent change caused the issue.
  • When the business knocks down your door to ask why it’s taking so long to implement all the shiny new features, you can refer them to the requirements documents / backlog to help prioritize tasks and maintain order amidst the chaos.

A Little Documentation Goes a Long Way

Business Requirements

Get the business to commit to something in writing. Help guide and translate. Use a system like TFS or JIRA to create and manage a backlog, prioritizing requests as they come in or on a periodic basis. All of these suggestions help to set expectations, avoid scope creep, and keep everyone on track.

System Architecture

An up-to-date inventory of SQL Servers is a must for any DBA (bonus points for automating the inventory process). Additionally, it’s important to have high-level visual diagrams (a la Visio or PowerPoint) of all the major systems and their integration points into the application(s). This is especially important for SSIS packages, which can be a bear to troubleshoot when starting from scratch.

Code

Every stored procedure should have a comment header at the top. This is programming 101. Essential elements of a header include a brief description of what the procedure does, what the parameters are used for, the author’s name and date of creation. Additionally, it’s important to sprinkle comments (one liners or blocks) throughout, highlighting the reasons for using eccentric techniques or explaining a particularly complex bit of code. Changes should also be documented either in the header or in your source control system.

Start Documenting Now

I know what you’re thinking, and I hear (and may have even used myself) the excuse all the time: I don’t have time to document my code. The servers are on fire, management was promised product xyz would ship six months ago, and I’m too busy defragging all the things to stop and document every line of code in the system! It’s OK. Even just a few lines of documentation can go a long way in helping to avoid confusion for others, and yourself, in the future. It’s also a huge return on investment when you think of the hours you’ll inevitably save yourself down the road.

Michael Kane

Hello!

One thought on “T-SQL Tuesday #83 – Dealing With the Same Problems

Leave a Reply

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