Anonymous Procedure to Measure Query Performance by Averaging

  • February 8, 2017
  • Posted by: user
  • Category: Uncategorized
No Comments

Following code can be used to measure query elapsed time by performing multiple runs of the same query and finding the average elapsed time of the query.

[code:sql]
SET NOCOUNT ON
DECLARE @ExecutionTime TABLE(Duration INT)
DECLARE @StartTime DATETIME,@endTime DATETIME
DECLARE @i INT = 1;
DECLARE @Iterations int = 5;

WHILE (@i <= @Iterations)
BEGIN
–Force Buffered Data Out For More Accurate Results
— DBCC DROPCLEANBUFFERS
SET @StartTime = GETDATE()
/* —— Query To Measure Elaspsed Time ——- */
select
myField1 , myField2 ,myField3
from
myTable
/* —- End ——- */
SET @endTime = GETDATE()
select @i as RunNo , @StartTime as StartTime , @endTime As EndTime , datediff(ms,@StartTime,@endTime) as ElapsedTime
INSERT into @ExecutionTime
SELECT DurationInMilliseconds = datediff(ms,@StartTime,@endTime)
SET @i += 1
END — WHILE
SELECT DurationInMilliseconds = AVG(Duration)
FROM @ExecutionTime

GO
[/code]

Reference taken from sqlusa.com