Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have this query on SQL server, a merge replication query:

SELECT DISTINCT
    b.tablenick,
    b.rowguid,
    c.generation,
    sys.fn_MSgeneration_downloadonly
    (
        c.generation,
        c.tablenick
    )
FROM #belong b
LEFT OUTER JOIN dbo.MSmerge_contents c ON 
    c.tablenick = b.tablenick
    AND c.rowguid = b.rowguid;

The estimated query plan includes information about 3 queries:

  1. The query above
  2. The function call to fn_MSgeneration_downloadonly
  3. The function call to fn_MSArticle_has_downloadonly_property

The actual query plan includes only this information:

  1. The query above

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

I tried these options:

SET STATISTICS PROFILE ON
SET STATISTICS XML ON

Which created an actual plan, but it was missing parts 2 and 3 same as when I used the actual query plan option in Management Studio.

If for instance I was to use Profiler to capture the information about the function calls what events would I select?


Didn't find an answer specifically related to the query plans, but I profiled SP:StmtStarting and SP:StmtCompleted and it showed the function calls.

share|improve this question
add comment

1 Answer 1

up vote 11 down vote accepted

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:

SSMS pre-execution plan

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

PE pre-execution plan

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;

SSMS post-execution

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:

Trace 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:

Function plans

The parent query plan is:

Parent plan

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.

share|improve this answer
    
So in your opinion is the query I am profiling a bad query based on the fact that it calls the function for every row returned. This query comes from sp_MSsetupbelongs and so I am surprised that MS would create a query like that. That query has caused a performance issue in merge replication for us. This is the conclusion I came to by using profiler as I described. –  peter Dec 4 '12 at 19:52
    
@peter It depends on the content of the function. If it is something that could be in-lined it probably should be. Microsoft SQL code is quite well-known for being less than shining examples of best practice. I don't know the details of merge replication very well, so I can't comment too much on the specifics, but that's the general view. –  Paul White Dec 4 '12 at 20:18
    
Thanks. I have brought it up with microsoft on the connect website. I think it could be inlined, but I am not 100% sure. Thanks for your help. SQL Sentry Plan Explorer looks interesting. –  peter Dec 4 '12 at 23:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.