I am trying to browse online but can not get enough info on how to measure performance of scalar UDF's in SQL server 2014.

Newer version yes i do see from plans we can get the same and newer dmv's

But i have on my SQL 2014 where bunch of scalar uDF's exist including the one which includes function involving calculation and functions calling base or user tables as well.

I am seeing blocking and SP performance and seems few calls using those scalar functions. But could not find a way to prove how bad those functions might be when calling from so many effected SP's

please help

  • 1
    I would upgrade, convert your scalar functions to inline TVFs, or use Extended Events (see mssqltips.com/sqlservertip/4689/…). Mar 25, 2020 at 19:54
  • @AaronBertrand: I totally agree with you, upgrade to 2017/2019 is in project planning and going on while we speak. Now to ask my developers to inline is for what i raised this question. I am having hard time to prove results. I looked at link, unfortunately cant use trace. But i do have xtevents running, collecting sp statement completed, rpc completed, sql batch but not module end. How can i get info from them as i do not see fn info there, thanks Mar 25, 2020 at 20:25
  • 2
    sql_statement_completed should allow you to aggregate cpu_time, duration etc. And re-writing a similar query to use an inline table-valued function you should be able to prove by just running them and seeing the performance difference. There is no better proof than that. Mar 25, 2020 at 20:32


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy