Sql-server – SQL extended event captured only the estimated execution plan but no actual execution plan

And nothing about the functions. Why is the function information missing in the actual plan?

This is by design, for performance reasons.

Functions that contain BEGIN and END in the definition create a new T-SQL stack frame for each input row. Put another way, the function body is executed separately for each input row. This single fact explains most performance problems associated with T-SQL scalar and multi-statement functions (note that in-line table valued functions do not use the BEGIN...END syntax).

In the context of your question, this would result in full SHOWPLAN output for each row. XML plan output is quite verbose and expensive to produce, so producing full output for every row would be a bad idea in general terms.

Example

Consider the T-SQL scalar function below, created in the AdventureWorks sample database, which returns the name of a product given its ID:

CREATE FUNCTION dbo.DumbNameLookup
(
    @ProductID integer
)
RETURNS dbo.Name
AS
BEGIN
    RETURN
    (
        SELECT
            p.Name
        FROM Production.Product AS p
        WHERE
            p.ProductID = @ProductID
    );
END;

Pre-execution plan

A pre-execution plan (estimated plan in SSMS) shows plan information for the parent statement and nested function calls:

-- Pre-execution plan shows main query and nested function call
SET SHOWPLAN_XML ON;
GO
SELECT dbo.DumbNameLookup(1);
GO
SET SHOWPLAN_XML OFF;

SSMS output:

The same XML viewed in SQL Sentry Plan Explorer shows the nested nature of the calls more clearly:

Post-execution output

SSMS shows details for only the main query when post-execution plan output is requested:

-- Post-execution plan shows main query only
SET STATISTICS XML ON;
SELECT dbo.DumbNameLookup(1);
SET STATISTICS XML OFF;

The performance impact of doing otherwise can be shown using the Showplan XML Statistics Profile Event Class in SQL Server Profiler, using a query that calls the function multiple times (once per input row):

SELECT TOP (5)
    p.ProductID,
    dbo.DumbNameLookup(p.ProductID)
FROM Production.Product AS p;

Profiler output:

There are five separate post-execution plans for the function executions, and one for the parent query. The five function plans look like this in the profiler lower pane:

The parent query plan is:

Executing the query without the TOP (5) clause results in a full execution plan for each of the 504 rows in the Product table. You can probably see how this would quickly get out of hand with larger tables.

The situation for triggers is reversed. These do not show any pre-execution plan information, but do include a post-execution plan. This reflects the set-based nature of triggers; each is fired once for all rows affected, rather than once per row.

In SQL Server 2000 the #temp table version does fail with the message

Invalid object name '#temp'.

However since SQL Server 2005 compiling a plan with a local temporary table does actually create the temporary table behind the scenes.

You can see this by getting the estimated plan for

Create table #temp
(
ID INT IDENTITY (1,1) 
)

Insert into #temp
DEFAULT VALUES

SELECT *
FROM #temp
OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 3604);

DROP TABLE #temp

Which produces an output like

The negative object id is the id of the object fleetingly created. Polling tempdb.sys.tables in a loop whilst generating the estimated plan and comparing with the TableID subsequently output can show this.

The estimated plan you see for the statements referencing the temp table is of limited use however as even the addition of a single row to the temp table will trigger a statement level recompile and potentially a different plan when you actually execute the SQL.

This behaviour does not apply to global temporary tables or permanent tables.

I presume this is also the same reason as

/*Works fine (assuming T doesn't exist)*/
IF 1 = 1
  SELECT 1 AS X
  INTO   T
ELSE
  SELECT 1 AS X
  INTO   T

/*Fails (There is already an object named '#T' in the database.)*/   
IF 1 = 1
  SELECT 1 AS X
  INTO   #T
ELSE
  SELECT 1 AS X
  INTO   #T