Proper Application Logging
It's surprising how many people think Microsoft SQL Server 'knows' what they did yesterday.
As professional SQL Server consultants we are routinely asked by clients to assist with performance issues - after they have occurred.
SQL Server does not keep a permanent history of what was running at a given time. What is does track is on a temporary basis only. And usually not in an easily digestible format that can be used to piece together exactly what was running at a given time. Sure, there is the query plan cache or default trace. Things which stores the query text and execution metrics of the workload, for a while at least.
Maybe what you are after is still available, maybe not. Because what it does track 'ages out' or is lost on a service restart. But even if it is available, and you get your hands on it, you still may not be able to determine:
when the query was submitted
who submitted the query
how the query was submitted
what data flow task in an SSIS package may or may not have submitted the query
what SQL Agent job submitted the query
what else was running at the same time as this query
etc.
Believe me, I wish it did. But unfortunately, that is up to the developers or operations resources to keep track of. And most of them don't know it. Or even how to document that information in a way that is easily digestible. We'll be writing another blog in the near future on 'quickly digestible documentation'.
Some highly motivated users could also try to examine the SQL Server transaction log. But most give up rather quickly. And those that succeed probably don't want that to be a primary troubleshooting tool.
As experienced SQL Server professionals people are sometimes surprised to hear us say 'I don't have a crystal ball. You need to tell me what else was running at that time'. There are a variety of 3rd party tools which will capture most of that data. We use many of them and they are quiet impressive. But tools cost money and still aren't enough for most end users. They need to be combined with skills in using the tools and expertise in SQL Server internals. Plus, you still need to know your own workload.
That's where solid application logging comes in. You should absolutely be logging the activity your SQL Agent jobs, SSIS packages, stored procedures and TSQL statements are performing. The activity logging should be capturing, at a mimimum:
start time
end time
duration (although that can be calculated, not always easily depending on what display format you want)
# records
results - did it succeed or fail, error message too if it failed
other fields to track what 'application', what package, what job, whatever ... you need to know!
server where code is executing, if a distributed solution
Sure, SSIS has logging with the SSISDB catalog. And SSRS has the trace log and execution history views. But how long will it take you to access and digest that information once there is a problem and your stress level is rising? Even better when people are blaming you for the 'job bombing'. It's so much better to implement your own logging. Logging that is accurate and easily digestible when needed to be used.
From our perspective, application logging is one of the most important troubleshooting tools you can have. It provides many, many benefits, such as:
a history of job/task/query execution times, which can be used for comparisons later
insight into how far along in its processing a task is
knowing what data was loaded, from what source, when, with what result
how frequently a process is run, its average run duration, etc.
not wasting your time slogging through objects/source code when there is a problem
We use application logging on every database solution we build and it is quite satisfying to simply query a table or two and have deep insight into a completed - or still executing - process. No having to parse complex, confusing and poorly formatted log files. No having to look at files on network shares. No having to wade through emails looking for the latest revision in specs. No wondering 'how far into the process is it?'. And actually being able to answer the big question ... 'how much longer will it take'? Just a simple query or two is all it takes, usually accessible through an attractive SSRS report!
If you need assistance in implementing appropriate application logging into your own solutions please give us a call today at 888.313.5610. We'll get you going in the right direction right away!
Thanks for reading!