March 12, 2013 at 6:54 am
OPTIMIZE FOR UNKNOWN
Can anyone explain how this works with multiple parameters.
If it can only keep one execution plan, how does the below then work?
OPTION (OPTIMIZE FOR(
@L1 UNKNOWN,
@L2 UNKNOWN,
@L3 UNKNOWN,
@S4 UNKNOWN,
@D5 UNKNOWN,
@L7 UNKNOWN,
))
March 12, 2013 at 6:56 am
In short it just doesn't use parameter sniffing to estimate the rows that will be affected by the specific parameter values.
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
March 12, 2013 at 7:43 am
In my db there are many sprocs which can be filtered by the parameter. Which is not good new for me.
There will be 1 parameter for each column. so the one sproc can filter on any combination of columns.
Again not good for me.
They typically will use Option Recompile,could Optimize for unknown work better than recompile in this situation.
March 12, 2013 at 8:24 am
Sounds like you're trying to do this:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
If you are, optimise for unknown is not going to help much. All it does is have the optimiser make row estimations based just on the average distribution in a column, not what the passed parameter is estimated to return.
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
March 12, 2013 at 10:11 am
OVER AND OUT. THAT EXPLAINS IT FULLY.
THANKS
March 12, 2013 at 10:19 am
Great article with a solution.
Dymanic SQL thats not open to vunerability.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply