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.
Yes, this is the case - SSMS does not always show you the estimated rows * number of executions, and this can obviously lead to some inaccurate assumptions if you don't dig deeper as you did. But technically the answer to your question is no. Sometimes SSMS will get this estimate right, depending on the operator and other semantics.
In SentryOne Plan Explorer we try to compensate for this by showing SQL Server's rows * number of executions in several cases instead of just blindly copying the row count from the plan XML. Your plan, for example:
Best Answer
Think about what "actual" means. It's what actually happens for the execution of that plan.
Another common name for the actual execution plan is the "post execution plan". As a real world example to correlate this scenario, say you plan to go on a cross country trip, so you plot out the roads you're going to take and how long you think it'll take. But because of road work and detours, it doesn't happen quite like you planned. That's the actual execution plan: The actual route that you took to get to where you're going. The estimated plan would have been what you thought you'd be doing, and what route you thought you should take.
You can't store post execution plans because they are just that....post execution. They contain metrics of what really happened, not what SQL Server thinks should happen (estimated execution plan).
The plan cache is really just a repository of "maps" that it can reuse without having to spend the expensive time to draw up a new one. Actual execution plans are after the fact, and the plan cache is to handle before the fact.