Troubleshooting Parameter Sniffing In SQL Server With sp_WhoIsActive

THANKS ADAM

---

Video Summary

In this video, I delve into the world of troubleshooting parameter sniffing using SPWhoisActive, a powerful yet often underutilized tool that has been around for years. I walk through practical scenarios where you might encounter performance issues and demonstrate how SPWhoisActive can be your ally in diagnosing these problems quickly. By leveraging features like `get average time`, `get outer command`, and `get plans`, I show how to identify parameter sniffing issues, understand the impact of different parameters on query execution, and explore various tuning options to mitigate performance bottlenecks. Whether you’re dealing with a server that’s suddenly under heavy load or simply want to enhance your troubleshooting toolkit, this video provides actionable insights and practical examples to help you navigate these challenges effectively.

Full Transcript

Erik Darling here with Erik Darling Data. And I wanted to record a video, which is why I’m recording a video. I’m having a lot of fun today. I wanted to record a video about troubleshooting parameter sniffing using SPWhoisActive. It’s such a wonderful free tool that’s, you know, been around for so long. But there’s a lot of overlooked stuff in there, and there’s a lot of things, there’s a lot of things that you can do with it that can help you troubleshoot tough issues like parameter sniffing. Now, yes, you can log it to a table, and yes, there are plenty of blog posts out there that teach you how to do that. But, you know, sometimes it’s the middle of the day. You’re sitting by your computer anyway. And we realize that not everyone is soul bound to the F5 key. But say that, you know, you’re sitting there, you’re monitoring tool, and, you know, starts throwing alerts about CPU being really high, or users start, you know, rushing into your, well, I mean, rushing into your Zoom meeting, saying things are slow, or, you know, slack alerts, whatever it is, you get, you, the server is on fire. And you want to figure out what’s wrong. Now, SPWhoisActive can show you all sorts of great stuff. There’s nothing running on my home server right now. So this is going to be blank. But, you know, if you were to run it, you can find all sorts of things, like you might see the blocking session ID column populated. So you might just be dealing with a blocking thing, not a parameter sniffing thing.

necessarily like a query performance thing. You know, blocking will make queries feel slow. It’s not actually queries running slow, it’s queries being blocked from running. So just turn on RCSI, you’ll save yourself a lot of trouble and heartache down the line. But, you know, what if it is parameter sniffing? What if what if we need to figure out if it’s parameter sniffing in pretty quickly? Well, let’s come over here and run our store procedure. And while this is running, you know, you run SPWhoisActive and you might see some stuff happening. You know, you might see that something’s been running for a few seconds, you might see reads creeping up, but you don’t necessarily know that it’s slow. One way to tell if it is slower than usual is to use the get average time parameter. If we run this, we’ll see the query current duration. We’ll also see that the average duration is 111 milliseconds. That’s pretty bad. You know, we’re up to 27 seconds now. And usually this thing runs really quickly. So what on earth is going on? Well, if we look at the query text, we’ll have parent ID in there. And we’ll see that I mean, this is parameterized, but we need to get thing we need to go a step further. We need to get we need to figure out what the parameter value is, right? So let’s run this with get at get I know, I’m not supposed to type in demos, but I’ve been practicing. I got my Mavis beacon CD. Everything is wonderful. So let’s run this with get outer command equals one with nothing executing on the server. Well, it’s what’s the point, right? But if we run that with this thing now? Well, let’s see. Now we have another line of text here. Now notice that the average time did bump up a little because you know, obviously, that thing ran for 40 something seconds. So this did skew up a little bit. But that’s not really the point of what I want to show you for this run. In real life, I would expect you I would expect you to read my mind and read my screen and then just run the command as it is. But we get some extra help here. We can see that using the SQL command, we’re using the get outer command parameter, we now get the SQL command line. So this showed us the query text from the store procedure that was currently in the SQL command.

What we’re currently executing. This will show us how the store procedure was currently executing. Like what the parameter value passed in was, which is really, really helpful when you’re troubleshooting a parameter sniffing scenario. Because now we can see, golly and gosh, this thing sure did get a parameter passed in. But now we know what it ran with and we know the parameter that it was slow running with. So the next step we have to take and this is a little bit redundant now because newer versions of SQL Server give you, you know, a little bit more information about the queries that are actively running. But if we run our store procedure, and we run this, now we’ll have additional information back. So like the average time creeped up a little bit again, because you know, we ran the long running store procedure again. We’ll get back the SQL text, we’ll get back the SQL text, we’ll get back the oops, didn’t mean to click on that, we’ll get back how the store procedure was executed. But now using the get plans parameter, we’ll also get execution plans. And if we look at the execution plan, we can see some stuff in here that that does sort of make our query more sensitive to parameter sniffing.

We’ve got some nested loops joins. And you know, when you’re troubleshooting a problem like this, you know, I don’t want you to walk away thinking that like, lookups are always bad, or that like nested loops joins are always bad. But these are the kind of choices that the optimizer makes, where your queries can become more sensitive to parameter sniffing issues, when your data is really massively skewed. So, you know, when this plan was compiled, I don’t think that the optimizer was like, oh boy, we’re going to have this thing run for 45 seconds, just to mess with your day.

Well, we know that it was executing with a parent ID of zero getting passed in for the parameter. But let’s go look at the execution plan. And we’ll get that select operator. And now when we look at the we look at the execution line, we see the stuff that might be sensitive to parameter sniffing here, you know, might be some other stuff going on. But we get a little grab the select operator and get the properties there. We can do that by right clicking and hitting properties or by hitting F4.

But this properties thing will open up. And if we look at the parameter list, again, newer versions of SQL Server make this a little bit a lot easier, you know, like I’m on this is I’m downloading this on SQL Server 2019. But we can see the parameter compile value was 184618. And the parameter runtime value is zero. So how is this helpful? Well, now we know how to test the store procedure, right?

So we can we know that we have a runtime value of zero and a compile time value of 184618. So I’m going to copy this. All right. And let’s let’s close this to and let’s go back to our store procedure here. Paste that in, get rid of that. And let’s see what happens when we execute sniffles over here with a parameter compile value of 184618.

Well, this is running crazy fast. All right. This runs very, very quickly. All right. Nothing, nothing bad there. When we look at the execution plan, we can see just how quickly this thing runs. But, you know, this is where you have to start making some tuning decisions, because obviously if we run it for 184618, everything’s fine.

If we run it for zero, everything is not fine. This will run for about 45 seconds. But if we I’m going to mess this up because I always I always mess this up. But if we clear out the plan cache and we run this for parent ID zero first, this finishes very quickly.

Right. We have a different execution plan. And now if we run this for 184618, it’s still going to be relatively quick. Right. A lot faster than when when when when the plans got shared in the in the in the opposite order.

So we’d have to ask some questions here. Right. Did did we get a bad plan because 184618 was like the compile value? Is parent ID zero not something that people usually pass in?

You know, should we optimize for parent ID equals zero? You know, should we recompile constantly looks like there’s lots of questions that we could ask ourselves about how we want to fix this. Maybe if we’re feeling crazy, we could we could, you know, change that.

We could rewrite the query or, you know, change indexes to fix the key lookup. There’s a lot of options on the table. There’s a lot of different things you could think about. It’s all going to come down to, you know, what you’re most comfortable with or like how much control you have over the code or the indexes or the application itself.

So there’s all sorts of stuff we could do here. You know, we could even force plans with query store. We could create plan guides. Maybe they’d even work. I don’t know.

It’s crazy. Just don’t wait for the robots to fix it. That’s that’s my advice. Don’t wait for the robots. If you let the robots one up you once, they’ll never stop.

Never be able to live that down. Anyway, when you’re looking when you’re troubleshooting parameter sniffing and you’re sitting in front of the server, SB who is active is a great tool to do that.

And the parameters that I use with SB who is active to help me get the most out of trying to get to the root cause of a parameter sniffing issue are get average time equals one, which will show you which will go into the plan cache and look at how long this query executes for on average. And it will tell you and you can help figure out is this thing running much more slowly than usual or is this thing running slow as usual.

If you’re if it’s a store procedure, get outer command equals one is very helpful because it will show you like just the regular execution shows you the text of the query that’s executing. Using get outer command will also get you the stored procedure and the current execution values for it. And of course, get plans equals one will get you the execution plan on newer versions of SQL Server.

This does kind of make get outer command a little bit redundant because we get the parameter compile value and the runtime value. But on older versions of SQL Server, you could probably do with getting both. But this the getting the execution plan back is probably pretty helpful no matter what, because this is what we start figuring out what which part of our query plan is sensitive to parameters nothing and trying to figure out if and how we want to fix it.

I think it sounded good. We might just want to let it go because we hate our server. We want it to burn slowly.

We want to migrate to a real database. Like text files or Excel or access. I don’t know.

You people are crazy. You know, maybe you just want to write your own database. Just import the Python database library and you’re set. Just forget about SQL Server.

Anyway, thank you for watching. I hope you learned something and I’ll see you in another video sometime. Bye. Bye.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.