March 16, 2017 at 4:19 pm
Comments posted to this topic are about the item Find UDF useage
March 19, 2017 at 5:00 pm
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
Change is inevitable... Change for the better is not.
March 22, 2017 at 2:05 pm
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
March 22, 2017 at 2:13 pm
Great catches! Thank you!
March 22, 2017 at 10:21 pm
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". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2017 at 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!
March 23, 2017 at 1:36 pm
Jim Youmans-439383 - Thursday, March 23, 2017 6:13 AMWhile 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
Change is inevitable... Change for the better is not.
May 12, 2017 at 7:58 am
Hi, I just added a script that uses some in built stored procedures that show the actual object dependencies. It's essentially doing the same thing though.
https://www.sqlservercentral.com/Forums/1874275/Find-function-dependencies
Have a look and let me know if you think it's useful. It runs quickly.
sys.dm_sql_referencing_entities(@SchemaFunction,
'OBJECT')
May 19, 2017 at 5:28 am
Excellent script, slight modification to include schema name, just in case anybody else needs that info
-- create table to hold function names
CREATE TABLE [#TempFunctions]
(
[ID] INT IDENTITY(1, 1)
, [schemaName] NVARCHAR(256)
, [fnName] VARCHAR(256)
);
-- create table to hold stored procedure and function that is in it names
CREATE TABLE [#spWithFn]
(
[ID] INT IDENTITY(1, 1)
, [schemaName] VARCHAR(256)
, [spName] VARCHAR(256)
, [fnName] VARCHAR(256)
);
-- fill the function name table
-- FN = SQL_SCALAR_FUNCTION
-- IF = SQL_INLINE_TABLE_VALUED_FUNCTION
-- TF = SQL_TABLE_VALUED_FUNCTION
INSERT [#TempFunctions]
( [schemaName]
, [fnName]
)
SELECT
OBJECT_SCHEMA_NAME([object_id]) AS [Schema]
, [name]
FROM
[sys].[objects]
WHERE
[type] IN ( 'FN', 'IF', 'TF' )
DECLARE @fnName VARCHAR(256);
DECLARE @SQLCmd VARCHAR(512);
WHILE ( (
SELECT
COUNT(1)
FROM
[#TempFunctions]
) > 0 )
BEGIN
-- get one function
SELECT TOP 1
@fnName = [fnName]
FROM
[#TempFunctions]
ORDER BY
[fnName]
-- search all stored procedures for that function name
SET @SQLCmd = 'INSERT #spWithfn(schemaName,spName,fnName)
SELECT OBJECT_SCHEMA_NAME(object_id), Name, ''' + @fnName
+ ''' FROM sys.procedures WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ''%' + @fnName + '%'''
EXEC(@SQLCmd);
-- delete function from function table
DELETE
[#TempFunctions]
WHERE
[fnName] = @fnName;
END;
-- report on stored procs and number of UDFs contained
SELECT
[schemaName]
, [spName]
, COUNT(1) AS [NumberUDFs]
FROM
[#spWithFn]
GROUP BY
[schemaName]
, [spName]
ORDER BY
COUNT(1) DESC;
-- Show stored proc name and function name
SELECT
*
FROM
[#spWithFn]
ORDER BY
[spName];
DROP TABLE [#TempFunctions];
DROP TABLE [#spWithFn];
gsc_dba
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply