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 a query like this:

select dbo.fn_complexFunction(t.id)
from mytable t

In SQL Sentry Plan Explorer, I noticed I have to run Get Estimated Plan in SQL Sentry to make the Query Plan include the UDF.

When running 'Get Actual Plan', it doesn't appear that the logical reads and other metrics include the operations that occur in the UDF. In cases like this, is the only workaround to use the Profiler?

share|improve this question
1  
To my knowledge, the query engine itself doesn't factor in reads in UDFs. This is a big reason that UDFs are good to avoid, they are opaque to the optimizer. –  JNK Dec 11 '13 at 16:14
1  
Functions, IO statistics and the Execution plan by Gail will be helpful. –  Kin Dec 11 '13 at 19:57
add comment

1 Answer

up vote 10 down vote accepted

Obvious disclaimer: I work for SQL Sentry.

The biggest problems we have are:

  1. Like @JNK says, SQL Server obfuscates away the use of a UDF, and does terrible things with them anyway (like always estimates one row). When you generate an actual plan in SSMS, you don't see its use at all either. We are subject to the same limitations because we can only provide the information about a plan that SQL Server provides to us.
  2. We rely on different sources for runtime metrics when generating an actual plan. Unfortunately the plan XML does not include function calls, and SQL Server doesn't reveal the I/O incurred by a function when using SET STATISTICS IO ON; either (this is how we populate our Table I/O tab).

Consider the following view and function against AdventureWorks2012. This is just a silly attempt at returning a random row from the detail table given a random row from the header table - mostly to make sure we generate as much I/O as possible, every time.

CREATE VIEW dbo.myview 
WITH SCHEMABINDING
AS
  SELECT TOP (100000) rowguid, SalesOrderID, n = NEWID() 
    FROM Sales.SalesOrderDetail ORDER BY NEWID();
GO

CREATE FUNCTION dbo.whatever(@SalesOrderID INT)
RETURNS UNIQUEIDENTIFIER
WITH SCHEMABINDING
AS
BEGIN
  RETURN 
  (
    SELECT TOP (1) rowguid FROM dbo.myview 
     WHERE SalesOrderID = @SalesOrderID ORDER BY n
  );
END
GO

What Management Studio Does (and Does Not) Tell You

Take the following query in SSMS:

SET STATISTICS IO ON;

  SELECT TOP (5) SalesOrderID, dbo.whatever(SalesOrderID) 
    FROM Sales.SalesOrderHeader ORDER BY NEWID();

SET STATISTICS IO OFF;

When you estimate a plan, you get a plan for the query and a single plan for the function (not 5, as you might hope):

Estimated plan in Management Studio

You don't get any I/O data at all, obviously, since the query wasn't actually executed. Now, generate an actual plan. You get the 5 rows you expected in the results grid, the following plan (which makes absolutely no visible mention of the UDF, except in the XML you can find it as part of the query text and as part of the Scalar Operator):

Actual plan in Management Studio

And the following STATISTICS IO output (which makes absolutely no mention of Sales.SalesOrderDetail, even though we know it had to read from that table):

Table 'SalesOrderHeader'. Scan count 1, logical reads 57, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

What Plan Explorer Tells You

When we generate an estimated plan for the same query, we know about the same thing as SSMS. However we do show things in a slightly more intuitive way. For example, the estimated plan for the outer query shows how the output of the function is combined with the output of the query, and it is immediately clear - within a single plan diagram - that there is I/O from both tables:

Estimated plan in Plan Explorer

We also show the function's plan by itself, which I'm only including for completeness:

Estimated plan for UDF in Plan Explorer

Now, let's take a look at an actual plan, which is thousands of times more useful. The downside here is, again, we only have the information that SQL Server decides to show, so we can only expose the graphical plan diagram(s) that SQL Server gives us. This isn't a situation where we've decided not to show you something useful; we actually don't know anything about it based on the plan XML that is provided to us. In this case, it is just like in SSMS, we only see the outer query's plan, and it's as if the function isn't being called at all:

Actual plan in Plan Explorer

Our Table I/O tab, too, still relies on the output of STATISTICS IO, which also ignores any of the activity performed in the function call:

Table I/O for actual plan in Plan Explorer

However, there is functionality only available in our PRO version which gets the entire call stack for you. I have occasionally heard people ask, "Pffft, when will I ever need the call stack?" Well, not trying to sound like a salesman, but here is one case where this has huge value. We can actually break down the time spent, CPU used, and number of reads (and, for TVFs, number of rows produced) for every single function call:

Call stack in Plan Explorer, showing UDF calls

Unfortunately we don't have the ability to correlate that back to which table(s) the I/O is coming from (again, because SQL Server doesn't give us that information), and it isn't labeled with the UDF name (because it's captured as an ad hoc statement, not the function call itself). But what it does allow you to see, that Management Studio doesn't, is what a dog your UDF is being. You still have to join some dots, but there are fewer dots and they're closer together.

About Profiler

Finally, I would strongly recommend staying away from Profiler, unless it is to set up a server-side trace that you are going to run outside the scope of any UI tool. Using Profiler against a production system is almost certainly going to cause more problems than it will ever solve. If you want to get this information, please use a server-side trace or extended events, and be sure to filter very wisely. Even without profiler, a trace can impact your server, and retrieving showplans through extended events is not the most efficient thing in the world either.

share|improve this answer
    
Ah, I did not know about the call stack in the Pro version. That's exactly what I was looking for. It's good to know that exists. At this point, I don't think I can justify the price but I'll keep it in mind for future situations. Is there a reason why SQL Server doesn't provide I/O information for UDFs in STATISTICS IO? It's misleading to omit such essential information. –  Gabe Dec 11 '13 at 18:19
2  
@Gabe I don't know the reason, sorry. Maybe to make consultancy more lucrative? –  Aaron Bertrand Dec 11 '13 at 18:20
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.