Performance impact: SQL trace and user defined scalar functions

re: Performance impact: SQL trace and user defined scalar functions

Michael K. Campbell — Mon, 15 Jun 2009 23:46:34 GMT

Awesome post.

Very timely too - as I'm almost positive that I'm running into something VERY similar with a client I'm currently helping - where a sproc that repeatedly calls a UDF gobs of times (i.e. more than 50k) seems to take INSANELY longer when it's being OBSERVED. And, your post has helped give me some additional insights to pursue - because I was beginning to suspect very similar issues.

Great work though, and great post. Loved the intro/anecdote and the details you've provided.

re: Performance impact: SQL trace and user defined scalar functions

david wei — Tue, 16 Jun 2009 17:22:28 GMT

Linchi, are you using server side trace or client tool (the profiler)? Thanks.

David.

re: Performance impact: SQL trace and user defined scalar functions

Linchi Shea — Tue, 16 Jun 2009 17:52:33 GMT

Server side SQL trace to a file for the tests described here.

re: Performance impact: SQL trace and user defined scalar functions

david wei — Tue, 16 Jun 2009 20:13:25 GMT

Thanks, just wondering if any difference if we use Extended Event ...

re: Performance impact: SQL trace and user defined scalar functions

Mike Walsh — Wed, 17 Jun 2009 03:15:27 GMT

Great post, Linchi. Was actually just thinking of doing a "function" post myself with a quick reminder of some of the dangers of even unwatched functions. Sure we all know it but I sure bump into them a lot still..

Michael --> Linchi's post could explain the issue your client is seeing. It could also just be that the stored procedure is embarrassed.. When you are observing it, it shamefully realizes that the function is not so pretty and the feeling of dejection and shame it experiences when it knows that you are watching is just too much for the poor proc to bear with/

re: Performance impact: SQL trace and user defined scalar functions

Simon — Sun, 19 Jul 2009 14:00:57 GMT

I talk about the issues with scalar udfs in the above post. I also mention a way of minimizing the impact of udfs when doing sp and stmt level tracing. Has a dramatic improvement.

re: Performance impact: SQL trace and user defined scalar functions

Vish Baliga — Tue, 11 Aug 2009 11:44:17 GMT

The --CREATE FUNCTION... also shows up in the DMVs, does that mean we are incurring a hit even without tracing?

re: Performance impact: SQL trace and user defined scalar functions

pl80 — Tue, 16 Mar 2010 16:47:22 GMT

Great post, many thanks! Microsoft explains, that SP:Completed means that a stored procedure completed (who would have thought?) BUT IT ALSO MIGHT MEAN THAT A FUNCTION COMPLETED!!! This is the most important info in this post for me. I run a trace, and included the SP:Completed event. Guess what: > 1 Million trace records in 1 min because a view was using some scalar functions.

re: Performance impact: SQL trace and user defined scalar functions

Erik Eckhardt — Tue, 03 Jul 2012 22:01:31 GMT

Thanks for posting the results of your learning so we all could learn, too. Some valuable stuff here.