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.
3. Once selected click on configure and select callstack from the 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.
0x00007FFCBE17A259 0x00007FFCBF3ED231 0x00007FFCBE086636 0x00007FFCBE086501 0x00007FFCBE086427 0x00007FFCBE08607E 0x00007FFCBE085F04 0x00007FFCBE085E3E 0x00007FFCBF3EDF33 0x00007FFD3F01BC55 0x00007FFD3E424550 0x00007FFD3E423FB3 0x00007FFD3E4235F4 0x00007FFD3E42CCB5 0x00007FFD3E42B832 0x00007FFD3E42B623 0x00007FFD460B4D6D 0x00007FFD460B52D8 0x00007FFD460B50CD 0x00007FFD460C9628 0x00007FFD460C9720 0x00007FFD460C8E27 0x00007FFD460C93E9 0x00007FFD664B7BD4
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.
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.
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”
11. Copy the stack from the XEvent captured above in step 6 and paste it into the SQLCallStackResolver and click “Resolve callstacks!”.
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. 🙂