February 6, 2014 at 7:01 am
This is to learn how data is processed.
I do realise that this may change over versions or even that the optimiser might choose a different route if only one row is added to a table.
To see how efficient code is working I want to count how many times a function is accessed. So am thinking about a counter build in the function which counts and registers this in a table when it is accessed.
This is only for testing purposes and for learning.
Question1, does the number of accesses to a function change if I change the function with a build in counter ?
Any other suggestions?
Ben
Background, at the moment during a cumbersome query a function is resposible for a large part of the time which is spend on the query. Rewriting the query might help, but we would like to get some grips on the situation.
February 6, 2014 at 7:07 am
Is it function or stored procedure? If you are talking about function, have you tried to "build in counter ". I just wonder how are you going to do so, have you found a way to modify outside table data within UDF?
February 6, 2014 at 7:46 am
ben.brugman (2/6/2014)
To see how efficient code is working I want to count how many times a function is accessed.
Depends on the execution plan. The rule that functions cannot have side effects (make any permanent changes to the database) is there because the optimiser can and will choose to execute the function different numbers of times based on what it calculates is the optimal and cheapest option/
So am thinking about a counter build in the function which counts and registers this in a table when it is accessed.
Won't work, because you cannot modify data in a permanent table from within a function. Maybe a trace (profiler or server-side) on SP:completed?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2014 at 7:54 am
If you want to see how many times any sql executed with calling this functions, Gail's suggestion with profile might help.
But it has nothing to do with "how many times function is accessed".
Let say you have a scalar UDF which participates in some select statement. What will you count as "function accessed" in this case and how?
February 6, 2014 at 7:56 am
GilaMonster (2/6/2014)
ben.brugman (2/6/2014)
To see how efficient code is working I want to count how many times a function is accessed.Depends on the execution plan. The rule that functions cannot have side effects (make any permanent changes to the database) is there because the optimiser can and will choose to execute the function different numbers of times based on what it calculates is the optimal and cheapest option/
So am thinking about a counter build in the function which counts and registers this in a table when it is accessed.
Won't work, because you cannot modify data in a permanent table from within a function. Maybe a trace (profiler or server-side) on SP:completed?
Never tried it.:-) (Because you should not use side effects).
(Some procedural languages allow side effects in functions)
So didn't realise that this results in:
Msg 443, Level 16, State 15, Procedure Replace2, Line 14
Invalid use of a side-effecting operator 'UPDATE' within a function.
Based on timing; the changes indicate that the number of times the function was called was less after a query change.
Thanks,
Ben
February 6, 2014 at 8:02 am
Eugene Elutin (2/6/2014)
If you want to see how many times any sql executed with calling this functions, Gail's suggestion with profile might help.But it has nothing to do with "how many times function is accessed".
Let say you have a scalar UDF which participates in some select statement. What will you count as "function accessed" in this case and how?
As allready discussed a counter can not be build in.
What would count is everytime the countline *) is passed.
Not an option so for now we have used timing measurements to 'improve' the statement.
Ben
*)
The countline (resulted in the show errror).
update testdb.dbo.teller set tel = tel +1
February 6, 2014 at 8:15 am
ben.brugman (2/6/2014)
Eugene Elutin (2/6/2014)
If you want to see how many times any sql executed with calling this functions, Gail's suggestion with profile might help.But it has nothing to do with "how many times function is accessed".
Let say you have a scalar UDF which participates in some select statement. What will you count as "function accessed" in this case and how?
As allready discussed a counter can not be build in.
What would count is everytime the countline *) is passed.
Not an option so for now we have used timing measurements to 'improve' the statement.
Ben
*)
The countline (resulted in the show errror).
update testdb.dbo.teller set tel = tel +1
Thank you Ben, I knew it very well, check my first comment to your thread...
I just wanted you to find it by yourself.
February 6, 2014 at 8:36 am
You can try this code that may get some execution counts for the function you are investigating.
/* set the code text to be searched on with the variable @CodeName
*/
DECLARE @CodeName VARCHAR(128) = '%fn_%';
SELECT TOP 10
total_elapsed_time/1000.0 as total_elapsed_time
,execution_count
,(total_elapsed_time/execution_count)/1000.0 AS [avg_elapsed_time_ms]
,last_elapsed_time/1000.0 as last_elapsed_time
,total_logical_reads/execution_count AS [avg_logical_reads]
,st.Query
,qp.query_plan
,qs.plan_handle
,cp.objtype
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
OUTER APPLY sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st1
CROSS APPLY (
SELECT
REPLACE
(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
CONVERT
(
NVARCHAR(MAX),
N'--' + NCHAR(13) + NCHAR(10) + ist.text + NCHAR(13) + NCHAR(10) + N'--' COLLATE Latin1_General_Bin2
)
,NCHAR(31),N'?'),NCHAR(30),N'?'),NCHAR(29),N'?'),NCHAR(28),N'?'),NCHAR(27),N'?'),NCHAR(26),N'?')
,NCHAR(25),N'?'),NCHAR(24),N'?'),NCHAR(23),N'?'),NCHAR(22),N'?')
,NCHAR(21),N'?'),NCHAR(20),N'?'),NCHAR(19),N'?'),NCHAR(18),N'?')
,NCHAR(17),N'?'),NCHAR(16),N'?'),NCHAR(15),N'?'),NCHAR(14),N'?')
,NCHAR(12),N'?'),NCHAR(11),N'?'),NCHAR(8),N'?'),NCHAR(7),N'?')
,NCHAR(6),N'?'),NCHAR(5),N'?'),NCHAR(4),N'?'),NCHAR(3),N'?'),NCHAR(2),N'?'),NCHAR(1),N'?')
,NCHAR(0),N''
) AS [processing-instruction(query)]
FROM sys.dm_exec_sql_text(qs.sql_handle) AS ist
FOR XML
PATH(''),
TYPE
) AS st(Query)
WHERE qs.plan_handle = cp.plan_handle
AND st1.text like @CodeName
ORDER BY last_elapsed_time DESC;
That will also return the execution plan, but will get some execution counts - possibly.
You can read more about that script here - http://www.sqlservercentral.com/articles/Execution+Plans/103484/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 6, 2014 at 9:14 am
SQLRNNR (2/6/2014)
You can try this code that may get some execution counts for the function you are investigating.
Thanks for the script, in the script I noticed a number of nested replaces and see some employ for the replace2 function.
Example:
SET @UU = dbo.Replace2(@uu, 'aaa|bb|c|d|e|f|g|h','11|2|3|4444|5|6|7|8')
'aaa' gets replaced by '11'
'bb' gets replaced by '2'
etc.
Parts can be replaced more than once.
print Replace2('1234567890123456789012345678901234567890 A 123', '0|1|2|3|4|5|6|7|8|9|44444444|4444|44|44|4', '4|4|4|4|4|4|4|4|4|4|4|4|4|4|42')
Makes 42 of numbers, the line wil print: "42 A 42".
(Number 44 is repeated twice to adress number with 3 positions).
(Example with spaces did not present itself very wel, but a number of spaces can be replaced by a single space in a similar way).
Dwain.c did the largest contribution to this function.
Thanks for the code,
Ben.
-- =============================================
-- Author:ben brugman / dwain.c (sqlservercentral)
-- Create date: 20130118
-- Description:Does replace @in a str@ing
-- =============================================
-- drop function replace2
CREATE FUNCTION Replace2
(
-- Add the parameters for the function here
@in varchar(8000),
@ff varchar(8000),
@RR varchar(8000)
)
RETURNS varchar(8000)
AS
BEGin
-- Declare the return variable here
DECLARE @out varchar(8000)
-- Add the T-SQL statements to compute the return value here
-- SELECT @out = @in
-- Special_replace2 ain't so hard:
DECLARE @MyString VARCHAR(4000) = @in
-- transform is the table which hold a row for each replacement.
-- rCTEReplace is used in a 'loop' to process use each transform element once.
-- Starting with item number one
;WITH
Replacements (n,a,b) AS (SELECT 1, @ff,@rr),
Transform (ItemNumber, a, b) AS
(
SELECT ItemNumber, MAX(a), MAX(b)
FROM (
SELECT n, ItemNumber, a=a.Item, b=NULL
FROM Replacements
CROSS APPLY DelimitedSplit8K(a, '|') a
UNION ALL
SELECT n, ItemNumber, NULL, Item
FROM Replacements
CROSS APPLY DelimitedSplit8K(b, '|')) a
GROUP BY n, ItemNumber
),
rCTEReplace (n, s, r) AS (
SELECT n=1, MyString, REPLACE(MyString, a, b)
FROM (SELECT @MyString) a(MyString)
JOIN Transform ON ItemNumber = 1
UNION ALL
SELECT n+1, s, REPLACE(r, a, b)
FROM rCTEReplace
JOIN Transform ON ItemNumber = n+1
)
SELECT @out = r
FROM rCTEReplace
WHERE n = (SELECT COUNT(*) FROM Transform)
-- Return the result of the function
RETURN @out
END
February 6, 2014 at 9:39 am
ben.brugman (2/6/2014)
SQLRNNR (2/6/2014)
You can try this code that may get some execution counts for the function you are investigating.Thanks for the script, in the script I noticed a number of nested replaces and see some employ for the replace2 function.
Thanks, I will take a look at it.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply