SQL Server – Optimization – Scaler Functions & I/O

Introduction

On the Application that I am working on, we use quite a bit of Scaler Functions

Problem Domain

We will use a simple DB Object constructs:

Table :- dbo.invoice

  • The dbo.invoice table has two columns (invoiceID, invoiceDate)
  • We will populate the table with 10,000 records

Function (Scaler) :- dbo.ufn_DayofWeek

  • The dbo.ufn_DayofWeek is a Scaler Function

Function (TVF) :- dbo.TVF_dayofweek

  • Thedbo.TVF_dayofweek is a Table Value Function

SQL Script

Create Table Objects and test queries:

Create Table


/*
	set statistics io on;
	set statistics time off;
*/

set nocount on
go

use [tempdb]
go

if object_id('dbo.invoice') is null
begin

	--truncate table dbo.invoice
	create table dbo.invoice
	(
	        [invoiceID] bigint not null identity(1,1)
	    ,   [invoiceDate] datetime not null
	)

end
go

 
 

Add Data

if exists 
	(
		select 1
		from   dbo.invoice
	)		
begin

	set noexec on

end
go

print 'Inserting into Invoice Table'
go

insert into dbo.invoice
	(
		[invoiceDate]
	)
	values
	(
		dateadd(day, rand() * 10000, getdate())
	)
go 10000

print 'Inserted into Invoice Table'
go

 
 

Create Scaler Function


set noexec off
go

--drop function dbo.ufn_dayofweek

if object_id('dbo.ufn_dayofweek') is not null
begin

	set noexec on
end
go

	CREATE FUNCTION dbo.ufn_DayofWeek(@date datetime)
	RETURNS sysname
	WITH EXECUTE AS CALLER
	AS
	BEGIN

		declare @dayofWeek sysname

		set @dayofWeek = datename(dw, @date)

		return (@dayofweek)

	end

go

 
 

Create Table Value Function

set noexec off
go

--drop function dbo.TVF_dayofweek

if object_id('dbo.TVF_dayofweek') is not null
begin
	set noexec on
end
go

CREATE FUNCTION dbo.TVF_dayofweek (@date datetime)
RETURNS TABLE
AS
RETURN 
(

     select datename(dw, @date) as [dayofweek]

);
GO

 

Benchmark – Compare Inline, Scaler, and Table Value Function

 

set noexec off
go

--use inline SQL
select 
	  datename(dw, tblInvoice.invoiceDate) as [dayofweek]
	, count(*) as cnt
from   [dbo].[Invoice] tblInvoice
group by
	datename(dw, tblInvoice.invoiceDate)

print ''

--use Scaler Function
select 
	  dbo.ufn_DayofWeek(tblInvoice.invoiceDate) as [dayofweek]
	, count(*) as cnt
from   [dbo].[Invoice] tblInvoice
group by
	dbo.ufn_DayofWeek(tblInvoice.invoiceDate)

print ''

--use Function (Table Value Function)
select tblDayofWeek.[dayofweek] as [dayofweek], count(*) as cnt
from   [dbo].[Invoice] tblInvoice 
         cross apply dbo.tvf_DayofWeek(tblInvoice.invoiceDate) tblDayofWeek
group by
	tblDayofWeek.[dayofweek]

Everything is good

Inline SQL

Query Plan

InLineNoFunction-QueryPlan

Statistics I/O

InLineNoFunction-StatisticsIO

Scaler Function

Query Plan

InLineNoFunction-QueryPlan

Statistics I/O

ScalerFunction-StatisticsIO

Function – Table Value Function

Query Plan

TableValueFunction-QueryPlan

Statistics I/O

TableValueFunction-StatisticsIO

Problem Underneath

When we run SQL Server Profiler …

Inline SQL

InLineNoFunction-SQLServerProfiler

Scaler Function 

ScalerFunction-SQLServerProfiler

Table Value Function

TableValueFunction-SQLServerProfiler

.. we see that the Scaler Functions are a bit more expensive; they silently make a lot of calls to the database.

Other Interesting Observations

As you run queries that referenced programmable objects (Stored Procedures \ Views \ Functions \ Triggers \etc),  you are probably tracking usage via sys.dm_exec_cached_plans.

You make code changes and you wipe out at your tally by clearing out your “Procedure Cache”:

   DBCC FREEPROCCACHE

But, in my simple test with “Table Value Functions”, the numbers do not always increase in linear nor predictable fashion.

Microsoft has an Engineer arc and so don’t sweat it too much:

sys.dm_exec_cached_plans (Transact-SQL)
http://technet.microsoft.com/en-us/library/ms187404.aspx

  • refcounts
    Number of cache objects that are referencing this cache object. Refcounts must be at least 1 for an entry to be in the cache.

  • usecounts
    Number of times the cache object has been looked up. Not incremented when parameterized queries find a plan in the cache. Can be incremented multiple times when using showplan.

Refcounts which is the number of times the programmable object is referenced within the same SQL Statement or via different statements worked as expected.

But,   mileage with “usecounts” might very will vary.

 

Inspiration

I was seeing the same problem and the idea that I was seeing right was solidified by reading http://www.sqlperformance.com/2012/10/t-sql-queries/beware_statistics_io. The blog was written by Aaron Bertrand and distills his and Steve Wright’s experiences.

Conclusion

So please keep an eye on those Scaler Functions.

It is sometimes tough to gauge overall impact, as the tooling is not always supportive of what you think you see or will like to see.

References

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s