March 1, 2023 at 2:19 pm
I have a poorly performing proc, that is called heavily, and is taking around 4 seconds to complete. I have tuned the worst performing statement, and it appears to be a good improvement. HOWEVER, the elapsed_time from sys.dm_exec_query_stats is degraded.
The question is whether my efforts have been successful or not? Any thoughts would be appreciated.
The statement start with "WITH Object3 AS ( ..." is the original statement
The statement start with "WITH Object17 AS ( ..." is the modified statement
Selected stats from sys.dm_exec_query_stats
SELECT src.*, [%Improvement] = CAST((src.[Statement_Original] - src.[Statement_Modified]) *100.0 / src.[Statement_Original] AS decimal(6,2))
FROM (
VALUES ( 'execution_count', 2, 2 )
, ( 'total_rows', 2, 2 )
, ( 'last_rows', 1, 1 )
, ( 'min_rows', 1, 1 )
, ( 'max_rows', 1, 1 )
, ( 'total_worker_time', 138583, 4442 )
, ( 'last_worker_time', 72001, 2215 )
, ( 'min_worker_time', 66582, 2215 )
, ( 'max_worker_time', 72001, 2226 )
, ( 'total_logical_reads', 7490, 42 )
, ( 'last_logical_reads', 3745, 21 )
, ( 'min_logical_reads', 3745, 21 )
, ( 'max_logical_reads', 3745, 21 )
, ( 'total_elapsed_time', 813408, 1153487 )
, ( 'last_elapsed_time', 407415, 575528 )
, ( 'min_elapsed_time', 405993, 575528 )
, ( 'max_elapsed_time', 407415, 577959 )
, ( 'total_grant_kb', 127440, 0 )
, ( 'last_grant_kb', 63720, 0 )
, ( 'min_grant_kb', 63720, 0 )
, ( 'max_grant_kb', 63720, 0 )
, ( 'total_used_grant_kb', 3664, 0 )
, ( 'last_used_grant_kb', 1832, 0 )
, ( 'min_used_grant_kb', 1832, 0 )
, ( 'max_used_grant_kb', 1832, 0 )
, ( 'total_ideal_grant_kb', 127440, 0 )
, ( 'last_ideal_grant_kb', 63720, 0 )
, ( 'min_ideal_grant_kb', 63720, 0 )
, ( 'max_ideal_grant_kb', 63720, 0 )
) AS src([stat_name], [Statement_Original], [Statement_Modified])
March 1, 2023 at 3:18 pm
Are
FUNCTION1,FUNCTION2, etc, all user-defined scalar functions?
No, there are no functions in the code. Those are simply SQL Sentry Plan Explorer getting creative with its anonymisation of the code.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply