Now, very soon after that went live I received a comment from Dave Ballantyne (Blog | Twitter) mentioning that having SET STATISTICS TIME ON affects the time TSQL takes to execute. Really? That sounds dumb. Not Dave's comment, but the fact that the turning on of a setting to collect statistics actually affect the statistics you are collecting. That's a bit like timing the 100m by making the runners carry a guy with a stopwatch on their backs. Dave actually blogged about it here - The Observer Effect In Action and after reading that I started thinking about how much effect the TIME ON setting was having.

Now you can wrap a piece of code with a few lines of other code and easily come up with a way of measuring the time taken. Declare a variable to hold the time, set it to GETDATE(), run your script, subtract the time now from the variable ...

So, if I wrap the execution of the UDF and TVF from my previous post in this code and execute it twice, once with TIME ON and once with TIME OFF I'll see the difference that this causes.

The scalar UDF wrapped in the Start/Stop timing code twice - once for TIME ON and once for TIME OFF 

So running this I thought I might see a few ms difference as SQL Server takes a CPU cycle or two to log the time taken.

WRONG!

Over 2s is added to the execution time of the Scalar UDF when you have STATISTICS TIME ON.

OK, what about the TVF? That only took 3/10ths of a second in my last test, how would this be affected? I re-wrote the script to use the TVF and return the times as above and the results were pretty variable, sometimes TIME ON was quicker, sometimes TIME OFF was quicker, only ever by a few ms though. This was intriguing, and annoying. SQL Server was skewing my results without so much as a mention in Books OnLine that it might happen.

Having seen the TVF results vary I set about running these queries a lot of times and seeing how much things did actually vary. This is the resulting code:

USE [adventureworks]
GO
IF OBJECT_ID('TimeOnOffResults') > 0
   BEGIN
   RAISERROR('table exists',0,0,1) WITH NOWAIT
   END
ELSE
   BEGIN
      --DROP TABLE TimeOnOffResults 
   CREATE TABLE TimeOnOffResults
      (
               RunID INT IDENTITY ,
               TimeOnOff CHAR(3) ,
               TVF_results INT ,
               Scalar_Results INT
      )
   END
GO
DECLARE @x INT
   SET @x = 1
WHILE @x <200
   BEGIN
      SET STATISTICS TIME OFF
      SET ROWCOUNT 1000000
   DECLARE @Now DATETIME
   DECLARE @TVF INT
   DECLARE @Scalar INT
   DECLARE @TimeON_Off CHAR(3)
   SELECT @TimeON_Off = 'Off'
   SELECT @Now = GETDATE()
   DECLARE @d1 DATETIME
   DECLARE @d2 DATETIME
   DECLARE @r1 INT
   DECLARE @r2 INT
   SELECT @d1 = [dbo].[fn_01]() ,
      @r1 = [dbo].[Numbers].[N]
      FROM [dbo].[Numbers]
   SELECT @Scalar = DATEDIFF(ms, @now, GETDATE())
   SELECT @Now = GETDATE()
   SELECT @r2 = [n].[N] ,
      @d2 = [f].[FinancialYearStartDate]
      FROM [dbo].[Numbers] AS n
            CROSS APPLY fn_02() AS f
   SELECT @TVF = DATEDIFF(ms, @now, GETDATE())
   INSERT INTO [dbo].[TimeOnOffResults]
      ( [TimeOnOff] ,
               [TVF_results] ,
               [Scalar_Results]
      )
         VALUES ( @TimeON_Off , -- TimeOnOff - char(3) 
         @TVF , -- TVF_results - smalldatetime 
         @Scalar-- Scalar_Results - smalldatetime 
      )
   SET STATISTICS TIME ON
   SELECT @TimeON_Off = 'On'
   SELECT @Now = GETDATE()
      SET ROWCOUNT 1000000
   SELECT @d1 = [dbo].[fn_01]() ,
      @r1 = [dbo].[Numbers].[N]
      FROM [dbo].[Numbers]
   SELECT @Scalar = DATEDIFF(ms, @now, GETDATE())
   SELECT @Now = GETDATE()
   SELECT @r2 = [n].[N] ,
      @d2 = [f].[FinancialYearStartDate]
      FROM [dbo].[Numbers] AS n
            CROSS APPLY fn_02() AS f
   SELECT @TVF = DATEDIFF(ms, @now, GETDATE())
   INSERT INTO [dbo].[TimeOnOffResults]
      ( [TimeOnOff] ,
               [TVF_results] ,
               [Scalar_Results]
      )
         VALUES ( @TimeON_Off , -- TimeOnOff - char(3) 
         @TVF , -- TVF_results - smalldatetime 
         @Scalar-- Scalar_Results - smalldatetime 
      )
   SET @x = @x +1
   RAISERROR(@x,0,0,1) WITH nowait
   END

I left this run for almost an hour on my home machine and the results are quite interesting

Of the 199 executions the TVF averaged 8ms faster execution with TIME ON whereas the Scalar UDF over the same number of executions was on average 2.6s faster with TIME OFF.

So what does this mean?
To be honest, other than advising people to not use SET STATISTICS TIME as an absolute measure of their query execution times, I don't know. I can find no details online about why this is the case or any way to mitigate it other than, as Dave suggested, using variables in the way I have above. It seems an arbitrary 'weight' that comes along when Scalar UDF's are involved, it does even scale with the number of rows returned.