Don't ignore the cost of user-defined functions - by Hugo Kornelis

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


11
1
Sign in
to vote
ID 744019 Comments
Status Closed Workarounds
Type Bug Repros 3
Opened 5/25/2012 12:06:12 PM
Access Restriction Public

Description

Even when SQL Server knows the cost of executing a scalar user-defined function, that cost is not incorporated in the cost of an exeution plan that involves calling the function.

This effectively cripples the optimizer, because it can't produce good results if it hass to work on bad data.
Sign in to post a comment.
Posted by Hugo Kornelis on 11/3/2014 at 1:58 PM
I must agree with the previous comment.

Every client I walk into has a bunch of user-defined functions in their database. Every one of these functions has an estimated cost for the function itself of more than 0.0000001 cost units, usually significantly higher. Yet, that value is the hardcoded cost that the optimizer will use for a single UDF execution when generating the execution plan. This often results in plans that are extremely sub-optimal, up to invoking a deterministic function multiple times for a single row. And because the optimizer insists that the function is almost free, it will actually fight attempts to rewrite the query to get a better plan.

Obviously the best solution is to not use scalar user-defined functions at all, but we all know that this is realistically not going to happen.
Posted by SAinCA on 4/29/2013 at 2:25 PM
This statement is nigh UNBELIEVABLE: "the scenarios reported in the bug are not common enough"

I've yet to encounter significant volumes of T-SQL that DO NOT use at least one scalar UDF in its queries.

@Gus: how on earth did you arrive at this sorry conclusion? It doesn't appear you ventured out into the "real world" at all...

EVERY cost in a query MUST be accumulated for the COST BASED OPTIMIZER to be called such, or should we stop using the term COST BASED when it comes to SQL Server and call it the "Finger in the air" Optimizer?
Posted by Microsoft on 4/26/2013 at 10:24 AM
Hello,

Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. The reasons for closing this bug is because the scenarios reported in the bug are not common enough and so unfortunately it does not meet the bar for the current release.

If you have more supporting information and details or feel strongly that we should reconsider, please do resubmit this item for review.

Thanks again for reporting the product issue and continued support in improving our product.
Gus Apostol, SQL Server Program Manager
Posted by The Jams on 12/27/2012 at 10:50 AM
The Database tunning wizard does not have these limitations and is a better representation of what the Querry Optimizer will do than a single execution of a newly created UDF from a single client.
Posted by Microsoft on 7/19/2012 at 10:40 AM
Thanks for your feedback, we are looking into this and will get back to you.