Using Extended Events to Find the Actual Execution Plan for a Table Valued Function

While finding the estimated Execution Plan for a Table Valued Function (TVF) isn’t all that difficult, getting to the actual Execution Plan is somewhat of a challenge. Take this example from the AdventureWorks database.
SELECT * FROM ufnGetContactInformation(3)
By clicking on the Display Estimated Execution Plan we get the following:

Estimated:

To get the actual plan, make sure the following option is selected and execute the query.

Actual:

While the estimated gives us all kinds of information, the actual plan keeps the underlying operations hidden in favor of a Clustered Index Scan and a TVF operator. This isn’t very useful when it comes to troubleshooting performance issues especially if your query has multi-table joins to the TVF.
Thankfully, this is where Extended Events (EE) comes into play. By using EE, we can capture the Post Execution Showplan that will give us the actual full plan behind the Clustered Index Scan and TVF operators.
We’ll use the following code to setup the EE:
CREATE EVENT SESSION QueryPlanForTVF ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan (
    ACTION (sqlserver.database_name,sqlserver.plan_handle,sqlserver.sql_text)
    WHERE  (sqlserver.session_nt_user = N'<Your User Name>'))
ADD TARGET package0.ring_buffer
WITH (MAX_MEMORY = 4096 KB
     ,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
     ,MAX_DISPATCH_LATENCY = 30 SECONDS
     ,MAX_EVENT_SIZE = 0 KB
     ,MEMORY_PARTITION_MODE = NONE
     ,TRACK_CAUSALITY = OFF
     ,STARTUP_STATE = OFF);
GO
NOTE: This EE can several decrease performance if used on a high transaction environment. It is not recommended to use this in a production environment.
This session will capture the Showplan XML event, as well as the database name, plan handle, and SQL text of the query. The session is filtered on whatever name you choose (I used my NT login name). Finally, the session sends its output to the ring buffer so we can watch it live in SSMS.
Once the session has been created and turned on, right click on the EE and choose the “Watch Live Data” option.

And the following window will open in SSMS.

Now, in another SSMS window, we’ll execute the query with the TVF again and see the following data flow into the live data viewer.

By clicking on the Query Plan tab, we’ll finally see the full execution plan. Since this TVF has several code blocks in it, there are several plans, but I’ll only show the main one here with the knowledge there are several plans.

Check out the following posts for further reading on using Extended Events and the Query_Post_Execution_Showplan event:
Impact of the query_post_execution_showplan Extended Event in SQL Server 2012
Getting Started with Extended Events in SQL Server 2012