Alternatives To Debugging SQL Server

Buggery

If you seriously want to debug, you’ve gotta use something like WinDbg. It’s tough, but powerful.

It’s also… Where do you even start? The most common thing to do is set a Break Point.

Figuring out where you wanna set it can be challenging.

  • Sometimes you can capture a thread and step through the code.
  • Sometimes you can find the call stack you want by searching through symbols

The problem with these is that stepping through code that runs in milliseconds has taken me over an hour at times, and call stacks often have cryptic names.

This is where tools like PerfView can help.

Not A Developer

I’m not a developer, or particularly smart. I wish particularly smart developers would blog about this in depth.

In the meantime, you’ll have to get the intro material from me. I recently used it to help answer a tough question.

There are times when traditional troubleshooting methods won’t help you.

Instructional

PerfView is just an executable. I like to stick it in its own folder; it keeps the files that get created together.

When you open it up, you’re gonna wanna hit the Collect menu.

Greetings

Hit collect (or Alt+C, now that you peeked, cheater).

Jane’s Collection

I usually skip zipping, unless I know I’m going to send these to someone else. You can always zip them later.

I like to grab thread time. I dunno why. I’ve always done it, and I’m superstitious.

I also listen to Planet Earth every time I’m on a plane taking off because it assures a safe trip. Right?

Right.

Choices, Choices

Next, when you’re ready to get stuff, hit Start Collection. This button will change to Stop Collection after you click it.

You’ll wanna stop the trace when the thing you wanna profile is done running, or when you think you’ve got sufficient sample.

Starter

Now, I have to warn you here. You don’t wanna take a long collection. These files get big.

You also have to factor in that profiling code can slow it down.

CPU Stacks

CPU stacks are where I go next. When you double click on that, you’ll get a pop up to pick which application you wanna dig into.

PerfView collects system wide. So really, please, please, please don’t run it for a very long time.

sqlservr

Double click into sqlservr, and you’ll get a list of stuff.

Greatest

It won’t look like this for you at first. There’ll be a lot of jibberish and question marks. You need to get symbols for them to make sense.

Symbology

Hit ctrl+a to highlight the whole list of lines, then either hit alt+s or right click and select Lookup Symbols.

If it’s your first time doing this, it might take a while to download them, but you really need to do this part to make any sense of what’s going on.

Do Not Group Or Fold

The next thing you wanna do is clear out GroupPats — just highlight, hit delete, and then enter.

Next, change Fold% to 0 and hit enter.

This’ll make sure you get the most detail from your sample.

Fortunate

You can click around here to get other views of the data.

Mean to it.

How Does This Relate To Debugging?

Well, see those list of call stacks up there? That’s what you use to set Break Points in a debugger.

You might see something interesting there that either doesn’t have the full detail you’re after, or you want to follow more closely.

Either way, this is a good example of how to take a closer look at what SQL Server is up to under the hood without getting lost in a debugger.

Thanks for reading!

Leave a Reply

Your email address will not be published. Required fields are marked *