Execution plan for ITVF called by Stored Procedure

Question

Hello Forum,

I'm a developer that's new to the world of SQL Server, having come from using Oracle as the RDBMS for the past decade.
My question is regarding finding execution plans for inline table-valued functions called from stored procedures.

The scenario is the following ... SP1 (stored procedure 1) returns two result sets, both of which query ITVF1 (inline table-valued function 1).  SP1 is called from a Python module, using PyODBC.  When running the SQL Server Profiler, I can see that PyODBC is using prepared statements for calling SP1.  What I would like to do is to use the SQL Server Profiler to capture the execution plan that ITVF1 uses when it is called by SP1.  So far, I have not found a way to do that.  Is it possible?

I looked through the plan cache and also did not find any trace of the execution plan I'm looking for.  Here's the query I used for looking through the plan cache:

SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE TEXT like '%SecMasterSearch%'

Is SQL Server capturing the execution plan for ITVF1 somwhere in this scenario?  Or is it lost since ITVF1 is two levels deep in the call chain (i.e. called indirectly from a prepared statement by way of SP1)

Let me know if I have not provided sufficient background information.

Thanks, in advance, for any tips.

K

Answer 1

Hi,

These articles have helped me with researching plan cache in the past - perhaps you have already read some or all of these but if not, it's good reading.

http://www.sqlmag.com/article/tsql/understanding-query-plans-141850

http://sqltwist.com/archives/64

https://www.simple-talk.com/sql/t-sql-programming/dmvs-for-query-plan-metadata/

---

A SQL Server MVP or MSFT Eng should be replying soon as well. Hope this helps. Frank Garcia *** Please select "Vote As Helpful" if the information provided was helpful to you. If an answer to your issue solved the problem then please mark it as "Propose As Answer" located at the bottom. Thank you. ***