Different average duration for update with primary key
-
Hi , we running query bellow
UPDATE MyOrders
SET col1 = @P11,
col2 = @P12,
WHERE pk_col0 = @P3 -- cluster index
AND (col3 IS NULL
OR col4=0)
execution plan show 100% cluster index seek
using query bellow I getting query stat by hour
SELECT
[qsq].[query_id],
[qsp].[plan_id],
CONVERT(varchar(20),[rsi].[start_time],120) as StartHourUTC,
[rs].[count_executions],
[rs].[count_executions] * round([rs].[avg_cpu_time]/1000,2) as total_cpu_time_ms_sec,
[rs].[count_executions] * round([rs].[avg_duration]/1000,2) as total_duration_ms_sec,
round([rs].[avg_duration]/1000,2)as avg_duration_ms_sec,
round([rs].[avg_cpu_time]/1000,2)as avg_cpu_time_ms_sec,
round([rs].[avg_logical_io_reads],0)as avg_logical_io_reads,
[rs].avg_rowcount,
[rs].max_rowcount
FROM [sys].[query_store_query] [qsq]
JOIN [sys].[query_store_query_text] [qst]
ON [qsq].[query_text_id] = [qst].[query_text_id]
JOIN [sys].[query_store_plan] [qsp]
ON [qsq].[query_id] = [qsp].[query_id]
JOIN [sys].[query_store_runtime_stats] [rs]
ON [qsp].[plan_id] = [rs].[plan_id]
JOIN [sys].[query_store_runtime_stats_interval] [rsi]
ON [rs].[runtime_stats_interval_id] = [rsi].[runtime_stats_interval_id]
query WHERE
[qsq].[query_id] = 4991176 -- get id from query store
and
[rsi].[start_time] >= '2020-11-01 00:00:00.0000000 +00:00'
when I look at result (see image) I noticed sometime for same number of execution in different time average duration change a lot
Question: what factors could affect it ?
Database located in Azure SQL with DTU 4000
Thank you
-
It's impossible to say with certainty but perhaps there's another process which causes locking?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
from
avg_cpu_time - Average CPU time for the query plan within the aggregation interval (reported in microseconds).
Does avg_cpu_time count only CPU time without counting any wait time for blocking ?
avg_duration - Average duration for the query plan within the aggregation interval (reported in microseconds)
Is more accurate to use avg_duration for benchmark because it include all blocking and I/O wait time ?
-
Check for a job which running every 6 hours around 3 and 9 o'clock.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply