Find UDF useage

  • Comments posted to this topic are about the item Find UDF useage

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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

  • Great catches!  Thank you!

  • 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


    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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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!

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • 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')

  • 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