T-SQL Tuesday #31 – Logging
I’m excited to finally be making my first contribution to T-SQL Tuesday. Thank you to Aaron Nelson ( blog | twitter ) for providing this month’s topic.
The Observer Effect
The Observer Effect is essentially the impact that an observation has on the target of the observation. Where does this come into play with logging?
I’ve been in many heated debates about logging. Although it can help diagnose a production problem without having to grant undesirable permissions or redeploy an application with additional debug code, it can have a major impact to production performance. It rarely gets the attention it needs during the design phase, since it’s not a ‘feature’. There are many articles and blog posts that I would recommend to application developers about log4net and the Enterprise Library‘s Logging Application Block, so I won’t rehash those here. I will instead focus on some of the pain that logging can cause with a couple of examples that I have seen in the wild.
SSIS Packages Scheduled via SQL Server Agent
Consider a scenario where a set of ETL servers are set up to process incoming files as quickly as possible. Setting up a job that runs dtexec every 10 seconds will accomplish that task, but at what cost? What is the impact to msdb..sysjobhistory? The history is created whether the SSIS packages found files to process, or not. Now, you can certainly manage this after the fact, but did the row for a job that had no work to do in sysjobhistory provide any benefit in the first place? If files were supposed to be appearing on a steady interval, it could be cause for a pager to go off. Or, it just could be business as usual. If the SSIS package is also logging to a table, sysssislog is also filling up quickly, if not designed and configured properly.
Logging as a Debugging or Testing Tool
The problem with logging with something like log4net or the Logging Application Block is that it makes it so easy to get logging up and running into a SQL Server table, that a donkey developer doesn’t even have to think about it. They don’t have to define the difference between Error, Warning, Information, and Debug. They will all just get randomly sprinkled throughout the code and written to the database until some point in the future when somebody needs to peek in there and can’t find a thing. I’ve had testers insist that we log everything that happens, so we know if the system is working. That might be acceptable in a small, low transaction system, but it just doesn’t fly once you start building larger applications.
The Message
Logging is a critical part of supporting any system. Just be sure to give it the attention it deserves up front, to make sure that it is done in a way that allows your system to scale as needed.