Monthly Archives: December 2014

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<SuspendQueueSLock>::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</value>

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:

Book Review – Query Tuning & Optimization

Via Twitter in October Benjamin Nevarez @BenjaminNevarez solicited help for book reviewers for his new book Microsoft SQL Server 2014 Query Tuning & Optimization I jumped at this opportunity as Benjamin is a great author with significant SQL Server experience and this new book further solidifies his place in the community.

Query processing and its inner workings are extremely dense technical concepts that Benjamin is able to present to the reader in a very approachable readable manner. The book elegantly walks you through the major components of the query processor and dives deep where needed.

The code examples in the book are concise and accurately reenforce the key points the author is trying to convey in the text. 

The book excels at connecting very complicated query processing topics together for the reader in a simple and approachable manner.

It covers the many new features of SQL 2014, including In-Memory OLTP, incremental statistics, the new cardinality estimator and the new features added to columstore indexes.

There are few shortcomings with this book, I would have preferred citations throughout the book. Key concepts are introduced without citation and I would like to jump into some topics, specifically into the materials the author used when writing. The book does have an exceptional reference section, but there is no connection back to the text.

This book is now an a permanent fixture in my library of SQL Server references, I will likely need to replace it when I wear it out.

If you need help with query tuning or other performance issues with your SQL Server please contact me at: aen@centinosystems.com

Query Tuning & Optimization

Here are some of the (unedited) notes that I took while reading the book, covers the first six chapters:

Chapter 1

Solid intro. Good examples specifically on the warnings sections page 24 with references to upcoming chapters on how to deal with them. Gets off the ground with the appropriate groundwork/fundamentals for QP architecture and reading query plans and to move onto the subsequent chapters. 

Chapter 2
The chapter begins with the building blocks of query troubleshooting how to find resource utilization exec_requests and exec_session and also plan hash and text hash for when you need to hunt down your code or plan. Page 51 CPU queries are great splitting on plan hash so each batch is in results. The chapter then moves from the building blocks of hashes and text to tell the reader which tools are available to find troublesome queries on your systems. 

Chapter 3
Walks us through the query optimization process with great detail and contrete examples. Toes the line between academic and practical. Disceting a QP at this level will give you great insight into the inner workings of sql server and allow you to create better more well performing queries.  Having references to academic and professional papers is a great resource.  But I would have loved to have had reference numbers so I could go off on a deep dive when needed. This chapter clears the air and busts some myths that are found on the web such as join order at the query level matters. Also gives you the ability to dive deep on your own questions so you can runoff and experiment. 

Chapter 4
The transition into this chapter connects the previous chapters.  There is a solid example of this on page 130-131. Ben quickly connects the ideas of operations to indexes and their potential efficacy/efficiency. When discussing tipping point between bookmark lookup and scan, the reader is left wondering why.  Lack of explanation. Everything else so far is covered in such great detail but this wasn’t. In the parallel limitations section page 156, lacks any insight as to why these features will not produce a plan. 

Chapter 6
Avoid the use of local variables in a where clause. Uses a 30 percent guess. Use parameters or literals. The most concise explanation of the histogram yet. Wanted deeper discussion on stats skew. 

First Blog Post

Well, this is my first blog post…I’ve said I was going to do this for a long time and it’s finally here. 

Via Twitter a in October Benjamin Nevarez @BenjaminNevarez solicited help for book reviewers for his new book “Microsoft SQL Server 2014 Query Tuning & Optimization” I jumped at this opportunity two reasons, first Benjamin is an exceptional author and two I want to start contributing to the community. I feel that reviewing books can help others decide on how to spend their valuable time and hard earned money. Hopefully this effort helps someone in someway. 

I’ve enjoyed doing this so much that the folks at RedGate Software @RedGate asked for reviewers of Kalen Delaney’s new book “SQL Server Internals: In-Memory OLTP” I jumped on that too! Hopefully I will get this done quicker than the first :)