On the Application that I am working on, we use quite a bit of Scaler Functions
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
Create Table Objects and test queries:
/* 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
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
Function – Table Value Function
When we run SQL Server Profiler …
Table Value Function
.. 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”:
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:
Number of cache objects that are referencing this cache object. Refcounts must be at least 1 for an entry to be in the cache.
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.
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.
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.
- Beware Statistics I/O
- IO Stats – What are you missing