Tracing call stacks in SQL Server – Introduction

At this fall’s SQLIntersection conference in Las Vegas I attended, Paul Randal’s (t|b)“Performance Troubleshooting Using Latches and Waits” precon. Where at he asked for some assistance compiling data for a project he’s working on. The project that would require installing the “Debugging Tools for Windows” and generating debug symbols for the SQL binaries. I have always intended to work with the debug symbols to find the call stack traces and experiment with what SQL Server does during certain events, like creating a database, inserting a row and such. These are topics that interest me as a computer scientist and a SQL Server professional and also can help our clients in understand conditions when trying to get a handle on obscure performance anomalies.

In this multi-part blog series I will document the process of

  • Installing the Debugging Tools for Windows (WinDbg) on Windows 8.1 and generating debug symbols for SQL Server binaries
  • Implementing the debug symbols on a SQL Instance
  • Demonstrate how to build a call stack trace
  • Document the stack traces generated during interesting scenarios such as creating a database, adding a table, inserting a row and more

What is a symbol file?

A symbol file is used by a program, usually a debugger, at runtime to translate the address offsets of an machine binary to human readable names from the programming constructs such as function calls.

What is a call stack?

As each function is called, it’s address offset (i.e. program counter) is pushed onto the stack in a stack frame. The currently executing function is on the top of the stack. Subsequent function calls are pushed onto the top of the stack and the calling function is pushed down in the stack. This is the call stack. Each entry will have an address offset of the function call in the binary executable. In the execution of a program a function will call another function and so on. This call stack can hint at what is happening inside the execution of the program. 

What do we care about the call stacks when gathering wait and latch data for SQL Server?

This technique allows us identity code execution paths, waits, and latches from inside the SQL Server’s executing process. From this we can have greater insight as to under which conditions these events occur.

What does a call stack in SQL Server look like?

sqldk.dll!SOS_Scheduler::UpdateWaitTimeStats+0x2bc
sqldk.dll!SOS_Task::PostWait+0x9e
sqlmin.dll!EventInternal::Wait+0x1fb
sqlmin.dll!LatchBase::Suspend+0x633
sqlmin.dll!LatchBase::AcquireInternal+0x415
sqlmin.dll!bufwait+0x49
sqlmin.dll!bufwrite+0x35e
sqlmin.dll!GlobalFileHeader::StoreDbInfo+0x393
sqlmin.dll!BootPagePtr::Release+0x15e
sqlmin.dll!DBMgr::SyncBootPageWithDbReg+0x573
sqllang.dll!CStmtCreateDB::CreateLocalDatabaseFragment+0x860
sqllang.dll!DBDDLAgent::CreateDatabase+0xf7
sqllang.dll!CStmtCreateDB::XretExecute+0xdc1
sqllang.dll!CMsqlExecContext::ExecuteStmts<1,1>+0x427
sqllang.dll!CMsqlExecContext::FExecute+0xa33
sqllang.dll!CSQLSource::Execute+0x86c
sqllang.dll!process_request+0xa57
sqllang.dll!process_commands+0x4a3
sqldk.dll!SOS_Task::Param::Execute+0x21e
sqldk.dll!SOS_Scheduler::RunTask+0xa8
sqldk.dll!SOS_Scheduler::ProcessTasks+0x279
sqldk.dll!SchedulerManager::WorkerEntryPoint+0x24c
sqldk.dll!SystemThread::RunWorker+0x8f

Next up in this series is “Installing the Debugging Tools for Windows (WinDbg) on Windows and generating debug symbols for SQL Server binaries“

Please feel free to contact me with any questions regarding performance or other SQL Server related issues at: aen@centinosystems.com

References used for this project: