0

The database that I'm working on unfortunately has a lot of functions, with select statements in them, that are heavily used in where clauses in multiple stored procedures. This is not good design or good for performance, obviously.

Let's say one of the functions is named ObjectAccessGet. It comes up first when I run the Query Store "top resource consuming queries" report, in CPU and Logical Reads.

I'm trying to answer the question: "Of the stored procedures that call the function ObjectAccessGet, which ones are causing ObjectAccessGet to be used the most?

This will then determine WHICH of the stored procedures that call ObjectAccessGet that I will fix first.

I'm happy looking directly at the underlying tables (for instance sys.query_store_runtime_stats, etc).

1

Can I?

No, not really. You can tie a statement to an object, and you can use the built-in dependency reports to figure out which objects call other objects (within a single database, not cross-database):

NUTS

The main problem you'll find with scalar, and to some degree multi-statement table valued functions, is that they may be executed multiple times per query.

Figuring out which stored procedures call them "the most" isn't just a matter of references in the code. It's really how many rows a query must pass through the functions, where in the query plan the function is referenced, or for MSTVFs, how many times a Nested Loops join was executed to call to them.

It's a whole thing.

Options

You may find it easier to track down ill-performing queries, and check them for functions to rewrite, avoid, etc. Not every function is going to ruin every query, though they do have a bad reputation for a reason.

If you use sp_BlitzCache (available here), or sp_QuickieStore (available here), you may have better luck tracking down generally poorly performing queries for examination.

In particular, sp_BlitzCache will parse plan XML to look for function calls. You may see warnings about Forced Serialization, UDF calls, and Filter UDFs.

In the interest of full disclosure, I am a contributor/maintainer of both of those open source projects.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.