October 14, 2016 at 2:48 pm
I will try to explain as much as i can.
I have a dynamic SQL in the form of A SP.
I used Option recompile and option for local variables to work around the Parameter sniffing issues arising from having a dynamic sql. I had a very slow running SP and i removed the Option recompile feature, this resolved the the issue as the SP executed within the time frame needed. it seemed as if the workaround Option recompile and Option for local variables were conflicting with each other.
My questions are these?
1) Is there a permanent solution to Parameter Sniffing issues arising from dynamic queries
2) And what is the best workaround solution for parameter sniffing in the list below?
OPTION (RECOMPILE) / WITH RECOMPILE
Adding an "optimal" index on columns that don't have indexes
Make the index on a column in the tables search filtered or drop it entirely.
Use an index hint to force use of any other indexes.
The query hint OPTIMIZE FOR.
Change the application behavior for the specific SP.
Can someone point me to the best solution.
Thank you.
I learn from the footprints of giants......
October 14, 2016 at 5:00 pm
JALLY (10/14/2016)
I will try to explain as much as i can.I have a dynamic SQL in the form of A SP.
I used Option recompile and option for local variables to work around the Parameter sniffing issues arising from having a dynamic sql. I had a very slow running SP and i removed the Option recompile feature, this resolved the the issue as the SP executed within the time frame needed. it seemed as if the workaround Option recompile and Option for local variables were conflicting with each other.
My questions are these?
1) Is there a permanent solution to Parameter Sniffing issues arising from dynamic queries
2) And what is the best workaround solution for parameter sniffing in the list below?
OPTION (RECOMPILE) / WITH RECOMPILE
Adding an "optimal" index on columns that don't have indexes
Make the index on a column in the tables search filtered or drop it entirely.
Use an index hint to force use of any other indexes.
The query hint OPTIMIZE FOR.
Change the application behavior for the specific SP.
Can someone point me to the best solution.
Thank you.
A) "dynamic queries", as in dynamic SQL? That is one of my best tools against parameter sniffing. You give the optimizer the EXACT values to use!
B) Statement-level OPTION (RECOMPILE) is awesome, and I recommend it nearly daily to clients and forum posters. These days I will trade CPU-ticks for IO (or bad plans) in the VAST majority of scenarios where I can do that to advantage.
C) OPTIMIZE FOR is a complete waste in my opinion. If you have data value skew or other significant statistical variances you are GUARANTEEING that at least some of the time you will get horrible performance! What's the point in that?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 14, 2016 at 5:06 pm
JALLY (10/14/2016)
I have a dynamic SQL in the form of A SP.I used Option recompile and option for local variables to work around the Parameter sniffing issues arising from having a dynamic sql. I had a very slow running SP and i removed the Option recompile feature, this resolved the the issue as the SP executed within the time frame needed. it seemed as if the workaround Option recompile and Option for local variables were conflicting with each other.
Why are you mixing up "Parameter Sniffing" and "dynamic queries"? And what do you exactly mean by dynamic query?
"option for local variables"? Do you mean OPTIMIZE FOR? What are you trying to achieve by using OPTIMIZE FOR and OPTION (RECOMPILE) in one query?
JALLY (10/14/2016)
2) And what is the best workaround solution for parameter sniffing in the list below?
It's like trying to answer the question - what are the best: all season, winter or summer tires? It depends on the query, parameters, plans, if it's overestimating or underestimating, can you afford to recompile every time etc. There is no best solution for all scenarios.
October 17, 2016 at 9:17 am
Alexander Suprun (10/14/2016)
JALLY (10/14/2016)
I have a dynamic SQL in the form of A SP.I used Option recompile and option for local variables to work around the Parameter sniffing issues arising from having a dynamic sql. I had a very slow running SP and i removed the Option recompile feature, this resolved the the issue as the SP executed within the time frame needed. it seemed as if the workaround Option recompile and Option for local variables were conflicting with each other.
Why are you mixing up "Parameter Sniffing" and "dynamic queries"? And what do you exactly mean by dynamic query?
"option for local variables"? Do you mean OPTIMIZE FOR? What are you trying to achieve by using OPTIMIZE FOR and OPTION (RECOMPILE) in one query?
JALLY (10/14/2016)
2) And what is the best workaround solution for parameter sniffing in the list below?It's like trying to answer the question - what are the best: all season, winter or summer tires? It depends on the query, parameters, plans, if it's overestimating or underestimating, can you afford to recompile every time etc. There is no best solution for all scenarios.
Hello Alexander,
I guess my explanation was not succinct. The problem that i have occurs when a query takes longer than usual to execute due to an issue that results from Parameter Sniffing. I have Used option recompile and also Optimize for Local Variable at the same time in different steps in the script.
what would be the best way to handle any issue that results from Parameter sniffing or it can only be resolved on a case by case basis?
I learn from the footprints of giants......
October 18, 2016 at 1:22 am
I guess my explanation was not succinct. The problem that i have occurs when a query takes longer than usual to execute due to an issue that results from Parameter Sniffing. I have Used option recompile and also Optimize for Local Variable at the same time in different steps in the script.
what would be the best way to handle any issue that results from Parameter sniffing or it can only be resolved on a case by case basis?
How did you figure out that this issue is due to parameter sniffing, did you check the plan xml ?
If the parameter sniffing is the issue, then identify the script that is causing that, the table involved in that script might have skewness, you can try to add option recompile for that statement alone and test.
You have to check the pattern of data in your tables, if skewness is common then you adding recompile option is good.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply