Get an actual plan for a stored function

Question

I want to get an execution plan for my scalar function because it differs from one when I'm only running a function body. I was in same situation some time ago, and I solved it by defragmenting indices (function didn't use fragmented indices because of high fragmentation, but pure query did). But now i have <0.1% fragmentation.

I can get proper estimate execution plan, but when I want to get an actual plan, I get almost nothing.

So here I get only useless outer query plan, but don't see what's happening in function.

Can it be fixed?

---

Estimated execution plan:

<ParameterList>
  <ColumnReference Column="@DateTo" ParameterCompiledValue="NULL" />
  <ColumnReference Column="@DateFrom" ParameterCompiledValue="NULL" />
  <ColumnReference Column="@ServerID" ParameterCompiledValue="NULL" />
  <ColumnReference Column="@ResourceTypeID" ParameterCompiledValue="NULL" />
</ParameterList>

---

I found an actual execution plan for the function (thanks to Sql Server Profiler)

Actual plan for pure query (fast): function (slow):

this function is just taking some XML field from table and join them into one big XML file:

ALTER FUNCTION [dbo].[fn_GetErrorXML]
(   
    @DateFrom datetime,     
    @DateTo datetime,       
    @ResourceTypeID bigint,  
    @ServerID int           
)
RETURNS NVARCHAR(MAX) 
AS
BEGIN 

    DECLARE @ErrorDescription VARCHAR(MAX)
    SET @ErrorDescription = '<root>';
    WITH CTE AS
    (   

        SELECT 
            d.GeneratedOnUtc,
            ErrorDescription = cast(d.Data as nvarchar(max))
        FROM dbo.AgentData d
          INNER JOIN dbo.Agent a ON a.CheckID = d.CheckID
          INNER JOIN dbo.Server s ON a.ServerID = s.ID
        WHERE d.EventType = 'Result' AND
              a.ResourceTypeID & @ResourceTypeID > 0 AND
              s.ID = @ServerID AND
              d.GeneratedOnUtc between @DateFrom AND @DateTo AND
              d.Result = 0                            
    )
    SELECT @ErrorDescription = @ErrorDescription + cte.ErrorDescription
    FROM CTE    
    ORDER BY cte.GeneratedOnUtc ASC

    RETURN(@ErrorDescription + '</root>')
END

Answer 1

Well, answer for my initial question: you can use SQL Server Profiler and profile your database and get plans for all executing queries, just toggle a chechbox in new trace settings.