Microsoft – SQL Server – Transact SQL – Functions – Scalar Functions – Cost Analysis

Background

Here is a bitty post that talks about Scalar Functions and one attempt to cost them.

DDL – Create Table Objects

 

set noexec off
go

use [DBLAB]
go

if schema_id('dblab') is null
begin

    exec('create schema [dblab] ');

end
go

/*
    Create Scaler Function - [dblab].[ufn_getPersonFullName]
*/
if object_id('[dblab].[ufn_getPersonFullName]') is not null
begin
    set noexec on
end
go

create function [dblab].[ufn_getPersonFullName]
(
      @firstname nvarchar(60)
    , @lastname  nvarchar(60)
)
returns nvarchar(130)
begin

    return @firstname + @lastname
end
go

set noexec off
go


/*
    Create Scaler Function - [dblab].[ufn_getPersonFullName_persisted]
*/
if object_id('[dblab].[ufn_getPersonFullName_persisted]') is not null
begin
    set noexec on
end
go

create function [dblab].[ufn_getPersonFullName_persisted]
(
      @firstname nvarchar(60)
    , @lastname  nvarchar(60)
)
returns nvarchar(130)
with schemabinding
begin

    return @firstname + @lastname
end
go

set noexec off
go


/*
    Create Table Value Function - [dblab].[TVF_PersonFullName]
*/
if object_id('[dblab].[TVF_PersonFullName]') is not null
begin
    set noexec on
end
go

create function [dblab].[TVF_PersonFullName]
(
      @firstname nvarchar(60)
    , @lastname  nvarchar(60)
)
returns TABLE AS
return
        (   
            select @firstname + @lastname
                    as [fullname]
        )
;
go

set noexec off
go

/*
    Create Scaler Function - [dblab].[ufn_getPersonAge]
*/
if object_id('[dblab].[ufn_getPersonAge]') is not null
begin
    set noexec on
end
go

create function [dblab].[ufn_getPersonAge]
(
      @dateofBirth datetime
)
returns smallint
begin

    return datediff(year, @dateofBirth, getdate())
end
go

set noexec off
go


/*
    Create Table
        -- drop table [dblab].[person]
*/
if object_id('[dblab].[person]') is not null
begin
    set noexec on
end
go

create table [dblab].[person]
(
    [Person_ID] bigint not null
        identity(1,1)

    , [firstname]   nvarchar(60) not null
    , [lastname]    nvarchar(60) not null
    , [dateofBirth]  datetime null

    , [fullname] as [dblab].ufn_getPersonFullName([firstname], [lastname] )
        
    , [age] as [dblab].ufn_getPersonAge([dateofBirth])

    , [fullname_persisted] 
        as [dblab].ufn_getPersonFullName_persisted([firstname], [lastname] ) PERSISTED
                            
)
go


set noexec off
go



 

DML – Insert Data

 

set nocount on
go

use [DBLAB]
go

/* 
    Remove datra
*/
truncate table [dblab].[person];

/*
    Insert Data - Execute Batch 
*/
begin tran

    insert into [dblab].[person] ([firstname], [lastname], [dateofBirth]) values ('Sam', 'Smith', '1/1/1972');
    insert into [dblab].[person] ([firstname], [lastname], [dateofBirth]) values ('Tanya', 'Jacobs', '11/18/1983');
    insert into [dblab].[person] ([firstname], [lastname], [dateofBirth]) values ('Sarai', 'Peterson', '4/16/1976');
    insert into [dblab].[person] ([firstname], [lastname], [dateofBirth]) values ('Paul', 'Grant', '8/19/1987');

commit tran

 

DML – Fetch Data

 

 

Query Explanation

 

Query Explanation
1st Query Does not access Scalar Function
 2nd Query Access Scalar Function through computed column (Age)
3rd Query Access Scalar Function through computed column (Fullname)
 4th query Access Scaler function, but as column (fullname) is persisted we do not pay price during retrieval, but during inserts/updates
5th Query  Accesses Table Value Function

 

 

Query – SQL

 

set nocount on
go

use [DBLAB]
go

/*
    Get data : Exclude columns derived from Scaler Function
*/
select 
          tblPerson.[Person_ID]
        , tblPerson.[firstname]
        , tblPerson.[lastname]
        , [dateofBirth]
from  [dblab].[person] tblPerson;


/*
    Get data : Include Age -  Age is derived from Scaler Function
*/
select 
          tblPerson.[Person_ID]
        , tblPerson.[firstname]
        , tblPerson.[lastname]
        , [dateofBirth]
        , [age]
from  [dblab].[person] tblPerson;

/*
    Get data : Include Age -  Fullname is derived from Scaler Function
*/
select  
          tblPerson.[Person_ID]
        , tblPerson.[firstname]
        , tblPerson.[lastname]
        , [fullname]
from  [dblab].[person] tblPerson;



/*
    Get data : Include Fullname_persisted -  Fullname_person is derived from "persisted" Scaler Function
*/
select  
          tblPerson.[Person_ID]
        , tblPerson.[firstname]
        , tblPerson.[lastname]
        , [fullname_persisted]
from  [dblab].[person] tblPerson;


/*
    Get data : Include Age -  Fullname_person is from TVF Function
*/
select 
          tblPerson.[Person_ID]
        , tblPerson.[firstname]
        , tblPerson.[lastname]
        , tblFullName.fullname

from  [dblab].[person] tblPerson

            cross apply [dblab].[TVF_PersonFullName](tblPerson.[firstname], tblPerson.[lastname] )                             tblFullName

 

Query Cost

QueryCost

 

 

Diagnostic – Plan Cache – Find Scalar Functions

 

Query Result

 

Query Explanation
DatabaseName Name of Database
Plan_handle Query Plan Handle
PhysicalOp Physical Operator
LogicalOp Logical Operator
UseCounts Number of times query has executed
Parallel Was query executed in parallel
Estimated Execution Mode Row
Estimate Rows Number of Rows that is estimated will be returned
 AvgRowSize Average Row Size for each data row
 Estimated I/O Always 0; which is acutely misleading
 text Query Text
 Query_Plan Query Plan

 

 

Query

 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan')

select 
          databaseName	
        , tblIn.plan_handle
        , tblIn.PhysicalOp        
        , tblIn.LogicalOp                
        , tblIn.useCounts
        , tblIn.Parallel
        , tblIn.EstimatedExecutionMode
        , tblIn.EstimateRows
        , tblIn.AvgRowSize
        , tblIn.EstimateIO

        , tblSQLText.text
        , tblQueryPlan.query_plan

from
(

  SELECT 

          cp.plan_handle

        , (cast(cp.usecounts as bigint)) as useCounts

        , (operators.value('@PhysicalOp','nvarchar(50)'))
               as PhysicalOp

        , (operators.value('@LogicalOp','nvarchar(50)'))
               as LogicalOp

        , (operators.value('@EstimateRows','numeric(10,2)'))
               as EstimateRows

        , (operators.value('@EstimateIO','numeric(10,2)'))
               as EstimateIO

        , (operators.value('@AvgRowSize','numeric(10,2)'))
               as AvgRowSize

        , (operators.value('@Parallel','nvarchar(50)'))
               as Parallel

        , (operators.value('@EstimatedExecutionMode','nvarchar(50)'))
               as EstimatedExecutionMode

		, db_name(qp.dbid) as databaseName

   FROM sys.dm_exec_cached_plans cp

   CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

   CROSS APPLY query_plan.nodes('//RelOp') rel(operators)
   
  

    where 
    (

      ---Look for Scaler Functions
      (

        (
		  
               (operators.value('@PhysicalOp','nvarchar(50)')  like '%Scalar%')
            or (operators.value('@LogicalOp','nvarchar(50)')  like '%Scalar%')

         )
      
      )

   

    )        


) tblIn

   CROSS APPLY sys.dm_exec_query_plan(tblIn.plan_handle) tblQueryPlan

   CROSS APPLY sys.dm_exec_sql_text(tblIn.[plan_handle]) tblSQLText

where
        (
        
                (tblSQLText.text not like '%XmlNamespaces%')
            and (tblSQLText.text not like '%XmlSchemaNamespace%')

        ) 

 order by 
 
          ( 
                (tblIn.EstimateRows * tblIn.AvgRowSize)
                     * tblIn.useCounts

          )  desc

 

Listening To

 

Listening to Wale’s Letter…
https://www.youtube.com/watch?v=Pa9uYe6QAt8

 

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