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.
I used to have a presentation called “The Dangers of BEGIN and END” that looked at this stuff. 🙂
Thanks Grant, that is cool!!!
Thomas
Thanks for fleshing this out Grant! My job has asked me talk about reading execution plans with some of the greener devs. I’ll mention your point here and have them take a look at this post 🙂
Glad it’s useful. It’s one of those things I’ve known about forever just kept forgetting to put it up in a blog post.
Rob, I’d sit through that session. Sounds good.
Thanks for posting this – that query will certainly come in handy as I slog through our code to excise the “evil” UDFs that hurt the performance of our application.
Thanks for sharing your thoughts. I really appreciate your efforts and
I will be waiting for your further write ups thank you once again.
Usually I will try to avoid writing and using a function whenever possible because they are resource pigs. The exception is when I need some “black box” code to run. For reporting we have two particularly ugly queries that determine a records “as of” state and another to determine the fiscal year. I don’t want this code in so many places in case there is a mistake in it (there has been more than once). Under these circumstances a function makes sense but so far only under this circumstance.
Thanks for posting this! I’ll be using it in my SQL Saturday demo,,,
I’m having trouble running the query. I get an error message for all three columns like:
The multi-part identifier “deqp.query_plan” could not be bound.
If I do a ‘Select *’ instead it works.
Somehow the aliases don’t seem to work.
Any suggestions as to what I’m doing wrong?
Thanks lots!
Sorry, not sure. It’s working fine on the machines I tried it on. In fact, SELECT * should give you an error because several of those DMOs share common columns.
Reiner – you may want to check that the query copied okay. For example, check those apostrophes haven’t come across as ‘smart apostrophes’, and that the “minus” hasn’t come over as an “em-dash”.
I had checked the apostrophes, but not the minus. It indeed came over as an “em-dash”.
Thanks lots.
[…] Execution Plan for a User Defined Function – A look at the hidden complexity your execution plans may be covering up. Grant Fritchey (Blog|Twitter) reveals all. […]
Hi.
I have a question regarding datepart and dateadd. I had an INSERT running which took a very long time and generated very high logical reads and very high CPU cost. Is is possible to measure the true cost of these?
[…] Execution Plan for a User Defined Function by Grant Fritchey […]