26Mar 2012 by Grant Fritchey 15 Comments

Execution Plan for a User Defined Function

SQL Server, T-SQL
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) /…
Read More
07Jan 2009 by Grant Fritchey No Comments

Table Valued Functions

T-SQL
I've been blogging for a whole nine months now. I decided to look back and see what's bringing people to the site. The number one search phrase is "sql server 2005 service pack 3" but the overwhelming topic that most people are using to get to the site are user defined functions, specifically multi-statement table valued user defined functions. It's completely understandable. Ever since I first saw these things in use back in SQL Server 2000, I thought they were slick. Unfortunately appearances can be deceiving. The reason so many people are searching out information on these things is because they just don't work very well. SQL Server can't create statistics on the tables generated through the multi-statement UDF. Because it has no statistics to work with, the query optimizer…
Read More
21Nov 2008 by Grant Fritchey No Comments

Multi-Statement Table Value Function Alternative

T-SQL
I was talking with Andrew Novick at the PASS Summit. We ended up talking about multi-statement table valued functions. I was talking about how much the performance of these things is weak (to be kind). He agreed, but suggested an alertnative that might be worth further exploration, if you really think you need multi-statement UDFs. Andrew said that in his testing, using CLR offered a great alernative to using the UDF. I'm still pretty convinced that any type of programming you're doing on the SQL Server end that requires a UDF or CLR is probably either just TSQL gone wrong (see Jeff Moden and the RBAR concept) or it's something that doesn't belong on the SQL Server but instead should be done on the application layer somewhere. Still, it is…
Read More
15Aug 2008 by Grant Fritchey No Comments

More on Table Valued Functions

T-SQL
From the hits in the search results, this is a popular topic. That being so, I'd like to redirect you to another blog that has some more detailed tests available. These tests show more of the shortcomings of multi-statement table valued functions. Gail Shaw is an MVP and a regular at SQL Server Central. She's worth tracking and this post shows why. I want to be clear. I'm not suggesting that you never, ever, use multi-statement table valued functions. There may be places where their use is helpful. I'm saying that using them comes with a very heavy cost, so you better be sure that they are in fact needed in the situation, whatever it is. There was a long discussion and debate over at SQL Server Central recently on…
Read More