What SQL Server Parameter Sniffing Looks Like In sp_WhoIsActive
Video Summary
In this video, I delve into the often-overlooked `sp_whoisactive` stored procedure to explore parameter sniffing issues in real-time on a SQL Server. Using SQL Query Stress, I run through a demo that highlights how parameter sniffing can manifest by comparing query performance and execution plans under different conditions. By leveraging `sp_whoisactive`, we focus on the average milliseconds column to identify potential parameter sensitivity problems, where queries might perform well most of the time but occasionally take much longer. This video is perfect for anyone looking to catch these elusive issues early before they become major bottlenecks in their SQL Server environment.
Full Transcript
Erik Darling here with the unstoppable Darling data. And we’re going to use an often overlooked parameter for SP who is active to look at what parameter sniffing can look like while it’s happening to your SQL Server. And we’re going to actually recycle a demo from last time, just like I promised. And I’m going to be using SQL query stress to do a thing. The thing that SQL query stress is going to run is, well, it’s going to look like the last demo that I did on what parameter sniffing looks like in query store. If you are watching this video, that would be the one that I’m referencing where we use query store to identify queries that might be parameter sensitive. Wew call so WA are using. Just back Andcr ow. move on. So what of Las огрwh here and code.
here of mystic call. we are going to use the small query plan for whenever the second part of the sys date time call modulus by five equals zero then we’re going to go and use the big one so what we’re going to see is most queries finishing pretty quickly than every five seconds a bunch of queries taking like four five plus seconds there might be some more if we can introduce decent temp db contention in there we’ll see what happens we’ll see we’ll see what we catch we don’t know yet i do all these demos without practicing so you can imagine how good i am when i actually do practice but anyway oh wait that’s the that’s the old demo we don’t need that anymore you can see now where i embarrassingly copied and pasted my code so if we run sp who is active right now nothing is running nothing is running there are no rabbits up my sleeve and now if i run sql query stress and we look at what happens here we’re going to see a column that a lot of folks often miss and that’s this column here with the avg at the end this is how long the query runs for on average so every once in a while when we see the average milliseconds still low but the actual seconds that something is taking be pretty high that’s a pretty good sign that we are hitting a parameter sensitivity problem right this has got seven seconds there on average it’s 100 163 milliseconds and so that’s kind of what it looks like when things start getting weird we can go look at the execution plan the execution plan is going to look like again classic parameter sniffing and what we see here is that uh this query was expected to let’s zoom in on this sql server was expecting 4756 rows which with the uh other with the other parameter is not is just kind of about right but when we use the further back parameter is just about wrong and so that’s why we have this high number of rows here now sometimes if we get lucky and we look in the uh plan we look at the uh plan details we’re using actual parameters and not local variables because local variables mess this up but if we look at the parameter values here we can see exactly what it was compiled with which is useful because how do we know how can we test parameter sensitivity unless we have a starting point and the good starting point here would be to you know test our query in a parameterized sort of way with um should be all done now test the query in a parameterized sort of way like this again this is the good parameterized parameterized dynamic sql now granted it being sensitive to parameter sniffing is not good but you know there are various ways of fixing that but what we would want to do is kind of going back to the the demo from the previous run previous video and we don’t have to go 50 times to do this uh we would want to test let’s just make sure everything is every all things are equal here dbcc free proc cache we’re going to get rid of everything and we’re going to run this with the parameter compile value that we found in the plan cache all right let’s let’s turn on query plans for this one it won’t won’t hurt us since we’re not running it 50 million times we run that and get the execution plan and again sql server’s guess here was pretty gosh darn good for a range predicate right we got back we we expected 4756 rows we got back oh just about 300 200 and some odd more maybe actually that is pretty close to 300 isn’t it i guess it is you know about 300 rows extra 300 more rows than estimated uh cardinality estimated not just not just a guesstimate there and then if we are intrepid query tuners we could go and look at what happens if we use that same execution plan but with a value that gets more data in this case just like last time we’re going to go back to 2011 12 30 from 2013 12 30 and if we run this we’re going to see a query plan that looks fairly close to what we saw when we ran who was active where we got let’s see uh 1808340 we got 1.8 million rows back rather than the 5 000 or so rows back that we got on the other query and of course ending up in a loop and all that stuff when you have way way more rows than would make sense to loop over can be rather caustic on the cpu so we have the serial execution plan not not for serial just uh you know serial because sql server came up with the execution plan based on a parameter that was expected to return a low number of rows and it did but now that plan doesn’t make a whole heck of a lot of sense when we have to return way more rows and that isn’t that something isn’t that ain’t that parameter sniffing so anyway uh that is what parameter sniffing will look like when you uh use sp who is active to look at what is currently running on your server again the two parameters that i typically use to figure this stuff out one is to get average time because i need to know if what i’m seeing is what normally happens when the query runs right so like you know you see get average time the average time is normally really low but now all of a sudden you’re seeing the average time like the average run time like the the actual run time way higher than the average time well that’s a sign that something has something has run amok with your query now does it have to be parameter sniffing not necessarily your server could be under an unduly high load the query could be blocked it could be uh some other sort of resource contention going on all right it could be all sorts of things that aren’t parameter sniffing but if you if you use this and you are and you think that it might be parameter sniffing you are curious about a potential parameter sniffing problem on your server well that’s when you know using uh sp underscore quickie store to look at query store data again that expert mode parameter we talked about the video before this one was a good way to figure out what was going on there especially sql server 2017 and up will collect high level query weight stats and it could be really useful to look at those to see if maybe there were some weights that you wouldn’t expect this query to hit like maybe a bunch of lock weights or something so that’s typically what i do when someone says hey eric it’s an emergency we need to pay you to fix our sql server because that’s what you do for a living and i say okay what’s wrong and they say i think it’s parameter sniffing this is one of the very first things i do so i come in make sure that the remote dac is enabled all right the dedicated administrator connection very important for these things run sp who is active get average time see if there’s any big discrepancies between the current run time and the average run time columns and then we go from there all right we have to choose our own adventure we have to figure look at weight stats you have to look at you know overall server load remember all good old sp pressure detector it’s a good one for that dig into all sorts of fun things anyway it’s hot in here and my hair and makeup crew wasn’t available today so i’m a little little shiny i hope that doesn’t offend you too much i hope that you uh you can deal looking at my shiny countenance and uh i don’t know i’m gonna go towel myself off and then i’m gonna record another video about about sp who is active and how you can tell the difference between queries that are doing work and queries that are stuck all right cool thank you for watching hope you enjoyed yourselves i hope you learned something i hope that you will uh find it in your lungs to like this video and subscribe to my channel so i can hit the 3000 subscriber mark sometime before the 50th president of the united states states is elected that’d be that’d be cool i’m still doing sql server by then who knows maybe this will still be useful to some maybe these fossils will still be useful anyway uh thank you for watching you’re cool and good looking young if i wasn’t married i would
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.