SQL and the like

Dave Ballantyne's blog. Freelance SQL Server database designer and developer at Clear Sky SQL

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

statstime

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.

Comments

UDF Overhead ??? A simple example - SQL and the like said:

Pingback from  UDF Overhead ??? A simple example - SQL and the like

# November 24, 2009 10:19 AM

Tony Rogerson's ramblings on SQL Server said:

This Thursday 26th is the London SQL User Group and also the Edinburgh UG; in London we have Tony Rogerson

# November 24, 2009 12:58 PM

Dew Drop – November 25, 2009 | Alvin Ashcraft's Morning Dew said:

Pingback from  Dew Drop – November 25, 2009 | Alvin Ashcraft's Morning Dew

# November 25, 2009 4:45 PM

Don’t even believe SSMS when you think it’s telling the truth ??? Simple-Talk said:

Pingback from  Don’t even believe SSMS when you think it’s telling the truth ??? Simple-Talk

# June 26, 2012 10:51 AM