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

 

Technical: Microsoft – SQL Server – Referential Integrity – Review Query Plan

Technical: Microsoft – SQL Server – Referential Integrity – Review Query Plan

 

Background

Last week while talking to someone, we touched on SQL Server Performance.  Mostly how is this done, why will one use one technique over the other, etc.

One of the many areas we covered in how is Referential Integrity enforced.

In this posting, I will try to firm up my understanding by working on a straight forward two table example.

Table Design

Here is our table design

TableDesign

DDL Statements

Here is our DDL Statement

 



set noexec off
go

use [DBLab]
go

--drop TABLE [dbo].[order]
--drop table dbo.Customer

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

CREATE TABLE [dbo].[customer]
(
	[Customer_ID] [bigint] NOT NULL,
	[CustomerName] [nvarchar](255) NOT NULL,
	[inceptionDate] [datetime] NOT NULL,
	[expiryDate] [datetime] NULL,
	[active] [bit] NOT NULL  default 1,
	[dateAdded] [datetime] NOT NULL  default getdate(),
	[addedBy] [sysname] NOT NULL default SYSTEM_USER,

		CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
		(
			[Customer_ID] ASC
		)
)
go

set noexec off
go

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

CREATE TABLE [dbo].[order]
(
	[Order_ID] [bigint] not null identity(1,1),
	[Customer_ID] [bigint] NOT NULL,
	[dateAdded] [datetime] NOT NULL default getdate(),
        [addedBy]   [sysname]  NOT NULL default SYSTEM_USER,

		, CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED 
		(
			[Order_ID] ASC
		)

		, CONSTRAINT [FK_Customer] FOREIGN KEY
		(
			[Customer_ID]
		)
		references [dbo].[customer]
		(
			[Customer_ID]
		)
)
go


set noexec off
go




 

 

DDL Statements

 

Cleanup remnant data


delete from [dbo].[order];
delete from [dbo].[customer];
go

 

 

Prepare Referenced table

DML



insert into [dbo].[customer]
(
 [Customer_ID]
 , [CustomerName]
 , [inceptionDate]
 , [expiryDate]
 , [active]
 , [dateAdded]
 , [addedBy]
)
values
(
 1
 , 'Kirkland'
 , getdate()
 , null
 , 1
 , getdate()
 , SYSTEM_USER

)
go

insert into [dbo].[customer]
(
   [Customer_ID]
 , [CustomerName]
 , [inceptionDate]
 , [expiryDate]
 , [active]
 , [dateAdded]
 , [addedBy]
)
values
(
   2
 , 'Kirkland dup data'
 , getdate()
 , null
 , 1
 , getdate()
 , SYSTEM_USER
)
go

 

ScreenShot:

InsertIntoCustomer

 

 

Review Referencing table

DML

insert into [dbo].[order]
(
 [Customer_ID]
 , [dateAdded]
 , [addedBy]
)
select
   1
 , getdate()
 , SYSTEM_USER
union all
select
   2
 , getdate()
 , SYSTEM_USER

 

ScreenShot:

 

InsertIntoDependentTable

 

Explanation:

  • As we have a clustered index on the referencing table, the clustered index insert happens
  • The system then initiates a Nested-Loops — Left semi join against our referenced table looking to see whether we have existing “parent” records
  • Left semi join is a system join and it can not not be explicitly requested by User SQL.  It basically means look for your first match, once you find it move along
  • The gate-keeper is our Assert Statement which in this case says that flag occurrences where matching records are not found in the Referenced Table

 

Review Referenced table

DML

delete from dbo.customer where customer_ID = 2;

 

ScreenShot:

DeleteFromReferencedTable_v2

Explanation:

  • The Delete is represented as a Clustered index delete
  • This time the system initiates a Nested-Loops — Left semi join against our referencing table
  • It tries to find records in our Referencing tables that have matching values to the records we are deleting
  • If found, the Assert statement raises an exception

 

 

Conclusion

SQL Server Engine is quite optimistic in nature, all of the work is conducted and recorded in the Transaction Log.

Again, I will go out on a limb here and say this:

If problem happens, the records are marked disposable in the Transaction Log and not carried over to the actual database files.

How do I know all this without following our transactions in the Transaction Log…

To prove/disprove is for another posting.

 

Reference

 

 

 

 

 

 

 

Microsoft – SQL Server – Performance Tuning – Query Plans – Find Operators – “Sequence Project”

Introduction

Here is a quick follow-up to “Technical: Microsoft – SQL Server – Indexing Requirements – Comparing Windowing Functions against Correlated Join” (“https://danieladeniji.wordpress.com/2013/11/01/technical-microsoft-sql-server-windowing-function-indexing-requirements/“).

If you find your team using Windows Functions and you want to know how actively they are being used and also you will like to “cost” them a bit, please try the following Query.

Query – Utilizing Dynamic Management View (DMV)



WITH XMLNAMESPACES 
(default 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT top 30
				st.text as sqlText,
				pl.query_plan,
				ps.execution_count,
				ps.last_execution_time,
				ps.last_elapsed_time,
				ps.last_logical_reads,
				ps.last_logical_writes
FROM sys.dm_exec_query_stats ps with (NOLOCK)
    Cross Apply sys.dm_exec_sql_text(ps.sql_handle) st
    Cross Apply sys.dm_exec_query_plan(ps.plan_handle) pl
where   pl.[query_plan].exist('data(//RelOp[@PhysicalOp="Sequence Project"])') = 1
and     pl.[query_plan].exist('data(//RelOp[@PhysicalOp="Sort"])') = 1

Order By (ps.last_logical_reads + ps.last_logical_writes) desc

OPTION (RECOMPILE);

Explanation:

  • Checks Query Plan and digs for Sequence Projects that suffer the curse of Sorts.

References

References – SQLTeam.com

References – Microsoft Customer Service and Support (CSS) SQL Support

References – SQL Server Engine Team

References – Simple-Talk (Fabiano Amorim)

References – ScaryDBA

References – Technet.Microsoft.com // Gallery // Script Center