functions and dmvs.sql

/*****************************************************************************
Copyright (c) 2017 SQL Workbooks LLC
Terms of Use: https://sqlworkbooks.com/terms-of-service/
Contact: help@sqlworkbooks.com
*****************************************************************************/

IF DB_ID('functiontest') IS NOT NULL
BEGIN
    use master;

    ALTER DATABASE functiontest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE functiontest;
END

CREATE DATABASE functiontest
GO

USE functiontest
GO


ALTER DATABASE current SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE, 
    QUERY_CAPTURE_MODE = ALL, /* AUTO is often best!*/
    MAX_PLANS_PER_QUERY = 200,
    MAX_STORAGE_SIZE_MB = 2048,
    CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30),
    SIZE_BASED_CLEANUP_MODE = AUTO,
    DATA_FLUSH_INTERVAL_SECONDS = 15,
    INTERVAL_LENGTH_MINUTES = 30,
    WAIT_STATS_CAPTURE_MODE = ON /* 2017 gets wait stats! */
    );
GO

ALTER DATABASE current SET QUERY_STORE = ON
GO
ALTER DATABASE current SET QUERY_STORE CLEAR ALL;
GO


/***************************************************************************************************
CREATE SIX FUNCTIONS TO TEST
***************************************************************************************************/

CREATE FUNCTION dbo.scalar_function_does_data_access
    (@name sysname)
RETURNS SMALLINT
AS
BEGIN
    DECLARE @LEN SMALLINT

    SELECT @LEN = LEN(name)
    FROM sys.databases
    WHERE name=@name

    RETURN(@LEN)
END;
GO


CREATE FUNCTION dbo.scalar_function_does_not_do_data_access
    (@name sysname)
RETURNS SMALLINT
AS
BEGIN
    DECLARE @LEN SMALLINT

    SELECT @LEN = LEN(@name)
    RETURN(@LEN)
END;
GO


CREATE FUNCTION dbo.MSTFV_does_data_access
    (@name sysname)
RETURNS @tablethingy TABLE ( Len SMALLINT )
AS
BEGIN
    INSERT @tablethingy (Len)
    SELECT LEN(@name)
    from sys.databases
    where name = @name;

    RETURN
END;
GO

CREATE FUNCTION dbo.MSTVF_does_not_do_data_access
    (@name sysname)
RETURNS @tablethingy TABLE ( Len SMALLINT )
AS
BEGIN
    INSERT @tablethingy (Len)
    SELECT LEN(@name);

    RETURN
END;
GO



CREATE FUNCTION dbo.TFV_does_data_access
    (@name sysname)
RETURNS TABLE
AS
RETURN
    SELECT LEN(@name) as [Len]
    from sys.databases
    where name = @name;
GO

CREATE FUNCTION dbo.TVF_does_not_do_data_access
    (@name sysname)
RETURNS TABLE
AS
RETURN
    SELECT LEN(@name) as [Len];
GO


/***************************************************************************************************
Call the functions in some queries
***************************************************************************************************/

/* This is a bit nuclear. This script is intended for dedicated / isolated test instances only.*/
DBCC freeproccache
GO

select 
    dbo.scalar_function_does_data_access('functiontest')
GO 10

SELECT 
    dbo.scalar_function_does_not_do_data_access('functiontest')
GO 10

SELECT * 
FROM dbo.MSTFV_does_data_access('functiontest')
GO 10

SELECT * 
FROM dbo.MSTVF_does_not_do_data_access('functiontest')
GO 10

SELECT * 
FROM dbo.TFV_does_data_access('functiontest')
GO 10

SELECT * 
FROM dbo.TVF_does_not_do_data_access('functiontest')
GO 10


/***************************************************************************************************
What shoes up, where?
***************************************************************************************************/


/* sys.dm_exec_query_stats 
We have one line for every QUERY that was run (good!)
We only have CREATE FUNCTION entries for:
    CREATE FUNCTION dbo.scalar_function_does_data_access
    CREATE FUNCTION dbo.MSTFV_does_data_access  
    CREATE FUNCTION dbo.MSTVF_does_not_do_data_access


We do NOT have CREATE FUNCTION entries for these
    (this makes sense for the TVFs as these are single-statement TVFs that can be "inlined"):
    CREATE FUNCTION dbo.scalar_function_does_not_do_data_access
    CREATE FUNCTION dbo.TFV_does_data_access  
    CREATE FUNCTION dbo.TVF_does_not_do_data_access
*/
SELECT 
    sqltext.stmt,
    sqltext2.stmt,
    qs.execution_count AS [# executions],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,3))
		END AS [avg cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(qs.total_logical_reads / execution_count AS numeric(30,3))
	END AS [avg logical reads],
	qs.creation_time,
	qs.plan_generation_num,
    qp.query_plan AS [plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
CROSS APPLY (SELECT	SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
		((CASE qs.statement_end_offset  
			WHEN -1 THEN DATALENGTH(st.text)  
			ELSE qs.statement_end_offset  
			END
		- qs.statement_start_offset)/2) + 1) as stmt)  as sqltext
CROSS APPLY (SELECT	st.text as stmt)  as sqltext2
WHERE lower(sqltext.stmt) LIKE '%data_access%'
    or sqltext2.stmt LIKE '%data_access%'
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);
GO

/* sys.dm_exec_function_stats is 2016+ 
This only reports on scalar functions.
We get info on:
    dbo.scalar_function_does_not_do_data_access
    dbo.scalar_function_does_do_data_access*/
SELECT
    (SELECT st.[text]  FOR XML PATH(''),TYPE) as procedure_text,
    fs.execution_count AS [# executions],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(fs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,3))
		END AS [avg cpu sec],
	CASE WHEN execution_count = 0 THEN 0 ELSE
		CAST(fs.total_logical_reads / execution_count AS numeric(30,3))
	END AS [avg logical reads],
	fs.cached_time,
    qp.query_plan
FROM sys.dm_exec_function_stats AS fs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) AS qp
JOIN sys.objects as so on fs.object_id = so.object_id
WHERE so.name like '%data_access%'
    OPTION (RECOMPILE);
GO

/* Query Store is SQL Server 2016+ 
We get information for:
    scalar_function_does_data_access
    MSTFV_does_data_access
    MSTVF_does_not_do_data_access
*/
SELECT DISTINCT OBJECT_NAME(qsq.object_id) as obj_name, object_id
FROM sys.query_store_query as qsq
WHERE qsq.object_id <> 0;
GO