Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find UDF useage


Find UDF useage

Author
Message
Jim Youmans-439383
Jim Youmans-439383
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 522
Comments posted to this topic are about the item Find UDF useage
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71689 Visits: 40748
Thanks for sharing.

Be aware that, unless someone lost their mind while writing them, the "IF" (inline Table Valued Function or "iTVF")) is usually not the source of a performance issue. In fact, iTVFs can be used to replace slower Scalar and multi-Statement Table Valued Functions (mTVFs). Please see the following article for a fun little exercise.
http://www.sqlservercentral.com/articles/T-SQL/91724/

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lee Linares
Lee Linares
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 1424
Thanks Jim for a very handy script. I did notice a few minor 'gotchas' though. Nothing major:
1. If the UDF name is included ONLY as a COMMENT in the stored procedure , it will still be returned
2. If there are UDFs with similar names, you will get a false return. For example: If you have 2 UDFs named fnEasterSunday and fnEasterSunday2, then fnEasterSunday2 will be returned as having a reference to fnEasterSunday.

I modified the code slightly to use sys.modules so I could also find any views or other UDFs that might reference the functions:

-- search all objects for that function name
SET @SQLCmd =
'INSERT #spWithfn(spName,fnName)
SELECT OBJECT_NAME(object_ID) AS Name, ''' + @fnName + ''' FROM sys.sql_modules WHERE [definition] LIKE ''%' + @fnName + '%'''

I found that I had to add a line after the WHILE loop finished to eliminate self-references:

DELETE #spWithFn WHERE spName=fnName

Thanks again for taking the time to create and share this very useful script.

Lee
Jim Youmans-439383
Jim Youmans-439383
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 522
Great catches! Thank you!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71689 Visits: 40748
I also think it's worth mentioning that Scalar UDFs, although certainly can be bad especially when compared to their iTVF cousins, are usually not the top concern on a system with performance problems. It's more likely that things like accidental many-to-many joins that the programmer tried to overcome with the likes of DISTINCT, are usually much worse than any mere Scalar UDF. Things like an index seek occurring thousands of times in the same query (1 seek per row) are forms of "hidden RBAR". Things like datatype mismatches on join criteria and non-SARGable queries can also cause much more of a problem than even a shedload of Scalar UDFs. Then, there's ORM code which may be recompiling every time it runs (a problem that's difficult to find for most and can be much worse.... We just fixed such a thing in our system that was taking 2-17 seconds to compile and "only" took 100ms to execute that runs thousands of times per hour).

The bottom line is that there are a ton of things much worse than Scalar UDFs. You could end up finding and replacing every Scalar UDF and still have a system in deep kimchee. Don't waste your time on a "pre-optimization" effort of finding and replacing all Scalar and Multi-Statement Table Valued Functions (mTVFs) with iTVFs (inline Table Valued Functions) unless they are actually in the top ten of your actual performance problems. Instead, fix those top 10 problems. "Keep your eye upon the donut and not upon the hole". Wink

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jim Youmans-439383
Jim Youmans-439383
SSC Veteran
SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)SSC Veteran (217 reputation)

Group: General Forum Members
Points: 217 Visits: 522
While I agree with you in general, in my case the Scalar UDF was the issue. It was a simple convert INT to DATE (20170101 to 01/01/2017) in the select part of the query. The UDF was being called for every row of the result set (in this case it was about 250K). By replacing the UDF with TSQL code, I was able to improve the performance tremendously. I wanted to be able to see what other stored procedures were calling UDFs.

Thanks!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71689 Visits: 40748
Jim Youmans-439383 - Thursday, March 23, 2017 6:13 AM
While I agree with you in general, in my case the Scalar UDF was the issue. It was a simple convert INT to DATE (20170101 to 01/01/2017) in the select part of the query. The UDF was being called for every row of the result set (in this case it was about 250K). By replacing the UDF with TSQL code, I was able to improve the performance tremendously. I wanted to be able to see what other stored procedures were calling UDFs.

Thanks!


Oh yes... I absolutely agree and even said similar in my writeup. IF a UDF is actually the cause of a performance problem, it should be fixed.

And, to be clear, none of what I said was a slam on your code or article. I just had this vision that someone that may not know better may take your good code and make it an urgent mission to replace all Scalar UDFs in all their databases instead of correctly identifying possibly much more serious problems and fixing those first.

To be sure, thank you for taking the time to submit the code, explain why you wrote it, and participate in this discussion. Well done and thank you for making a difference.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search