Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

how to measure the response time of a query using whoisactive

Hi,

I am now given a task to calculate the mean value of the total execution time of a query in SQL server 2016 during low peak hour, normal working hour and peak hour.

It seems only use Whoisactive. e..g:

User generated imagecan't measure it by using the first volume:
User generated imageas this value is ONLY the total execution time before and up to the time I run whoisactive!
if I run the above again the query will gone and can't see the: User generated imageanymore.

any idea on how can we benchmark the total run time of the query during the 3x period above ?

Avatar of Andrei Fomitchev
Andrei Fomitchev
Flag of United States of America image
Try using:

SET STATISTICS TIME ON

SELECT ...

It shows:
SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Completion time: 2021-04-23T23:44:52.1394014-05:00
Avatar of marrowyung
marrowyung

ASKER

hi,
tks.
but I try to use some automated method on that to measure total execution time and record it accordingly ! It just like what SQL job history does, anyway to record Total execution time each time that query runs ?

Avatar of marrowyung
marrowyung

ASKER

any update to me ?

Avatar of marrowyung
marrowyung

ASKER

I will come back for this if this task really has to be done.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.