This evening I got into my vehicle to come home from about 45 minutes away to discover, much to my chagrin, that the truck wouldn’t start. I had jumper cables and friends nearby to get me going, but I wasn’t sure I would make it home – there was a cable with a bit of a loose connection and nothing I could really do to fix it at the time. I finally adjusted the cable sufficiently to keep the truck running and made it home, but it left me thinking – why don’t have I have a maintenance plan for my truck?

The idea behind maintenance is that if you sacrifice a little bit of time regularly, you’re less likely to have a major malfunction later down the road. This obviously applies to cars, but it also applies to SQL Server databases. Brad McGehee has written a great book on SQL Server maintenance plans – it sits on my bookshelf at work and taunts me about how I need to figure out how to apply it to developer work. Brad’s book is targeted at DBAs. I do have an idea or two about how an ETL developer can plan for regular maintenance:

• Plan performance reviews for your packages in Production. Look at what’s running the longest, and what individual steps within each package are running the longest. Watch especially for big changes in runtime and map out what changed to cause the new runtimes.
• Periodically review what individual pieces of code do. Rejustify it and analyze whether it could be rewritten to better fit business needs.
• Read lots of blog posts and consider how each new technique could enhance your ETL process.
• Review index usage stats in your databases to ensure that you have the correct indexes and that you don’t have any taking up space but not helping out.
• Take the time to review your architecture and make sure it’s still working for you instead of against you.

Those are just a few ideas, but I hope they help you to start thinking about what you can do to maintain what you build. Maintenance isn’t often fun or glamorous, but it can reduce the number of emergencies you have.