Table valued function when converted to stored procedure runs slow

I've a pretty complex table valued function doing inner joins over various tables. I've to tune this function. SQL wan't showing actual execution plan executed inside the function. So I thought to convert this to stored procedure and see the query plan. When I ran the original function as well as this new stored procedure in single b开发者_高级运维atch, 100% time was consumed by stored procedure.

Is this expected? I'm using SQL Server 2008.

Yes, this is expected. A multi-statement table valued function (TVF) is a black box and the internal processing isn't visible.

It isn't running slow (well it could be for other reasons, but this isn't the question). The optimiser can not judge the TVF vs the procedure correctly and so allocates 100% of the batch to the proc.

One way is to run the code (separatley) and use SQL Profiler to note Duration, Reads, CPU.