The Observer Effect In Action

I’ve put my hand up to to a quick 10 - 15 minute slot at the London user group, so I was getting my scripts together to do a presentation based on my UDF Overhead blog entry.  Naturally this being my first time talking, I wanted to make sure that I was accurate in terms of the statement timings and how I was interpreting the results.  After running the examples a few times I started to notice a discrepancy that I was wasn't expecting.

Executing the UDF with ‘SET STATISTICS TIME ON’ was visibly slower that without. In fact in profiler the duration time with stats time on was 2056ms, without it duration time was 233ms. This was the only difference. Testing on 2008 showed the same effect but to a lesser degree.

Heres the script im testing with.

Drop Function GetSalesCommission
go
Create Function GetSalesCommission(@SalesAmount money)
returns money
as
begin 
    Declare @CommissionAmount money
    Select @CommissionAmount = (@SalesAmount/100.0) * 5
    return @CommissionAmount
end
go

SET STATISTICS TIME ON
select sum(dbo.GetSalesCommission(SubTotal))  as Commission
  from Sales.SalesOrderHeader
go
SET STATISTICS TIME OFF
select sum(dbo.GetSalesCommission(SubTotal))  as Commission
  from Sales.SalesOrderHeader
go

And a screen shot of the profiler output

With this in mind,I’m certainly going to re-asses a few performance evaluation practices.  It doesn't completely negate my previous post on UDF Overheads but a large portion of the timings would seem to be related to this.