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 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

Reference taken from sqlusa.com

Comments

Leave a comment

Tell us about yourself

Your Name				(required field)
Your Email Address				(required field)
Website URL

Comment and preferences

Your Comment
		Subscribe to this comment thread Please enter the text you see in the picture: Submit