Application Logging with XML
I ran a series of editorials a while back on application logging. We considered what kinds of data and events needed to be logged. Some shared techniques used to maintain a history of changes, or log events. Today Scott is catching up with his old Email and shares with us some experience he has had using XML for logging.
Scott Writes:
I’m catching up on old emails and way too late to comment on your blog entries about logging, but for what it’s worth I thought I would just mention that I’ve been pretty happy with some application logging architecture I’ve put into place at two different jobs.
In one case, a data-heavy dashboard application for ticket reservation pricing analysis, I implemented writing a log entry for each of several query stored procedure calls, capturing the name and run time of the stored procedure and using SQL Server’s XML datatype to capture the stored procedures’ parameter name/value pairs. We were then able to not only see what queries, modes of queries, data time periods, etc were popular, but which ones were resource intensive. Sort of an application-specific, lightweight, focused mini-SQL Trace.
I’m currently using an XML column again for a generalized application log again for a real time application (tracking train movement). We are trying to track down patterns in certain real time events and failures of certain threshold-based logic, and having all the input facts in an XML column for an event is helping us understand where we could improve the logic and threshold settings to avoid many of the failure cases.
Using an XML column involves a bit of formatting logic in the code, and a bit of XML querying logic to use the logged data, but with SQL Server’s recent XML data type support and "FOR XML" query output mode, it’s not too bad.
We haven’t had performance issues querying on the XML column because we write rows to the log sparingly, use it for specific purposes rather than to log the whole world, and we usually filter on regular indexed columns of the log table, event type and date range, before including filtering logic that involves asking SQL to navigate into the XML.
Thanks, Scott, for sharing your experience. Get into the conversation with Scott by leaving a comment below, or simply drop an Email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
Understanding SQL Server Database Restore Process
In this article, you learn about database restore and recovery process. You also learn available restore options. You will also see how to verify a backup before restore. I’ll also compared and contrast complete restore, piecemeal restore, file restore, page restore, and recovery only.
Featured White Paper(s)
Key Considerations in Evaluating Data Warehouse Appliances
read more)