What Parameter Sniffing Looks Like in SQL Server’s Query Store
Video Summary
In this video, I delve into the world of parameter sniffing within QueryStore, specifically focusing on a classic scenario involving an index on the `users` table and the `sp_quickie_store` stored procedure. I highlight how parameter sniffing can lead to suboptimal query plans, especially when dealing with large data ranges, and explain why this issue is particularly challenging for queries without direct equality predicates. Using `sp_querystore`, I demonstrate how to identify these problematic queries by analyzing their execution metrics in QueryStore, such as duration and CPU usage. The video also touches on the importance of using expert mode to get a more detailed view of query performance, offering insights into when parameter sniffing might be affecting your SQL Server workloads.
Full Transcript
Guess who? You know, I don’t like that start, but I’m not going to redo it. I might have said that at the very beginning of the last video, I just don’t remember. So, Erik Darling here with Darling Data, and in this humdinger of a video, which I might have said about the last one too, hard to tell.
I can’t go watch it now because that would be embarrassing, and I hate the sound of my own voice, so we’re not going to do that. Anyway, in this video, we’re going to talk about what parameter sniffing looks like in QueryStore, specifically when you use myStore procedure, sp underscore quickie store, which is the only thing worth using to look at QueryStore. The QueryStore GUI is like read committed, the pessimistic garbage isolation level, it is an abomination.
God, man, cats, dogs, chunky peanut butter, it is the Chicago pizza of user interfaces. So, let’s get to it. Now, parameter sniffing, of course, can happen to any query that is parameterized.
If your queries aren’t parameterized, there’s a very, very low chance of there being a parameter sniffing problem. So, move that out of the way. I’d also like to mention that sometimes parameter sniffing is a good thing, specifically for most OLTP-ish workloads, because you want lots of plan reuse.
You don’t want SQL Server, you know, mucking around with things. You really don’t. So, what shall we do?
Well, we’ve got an index on the users table on the creation date column. And this is going to be sort of a classic parameter sniffing scenario where a plan with a simple index seek and a key lookup is good enough for small amounts of values, but not good enough for large amounts of values. It’s also worth noting that this query is ineligible for the SQL Server 2022 parameter sensitive plan optimization because we have, we don’t have an equality predicate here.
This is not an equality even though it is greater than or equal to. We are looking for a range of values and the parameter sensitive plan optimization simply does not comply with queries that do not have direct equality predicates in them. So, I’ve defeated you again Microsoft.
Alright, so let’s clear out query store because we don’t care what’s in there right now. Well, the most important thing happening is this demo. Nothing else is important.
So, what I’m going to do is I’m going to run this in a loop 50 times. And query plans are off, which is a good thing. And that finished rather quickly.
That finished in about a second. Now I’m going to change this. And I’m going to run this query, which goes back a little bit further in time. But I’m only going to run it once because I don’t feel like sitting here and staring at this thing running 50 times in a loop.
It takes a couple few seconds to run. If we ran a 50 time loop for 4 seconds, guess who would have a bad time? You and me!
50 times 4 is a number I can’t possibly conceive of. So, in order to look at this query specifically, we’re going to use a couple parameters for query store. One of them being query type.
And this is just simply an A to look for ad hoc queries. And we’re going to use expert mode so that we get a more full result set of columns. So, if we run this and we look at…
Well, we can get some of this junk out of the way. We don’t need all this clutter. Right? If we run this and we scroll over a little bit further, we’re going to see our query plan here, which, you know, look at that. Classic parameter sniffing, index seat, key lookup.
And if we scroll over a little bit further, we’re going to get to some duration and CPU metrics. Alright? So, average duration, 110 milliseconds.
Total duration, 5 seconds. Last duration, 4.2 seconds. Minimum duration of 17 milliseconds. And a maximum duration of 4.2 seconds.
This is going to also correlate with some CPU metrics and some read metrics. So, average CPU, 110. Total CPU, 5.
Last CPU, 4.2. Min CPU, milliseconds, 17. Max CPU, milliseconds, 4.267. And we’ll also see sort of a similar pattern with the reads here. So, the min reads is 142.
The max reads is 45,000. So, one good way, if you are concerned about parameter sniffing going on with your queries, one very good way to figure out which queries might be, might fall into those categories, is to run spquickiestore.
You don’t need the query type for that. It might be, I only used A here to filter down to ad hoc queries, since that’s dynamic SQL. It’s not attached to a store procedure. You might find lots of store procedures that are also parameter sensitive, so heads up there.
But, a good way to do that, mostly to use the expert mode equals one parameter, which will get you a bunch of extra columns. If you don’t use expert mode equals one, the only thing that I show in the columns is the average and total for like CPU duration logical reads.
Just because I don’t want a big cluttered result set, I don’t know, like, you know, if I don’t know what the, if I need to dig deeper, I can. And that’s why the expert mode switches there.
I don’t necessarily need every single column under the sun in my initial result set. So this is a little bit of a dig deeper scenario. This is what experts do when they look at query store.
But if you see a big variation between, you know, average, min, max, last, you know, I mean, last might even be okay, but mostly the mins and the maxes and the averages. If there’s a, you know, you’re on average, you’re doing okay, but sometimes you do not so okay.
Well, you might have a parameter sniffing problem. It might even be classic parameter sniffing, which is when you have an index seek in the key lookup. How you choose to resolve those problems.
Well, that’s between you and your maker. If you need help with that sort of thing, I am available for hire. My business model is not just making free ad-free YouTube videos. It’s mostly consultancy.
So you can hire me to do it. You can also buy my training if you want to learn how I do it, which is might be suitable for you if you’ve got that kind of time on your hands and you can sit through more of me babbling about SQL Server. Anyway, I think a video that I’m going to record next, because the demo is here and it’s nice and handy, is I’m going to show you what parameter sniffing looks like in SP who is active.
It should be a good time, I think, for everyone. All right. Cool.
Thank you for watching. I hope you learned something. I hope you enjoyed yourselves. I hope you find it somewhere deep within your soul in the crevices where there’s all sorts of, you know, Cheez-It crumbs and old coins, popcorn. To like the video and subscribe to my channel.
I would love to hit the 3000 subscriber mark sometime before I turn 50. So, yeah, that’d be cool. Anyway, thank you for watching.
Stay tuned for how to spot parameter sniffing in SP underscore who is active. Thank you for watching.
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.