Different average duration for update with primary key

Question

Post reply

Different average duration for update with primary key

ebooklub

Hall of Fame

Points: 3905
More actions
November 13, 2020 at 10:55 pm

#3807706

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

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

ebooklub Hall of Fame Points: 3905 More actions · Answer 2

from

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-runtime-stats-transact-sql?view=sql-server-ver15

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 ?

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Check for a job which running every 6 hours around 3 and 9 o'clock.

_____________
Code for TallyGenerator