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.