Possible parameter sniffing?

Question

Possible parameter sniffing?

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 1

Gianluca Sartori (5/11/2010)
It makes sense. Everything I can suggest is trying to convert the code into dynamic SQL. It's fast and it doesn't cache bad plans.
You could use the ability of sp_executesql to accept parameters not in use in the sql string.
In the article by Erland Sommarkog there's a good description of how to accomplish this.

Annoyingly, the reason I couldn't give you the code is the same reason I can't use sp_executesql and I can't even access Erland's website. Unfortunately I work in a company that is extremely locked down. sp_executesql is seen as a security risk. I'm not sure what Erland did to upset them but it's because he's classified as a private homepage, and is therefore a no go 🙁

I think I may have to try biting the bullet and using the recompile hint. I wasn't keen on it for the same reason I wasn't keen on dumping the parameters into private variables as suggested on a few sites that have info on problems encountered using parameter sniffing. I just wish there was some more elegant way to do it, rather than side stepping the problem all together, i wanted to get parameter sniffing to work for me rather than against me. We did try a query hint a while back, using keepfixedplan or something along those lines, but it didn't stop the problem.

I also looked at the optimise for hint, but as I understand it, it works by suggesting to the compiler what a representative value is, which is fine if you were looking at department codes or something, but I am filtering on unique values. Every search will be for a different value. Am I understanding the hint correctly or have I missed the point of it?

Thanks

Paul

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

paul.goldstraw (5/11/2010)
Gianluca Sartori (5/11/2010)
It makes sense. Everything I can suggest is trying to convert the code into dynamic SQL. It's fast and it doesn't cache bad plans.
You could use the ability of sp_executesql to accept parameters not in use in the sql string.
In the article by Erland Sommarkog there's a good description of how to accomplish this.
Annoyingly, the reason I couldn't give you the code is the same reason I can't use sp_executesql and I can't even access Erland's website. Unfortunately I work in a company that is extremely locked down. sp_executesql is seen as a security risk. I'm not sure what Erland did to upset them but it's because he's classified as a private homepage, and is therefore a no go 🙁
I think I may have to try biting the bullet and using the recompile hint. I wasn't keen on it for the same reason I wasn't keen on dumping the parameters into private variables as suggested on a few sites that have info on problems encountered using parameter sniffing. I just wish there was some more elegant way to do it, rather than side stepping the problem all together, i wanted to get parameter sniffing to work for me rather than against me. We did try a query hint a while back, using keepfixedplan or something along those lines, but it didn't stop the problem.
I also looked at the optimise for hint, but as I understand it, it works by suggesting to the compiler what a representative value is, which is fine if you were looking at department codes or something, but I am filtering on unique values. Every search will be for a different value. Am I understanding the hint correctly or have I missed the point of it?
Thanks
Paul

No, you have the OPTIMIZE FOR hint right. But since, as you said, you can get a good enough plan for one set of values but not another, you could, and I'm not recommending this as a first choice, just one option, provide a value that works well.

I hate using hints. Any hints. I always feel that using a hint means I've failed in my tuning efforts. But sometimes, rarely, usually because of business requirements that prevent me from doing the right thing, you need to. I think you're at one of those junctures until you can rearchitect these queries.

"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

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 3

Thanks Grant. Do you know what makes the compiler decide one value is good and one isn't? Because these values are all almost the same. For instance, a value that might break it could be '00235345' and one that doesn't is '00645343'. I suppose the dilemma I face, knowing that any value could break it, is what is a good value?

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Grant Fritchey (5/11/2010)
I hate using hints. Any hints. I always feel that using a hint means I've failed in my tuning efforts.

Me too. That's why I hate tuning queries in Oracle: using hints seems to be a valid, recommended and documented way to go.

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 5

paul.goldstraw (5/11/2010)
Thanks Grant. Do you know what makes the compiler decide one value is good and one isn't? Because these values are all almost the same. For instance, a value that might break it could be '00235345' and one that doesn't is '00645343'. I suppose the dilemma I face, knowing that any value could break it, is what is a good value?

It's always down to the statistics. Not only how selective is any given value, but where does that value fall within the histogram? That's the primary deteriminant for any execution plan.

"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

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 6

Grant Fritchey (5/11/2010)
It's always down to the statistics. Not only how selective is any given value, but where does that value fall within the histogram? That's the primary deteriminant for any execution plan.

Does anyone know of any good resources on histograms and statistics (that hopefully I have access to!)?

Thanks

Paul

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 7

paul.goldstraw (5/11/2010)
Grant Fritchey (5/11/2010)
It's always down to the statistics. Not only how selective is any given value, but where does that value fall within the histogram? That's the primary deteriminant for any execution plan.
Does anyone know of any good resources on histograms and statistics (that hopefully I have access to!)?
Thanks
Paul

I know this excellent book that has information on that... :hehe:

Or you can hit Books Online. Kalen's "SQL Server 2008 Internals" has some excellent stuff on it too.

"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

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 8

it wouldn't be this by any chance would it? 😉 I have your ebook on SQL Server Execution Plans, I read it a while back when i started looking at this issue months ago.

I will pick this up again tomorrow and see what I can dig out. The problem with these intermittent issues is when you apply a fix, you then might have to wait a week just to find out if it worked or not. I will probably try using the WITH RECOMPILE hint and see how the performance is on that, but I might be back 😀

Thanks to all for their help today. I am glad my grasp of the problem wasn't too wide of the mark

Paul

ajrendall SSC-Addicted Points: 450 More actions · Answer 9

Just an aside (apologies for the off topic bits).

I remember the problem with this procedure Paul and also remember the lock-down policy that prevented us from getting to useful resources when I was working there.

So I've sent you an encrypted ZIP of the article above that someone suggested. It might or might not get through the filters, but keep an eye out for it. You should be able to remember the password. 😀

I've read it all myself and it does apply directly to the problem so hopefully you will find something there that you can use.

Andrew

paul.goldstraw SSCrazy Points: 2626 More actions · Answer 10

I thought i'd just write a follow up for those interested to say we've opted for using OPTION(RECOMPILE) at the end of the query. It doesn't seem to have affected execution times very much. We'll just have to see how it plays out over the coming weeks. I will post again with my findings after that.

Thanks again

Paul