Contact Us

Let us know to help you out quickly!

 Admin User     Feb,08 2017

Anonymous Procedure to Measure Query Performance by Averaging

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.

SET NOCOUNT ONDECLARE @ExecutionTime TABLE(Duration INT)DECLARE @StartTime DATETIME,@endTime DATETIMEDECLARE @i INT = 1;DECLARE @Iterations int = 5;WHILE (@i <= @Iterations)BEGIN--Force Buffered Data Out For More Accurate Results-- DBCC DROPCLEANBUFFERSSET @StartTime = GETDATE()/* ------ Query To Measure Elaspsed Time ------- */selectmyField1 , myField2 ,myField3frommyTable/* ---- End ------- */SET @endTime = GETDATE()select @i as RunNo , @StartTime as StartTime , @endTime As EndTime , datediff(ms,@StartTime,@endTime) as ElapsedTimeINSERT into @ExecutionTimeSELECT DurationInMilliseconds = datediff(ms,@StartTime,@endTime)SET @i += 1END -- WHILESELECT DurationInMilliseconds = AVG(Duration)FROM @ExecutionTimeGO

Reference taken from sqlusa.com