Troubleshooting Parameter Sniffing In SQL Server With sp_WhoIsActive


Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

4 thoughts on “Troubleshooting Parameter Sniffing In SQL Server With sp_WhoIsActive

  1. thx for posting that video. for a long time I didn’t use sp_whoisactive because i wanted to try and write my own. Well, I was an idiot. Such a great tool.

    1. Yeah, a lot of people have that inclination, but they don’t realize that Adam spent north of 100 hours working on it.

      I’m all in favor of people doing that stuff to learn how DMVs work and relate, because at some point you’re going to need something outside of what a pre-packaged proc does, or you’ll wanna contribute to an open source project that one is a part of.


  2. Thanks Adam! I have fixed a lot of procedures where I suspected the issue was parameter sniffing, but I never knew how to verify that that was the issue. In fact, just last night, I killed a running proc @ 25+ minutes, applied my fix and it ran in < 5 seconds. Now I can go back and see if that was the actual issue. I will often add 'option (optimize for unknown)' to the end of the select, if I can't find any other problematic joins or missing indexes and it will usually fix the problem. Do you have other potential fixes you would use?

Comments are closed.