Profiling statements inside a User-Defined Function

Question

I'm trying to use SQL Server Profiler (2005) to track down some application performance problems. One of the calls being made is to a table-valued user-defined function. This function wraps a select that joins several tables together.

In SQL Server Profiler, the call to the UDF is logged. However, the select that underlies the UDF isn't being logged at all. Because of this, I'm not getting useful data on which tables & indexes are being hit. I'd like to feed this info into the Database Tuning Advisor for some indexing advice.

Is there any way (short of unwrapping the queries themselves) to log the tables called by UDFs in Profiler?

Answer 1

You can't: a multi-statement TVF is a black box and you can only get CPU, Read, Writes etc.

by "black box" I mean it's a fully encapsulated and opaque series of statements inside another query, and there is no "flow" like you'd get line by line through a stored proc.

An in-line TVF is expanded like a view or macro into the main query and can be seen.

Edit: related: Table Valued Function where did my query plan go?