SET STATISTICS IO ON : Can you trust the result of this when using scalar functions?

One key thing we look at with the performance tuning on TSQL is, number of data pages read for retrieving data either from memory or disk. We always trust the SET STATISTICS IO ON and take decisions based on the result of it. But this does NOT always give the accurate value and it might mislead you for taking a wrong action on it.

Let's check this and see. The following code creates a scalar function that returns number of products related to the order accepting the SalesOrderId. This reads SalesOrderDetail table for getting the count.

USE AdventureWorks2014_New;
GO

CREATE FUNCTION dbo.GetNofProductsForOrder (@SalesOrderID int)
RETURNS smallint
AS
BEGIN

 DECLARE @NofProducts smallint
 SELECT @NofProducts = COUNT(*) 
 FROM Sales.SalesOrderDetail
 WHERE SalesOrderID = @SalesOrderID

 RETURN @NofProducts;
END;

Before using the function, let's see how many pages need to be read for getting data from both SalesOrderHeader and SalesOrderDetail tables. As you see, I use SET STATISTICS IO ON for this.

SET STATISTICS IO ON

SELECT SalesOrderID, SalesOrderNumber, SubTotal
FROM Sales.SalesOrderHeader;
SELECT SalesOrderID, SalesOrderDetailID, LineTotal
FROM Sales.SalesOrderDetail;

Now let's use the function. This code shows some order details along with number of products related to each order. Note the result related to SET STATISTICS IO ON.

SET STATISTICS IO ON

SELECT SalesOrderID, SalesOrderNumber, SubTotal
 , dbo.GetNofProductsForOrder(SalesOrderID) As NofProducts
FROM Sales.SalesOrderHeader;

Since we access both tables, number of pages to be read should definitely be more than 689 but it looks like this shows only pages related to SalesOrderHeader. How this can be happened? I do not have an answer now but will surely find the reason. Now the question is, how can we see the accurate value for this? Is it possible to see it?

Yea it is possible. It can be clearly seen with Profiler. If you analyze SQL:BatchCompleted and SQL:StmtCompleted, you can get the correct value. Here is the accurate value related to the query using Profiler.

Just be aware on this, this can happen statement like TOP too. In a way, it is always better to use Profiler for seeing pages read but still this can be used for small queries with no functions.