February 20, 2020 at 8:34 pm
Hi All,
I know there are various causes for parameter sniffing issues, but one I want to focus on is: not having a covering index. Logically speaking then, having a covering index would be a way to address the issue. This is further evidenced in Tara Kizer's comment on:
There’s lots of ways to workaround or resolve parameter sniffing issues. You’re going to want to stay tuned for part 3, which comes out tomorrow. Using local variables is NOT the way to workaround parameter sniffing issues. Part 3 covers that topic. The best solution is to get the right covering index in place and/or rewrite the query. I like plan guides, hints, etc. I love OPTIMIZE FOR. Lots of way to work around it. Local variables should be avoided. If you are on a newer version, you’ve got the Query Store. The goal of this series is not to show the various workarounds, but rather to get people to troubleshoot it correctly and THEN work on workarounds/solutions.
My question is, why? Why does having specifically a covering index on a query inherently protect it from parameter sniffing issues? Wouldn't a query plan still be compiled and stored in the plan cache with whatever value(s) the query or stored procedure was first run with, regardless of whether there was a covering (or even non-covering) index (assuming there weren't any query hints like OPTION RECOMPILE)?
Thanks in advance,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 21, 2020 at 12:27 pm
Let's start with the obligatory, it depends.
I wouldn't, myself, describe a covering index as a solution to parameter sniffing. Instead, what a covering index can give you, depending on the query, the index, the selectivity of the columns, is a better score for the row counts because of the combined selectivity of multiple columns. However, those multiple columns have to be part of the key. They can't simply be there from the INCLUDE operator. Otherwise, nothing changes. You're looking at the same set of statistics, selectivity and histogram.
The core issue, most of the time, but not all the time, for bad parameter sniffing is the histogram. All you can do to affect the histogram is to ensure that you're picking the right leading edge column for your index. For example, let's say we have a compound index that consists of a VARCHAR(50) column and a CHAR(1) column. Either column can be first, and the index could be defined as covering for a query that only needed those two columns. Yet, if you pick the CHAR(1) column as the leading edge, regardless of the selectivity of the columns combined, the histogram is going to be junk. Therefore, it's likely that you'll get either bad index use, no index use, or bad parameter sniffing. Swap the columns, and you're more likely to get good index use, but, you can still get bad parameter sniffing because of the histogram. Having a compound key doesn't help the histogram, it only helps the selectivity, which is a secondary choice for the row estimates from the optimizer.
All this gets really complicated. I'm not sure entirely why someone would say a covering index is a solution for bad parameter sniffing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 21, 2020 at 3:23 pm
Hi Grant,
As always, thank you for the response. Just one thing, if you don't mind.
I understand how indexes work and the importance of the selectivity of the leading column of the key--but why specifically would the histogram be junk if the CHAR(1) field were the leading column (let's assume the CHAR(1) field itself was incredibly selective and the VARCHAR(50) was not selective at all)? Are you suggesting certain data types have a bigger influence on the histogram than the selectivity of the data that resides in those fields?
I'm sure there's something obvious I'm missing on this point, but I just thought I'd ask for some clarification.
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 21, 2020 at 3:34 pm
Well, the CHAR(1) can have how many different values if we're not talking the entire ASCII set, 36, alphabet & numbers. The histogram is 200 steps... well, in this case 36. Let's go easy and say there are 36 million rows. Each step simply represents one million rows. The optimizer will hate that and if you see that index used at all, it's always going to be scans.
If we expanded it to the entire ASCII set, it's only 127 distinct values. Probably still a bad histogram.
Now, could a VARCHAR(50) be even worse? Sure. It could have 3 values across our 36 million rows and make a much worse histogram than the CHAR(1). I was assuming a general data distribution, not so much an edge case. However, the edge cases are out there.
Like I said, this is all very complicated.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 21, 2020 at 4:04 pm
The thing is, with one exception, I don't see indexing as a way to fix bad parameter sniffing. Indexes cause bad parameter sniffing. The one index I treat as a "solution" to bad parameter sniffing is the filtered index. That's the one that can, not saying will, help you fix bad parameter sniffing because it completely changes the data distribution, histogram, and therefore sniffing behavior.
Other than that, I see the "fixes" for bad parameter sniffing to be (no order):
Does any of that help?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 21, 2020 at 7:39 pm
Honestly--yes. I think all of this. I knew already knew some of the potential fixes, but the information about CHAR(1), the histogram, etc. and how the optimizer will behave, while very complicated, is more knowledge than I had before--so thank you for explaining all of that.
Much appreciated.
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 21, 2020 at 7:45 pm
Not a problem. That's what we're here for. Share the knowledge.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 23, 2020 at 10:58 pm
Grant,
Ya know--I was giving one your responses a little more thought...you stated that optimize for a value is a potential fix for Parameter Sniffing. I agree but wanted to get your opinion on something.
If we had the following queries and an index on Reputation and DisplayName, we might get a table scan on the first query if SQL Server determines there's so many Reputation = 1, it's easier to just scan the table than to use the index, right? If so, the plan with the table scan would be stored in cache and used on the second one.
SELECT DisplayName
FROM Users
WHERE Reputation = 1
SELECT DisplayName
FROM Users
WHERE Reputation = 25414
whereas if they were reversed, we might have an index seek on both (assuming the optimizer used the index on the Reputation = 25414 query), right?
Obviously I'm not saying anything you don't already know (heck, you've written books on these types of topics)...but here's what I'm wondering...
Let's say we know a lot about the data and that most Reputation values in the table aren't as plentiful as 1 and would likely benefit/use the index. To prevent the table scan/ensure the index seek, we use
SELECT DisplayName
FROM Users
WHERE Reputation = @ReputationPoints
OPTION (OPTIMIZE FOR (@ReputationPoints = 25414));
My question is: wouldn't that be the same as using an index hint to force the index?
SELECT DisplayName
FROM Users WITH (INDEX(IX_Reputation_DisplayName))
WHERE Reputation = @ReputationPoints;
I don't see that on the list as a possible solution to parameter sniffing, so that's why I ask. Also, if it is the same as optimize for value, then I think I prefer this to that because then we're not hardcoding a specific value.
Thanks in advance,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
February 24, 2020 at 11:58 am
No, forcing the index just means the optimizer will try to use that index. It doesn't mean it will perform a seek operation. You could use the FORCESEEK hint to arrive at seeks, but you actually don't know which index that it will use for that, so you may have to combine it with the index hint. <shudder>
I'm not a fan of hints. They take all control away from the optimizer, and you're not guaranteed a particular behavior really. I will use them, but they're at the bottom of the set of choices most of the time. Bad parameter sniffing is the one exception where picking the right value can help fix stuff quickly & easily, so hint there is frequently good.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 24, 2020 at 10:45 pm
Got it. Thanks for the response!
Mike Scalise, PMP
https://www.michaelscalise.com
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply