Matthew McGiffen searches for the root of the problem:
In this post we look at a method using Extended Events (XE) to identify what parent objects are calling a given SQL function and how often.
The background is that I was working with a team where we identified that a certain scalar function was being executed billions of time a day and – although lightweight for a single execution – overall it was consuming significant CPU on the server. We discussed a way of improving things but it required changing the code that called it. The problem was that the function was used in about 700 different places across the database code – both in stored procedures and views – though the views themselves would then be referenced by other stored procedures. Rather than update all the code they’d like to target the objects first that execute the function the most times.
Read on to see how Matthew did it, as well as some caveats along the way.