[Last Call] Learn how to a build a cloud-first strategyRegister Now


Table Value UDF Performance

Posted on 2011-10-20
Medium Priority
Last Modified: 2012-05-12
I have a table valueUDF that has recently started to perform rather poorly. I was attempting to "disect" the logic within the UDF to determine which aspect was causing the issue.

In the course of doing this, I executed a script to simply DROP & CREATE the UDF using its original definition.

After I did this, the performance went back to original levels (28 seconds down to 2 seconds). Are there some sort of plans/statistics maintained for UDFs like there are for tables? If so, can an UPDATE STATS like command be run against UDFs - I can't seem to find any references to this effect.
Question by:franco2
  • 4
  • 3
  • 2
  • +1
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 37003849

Its possible the sql had cached some plan for the query or SP that called this function.

By deleting the UDF, you caused sql to create a new plan for the query

Author Comment

ID: 37004125
It's a dynamic query being built from code - is there a way to call an sp_recompile on the UDF to drop any existing plans?
LVL 75

Accepted Solution

Anthony Perkins earned 375 total points
ID: 37004146
Do you ever update stats?
How about posting the query and the UDF, perhaps there is something that can be improved.
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

LVL 25

Assisted Solution

jogos earned 375 total points
ID: 37004341
Lesson learned : first make inventory of AS IS before you touch anything now you can't see where the gain was and it could as well reoccur in a few months.

Here http://technet.microsoft.com/nl-nl/library/cc966425(en-us).aspx you can find of info on the combination : recompile , plan reuse...
The UDF itself is created by code? So does that happen regularly and is it done also with elements refered in the UDF?  How is the maintenance of  your db (fragmentation, indexes, transactionlog backup, statistics) and where there recent problems?  Was there a large increase/decrease of data?
The number of questions could be very limite when you could compare the execution plan before and after

Author Comment

ID: 37522474
As we are no longer able to reproduce the problem, we've optimized the UDF as much as possible at this point and will attempt to examine stats / execution plans if or when the problem reoccurs.

Author Comment

ID: 37522713
I've requested that this question be closed as follows:

Accepted answer: 0 points for franco2's comment http:/Q_27407924.html#37522474

for the following reason:

Unable to reproduce problem so no acceptable answer can be determined at this time
LVL 25

Expert Comment

ID: 37522714
You ask for an explanation of a performance gain you have seen. You get a very possible explanation of cached plans #37003849 and the advice to make in future inventory before you do anything #37004341.

It would be strange you should be able to reproduce a bad cached plan and with your final comment 'and will attempt to examine stats / execution plans if or when the problem reoccurs' you just refrase the 'lesson learned'.

So you still think you didn't have anything usefull from us?
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37522732
I have to agree with jogos and to make matters worse the author abandoned the question for three months.

Author Closing Comment

ID: 37525808
Didn't mean to get people riled up about this; have accepted solutions from both of you and split points accordingly.
LVL 25

Expert Comment

ID: 37526384
Best explanation remains the first post, old cached plans and why delete create have solved that.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question