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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy