In one of my earliest interactions with SQL Server I was working at a company dealing with financial application. I was part of a team with C/C++ background. So before any solid understanding of SQL, our database code was mostly looking like C code, full of operations, procedure invocations, functions. We observed with panic that we could not debug our code, not easily. So came the need of having our faithful stack traces.

In time, our knowledge of sql improved, so we started to inline the code, cleaning it up and soon needing less and less the stack traces.

Until … the next workplace, and the next .. and still the next after 20 years. As long as SQL is written by non sql developers, the stack traces will be there to make an impact.

Stack traces

Seen in simple words, stack traces are reports or logs of call stacks. Call stacks in turn are maps of hierarchical called subroutines. So at any given point it time a call stack would show the present running subroutine and all the caller (parent) subroutines.

For us in the present context we need it for debugging and visibility purposes.

Implementation

As it will be with most of these articles, I will not enter in detail with implementation. Just outline a few ways to get it done (mostly coming out of personal experience).

The implementation of the call stack has a few requirements:

• fast – we should not create performance issues by keeping it
• non transactional – in case of errors, we might not want to rollback and lose it
• selective – log data based on trace level / situation

There are a number of implementations available, some with more and some with less work to do, however my favorite is built using .NET CLR.

The CLR would need to implement the following methods:

• AddToStack(SPID,MethodName,Parameters)
  • If there is not session for this SPID then create one
  • Adds to the stack the new call with it’s parameters
  • The parameters are easily captured at the start of the call
• RemoveFromStack(SPID,RemoveLog)
  • Removes the call from the stack, but for tracing purposes the log can stay
  • When the removed method is the initial one, then destroy the session
• GetCallStack(SPID)
• GetCallLog(SPID)

The objects (SPs, triggers, etc) would need to implement the logging as follows:

• Get the parameters
• Add the method and the parameters to the stack
• Do SP stuff
• Remove the method from the stack

I am not writing how error handling needs to be tackled as everyone does it in his own way. The call stack can be logged as soon as the error is encountered or on the root SP. Same the call log can be saved based on log level. Also what I usually like to do is write the content of the logs asynchronous. This allows me to choose what to write, when and where in such way so I do not hurt the server.

Benefits

What I like the most about this implementation is that it will not only give me logged (postmortem) information. It allows me to query at run time and see the stack of a process. There are a number of very good scripts running on the internet to get what runs in particular processes. To see the query, the plan, resource allocation and consumption, etc. But with this we can further enhance these scripts so that we can snapshot also the call stack, the parameters of the call, the times in which each step run, and so on.

Conclusion

The need for good logging will always be present. And while in some cases SQL Server comes with many features out of the box, they are not always easy to use and not always helpful. Having stack traces is always a plus, mostly in time critical applications, where stopping the application and debugging is not really an option.