September 6, 2016 at 10:11 pm
Anybody please suggest what is the disadvantages of using OPTION( OPTIMIZE FOR UNKNOWN ) at query level in Sproc? and How it affects the performance of SProc?
September 7, 2016 at 5:45 am
Why are considering this in the first place?
It is better to use query hints as minimally as possible. Since the execution plan changes based on data, query hints may prevent SQL to use the best possible plan. You might be trying to prevent parameter sniffing due to skewed data. But consider all scenarios and look at the data to estimate how it is going to change in future. You should use this as a temporary solution in my opinion.
September 7, 2016 at 6:15 am
If you've analysed and tested, and have confirmed through careful testing and consideration of alternatives, that the hint is the best way to solve the specific problem you have (most likely bad parameter sniffing), then go ahead and use it.
If you're trying stuff at random, don't.
If you've got access to Pluralsight, I have a course there on bad parameter sniffing and Optimise for Unknown is one of the solutions I discuss.
https://www.pluralsight.com/courses/identifying-fixing-performance-issues-caused-parameter-sniffing
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 7, 2016 at 8:04 am
GilaMonster (9/7/2016)
If you've analysed and tested, and have confirmed through careful testing and consideration of alternatives, that the hint is the best way to solve the specific problem you have (most likely bad parameter sniffing), then go ahead and use it.If you're trying stuff at random, don't.
If you've got access to Pluralsight, I have a course there on bad parameter sniffing and Optimise for Unknown is one of the solutions I discuss.
https://www.pluralsight.com/courses/identifying-fixing-performance-issues-caused-parameter-sniffing
Oh, thanks for posting that, I'll have a look.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply