How to reduce the est cost for index seek on object3.index11, currently its 53.6%

sanjiv bishnoi 2015-08-28 11:19:20

SQLkiwi 2015-08-28 15:11:42
The estimated cost is only a problem if it turns out to be a good reflection of runtime performance. Unfortunately, this currently very difficult to assess because SQL Server does not expose per-operator actual runtime costs.

This index seek is probably optimal, from what I can see in an anonymized plan. The table is large (41 million rows), so a hash or merge join would likely be more expensive than 56,857 seeks. There is no residual predicate, and it seems to be a single-row (equality on unique index) seek each time. With no physical I/O in the Table I/O tab, it seems relatively safe to say this seek is not the cause of the slow performance (467s elapsed).

One thing that did leap out at me: in the final Compute Scalar, there is a call to a scalar function, fn_BK_GetOrderType, which is executed 56,070 times. UDFs are estimated at essentially zero cost, since SQL Server cannot assess what it might do at runtime. If the function performs data access (as the name suggests it might), this might well be the cause of the slow performance.

Try running the query with this function call commented out, and see what effect that has. Scalar functions (especially those that perform data access) can be remarkable performance-killers, since the function's query is executed once for each row it is called on. In the present case, that is very like running 56,070 separate queries, in addition to the main query plan. If certain settings are on, or someone is collecting statement-level or plan trace information via Profiler or Extended Events, the effects can be even more dramatic. Bottom line: it is normally best to avoid scalar functions completely.

That's about all I can say from an anonymized plan. If that doesn't help, please consider sharing a non-anonymized plan.

Kevin 2015-09-10 19:27:47
Hi Paul, Where did you find the named scalar function "fn_BK_GetOrderType"? I can't find it anywhere when I looked through the various tabs in PE. Of course, I'll freely admit that I didn't take the time to scroll all the way through the plan XML. Was it located there? Many thanks,

-Kev

SDyckes2 2015-09-11 16:10:06
Kevin, it is located in the XML.
GregGonzalez 2015-09-11 18:00:44
This actually looks like a bug, the udf name should have been anonymized out. We're logging it.
SQLkiwi 2015-09-12 00:01:06
Just to confirm: yes I found it by searching the XML showplan.