Generating SQL Server Call Stack

Few days back, when having a discussion with one of my friend he shared me a issue with a client which was unsolvable until they dig more into the Call Stacks generated for the specific query which was causing the issue.

As you might have already figured from my series ‘What & How’, I love to go geeky into the things and I realized this is something I have been missing out. I never checked the Call Stacks.

First thing first, you can see the call stack and might be able to make some sense, once having the symbols, but still you will never able to know everything, that is because of two reasons 1. Microsoft doesn’t provide the full symbol library to the public and 2. There is no documentation to know what those symbols actually mean. But still, like me, if you want to see the stack for anything happening follow the below steps.

1. You need to download the SQLCallStackResolver created by Arvind Shyamsundar.

2. Download the symbol files, to download them cilck here. Make sure you are downloading the pdb symbol files for your version of SQL Server only.

3. Some knowledge of Extended Event.

1. Create a XEvent, I will be capturing the call stack for a checkpoint.
2. I selected the checkpoint begin and end events.

XEvent event selection

3. Once selected click on configure and select callstack from the global fields.

Choosing callstack Global fields

4. Save it and start your XEvent.

5. Watch the live data, and either manually trigger a Checkpoint, or wait for sometime for the automatic checkpoint.

6. When you click on the Event you will be able to see its stack as below.

Event captured

Stack looks like this by default which doesn’t make any sense, to decrypt or see what it means we are going to use SQLCallStackResolver.

Once you open the exe downloaded from the link provided above, we need to first provide some parameters.

7. Provide the directory where you have placed the downloaded pdb files.

Adding path to pdb files

8. We also need to run the below command from command prompt to add the required dll (this comes with the setup) into the registry.

regsvr32 "C:\<location>\SQLCallStackResolver.1.5\msdia140.dll"

9. Run below command in SQL Server and copy the content.

SELECT name, base_address 
FROM sys.dm_os_loaded_modules 
WHERE name not like '%.rll'

10. Paste the above content it into the SQLCallStackResolver’s “Enter base Addresses”

Base Addresses into SALCallStackResolver

11. Copy the stack from the XEvent captured above in step 6 and paste it into the SQLCallStackResolver and click “Resolve callstacks!”.

Translated Call Stack with appropriate symbols.

And now we have the whole stack in a “understandable” format. How much you are able to figure out from it is a completely different thing.

By using this we can understand the call stack generated from the Memory dumps too.

I hope it helps you one day. 🙂

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s