When you execute a multi-statement user-defined function you may see an execution plan that looks something like this:

It appears as if the cost of the UDF is free. This is especially true if you use the UDF in a query with other objects, such as joining it to actual tables. Since the optimizer always assumes a multi-statement UDF has a single row for statistics estimates, it’ frequently displays a low cost.

But you know that there’s more going on there, right? It’s a multi-statement UDF because it’s doing a lot of work, but that is not reflected in the execution plan.. or is it?

What if we went after the cache? Let’s run this little query:

SELECT  deqp.query_plan,

dest.text,

SUBSTRING(dest.text, (deqs.statement_start_offset / 2) + 1,

(deqs.statement_end_offset – deqs.statement_start_offset)

/ 2 + 1) AS actualstatement

FROM    sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

WHERE   deqp.objectid = OBJECT_ID(‘dbo.ufnGetContactInformation’);

This query doesn’t do anything really fancy. I’m using the sys.dm_exec_query_stats which holds aggregated performance metrics for any query currently in cache because it has the plan_handle that lets me look up execution plans in sys.dm_exec_query_plan. It just so happens that you also get the objectid there in the plan so we can look for the specific plan that corresponds to our UDF (I’m using an example from AdventureWorks2008R2). The results from the query look like this:

Each row represents a statement that has been executed from the UDF. There’s just a single plan for the UDF, and it looks like this:

Without even drilling down to all the details of what’s going on in the plan, I think we can agree, that this UDF is not zero cost, but may in fact be extremely expensive depending on what’s going on inside that execution plan.

Why is this useful? Because it lets you look behind that simplistic… lie, that is shown as a plan for the execution of a UDF to see what the UDF is actually doing. This may make it possible to improve the performance of the UDF since you’ll be able to identify missing indexes and other information thanks to your direct access to what’s going on inside the UDF.

I had demoed this during my 24 Hours of PASS presentation and was asked to put the code up on my blog, so here you go. I hope it’ proves helpful.